之前寫(xiě)了一篇博客介紹的是用SQL Profile來(lái)調(diào)整、穩(wěn)定目標(biāo)SQL的執(zhí)行計(jì)劃，即使無(wú)法修改目標(biāo)SQL的SQL文本。但SQL Profile實(shí)際上只是一種亡羊補(bǔ)牢、被動(dòng)的技術(shù)手段，應(yīng)用在那些執(zhí)行計(jì)劃已經(jīng)發(fā)生了不好的變更的SQL上，即當(dāng)我們發(fā)現(xiàn)這些SQL的執(zhí)行計(jì)劃已經(jīng)出了問(wèn)題時(shí)通過(guò)創(chuàng)建SQL Profile來(lái)糾正、穩(wěn)定這些SQL的執(zhí)行計(jì)劃。即便通過(guò)創(chuàng)建SQL Profile解決了目標(biāo)SQL執(zhí)行計(jì)劃變更的問(wèn)題，依然不能保證系統(tǒng)后續(xù)執(zhí)行的SQL的執(zhí)行計(jì)劃就不再發(fā)生不好的變更。這種不確定性會(huì)給Oracle數(shù)據(jù)庫(kù)大版本升級(jí)(比如從Oracle 10g升級(jí)到Oracle 11g)帶來(lái)一系列的麻煩，因?yàn)椴磺宄?jí)之后原先系統(tǒng)中哪些SQL的執(zhí)行計(jì)劃會(huì)發(fā)生不好的變更。

創(chuàng)新互聯(lián)公司堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站建設(shè)、成都網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的射洪網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

為了解決上述問(wèn)題，Oracle在11g中推出了SPM(SQL Plan Management)。SPM是一種主動(dòng)的穩(wěn)定執(zhí)行計(jì)劃的手段，能夠保證只有被驗(yàn)證過(guò)的執(zhí)行計(jì)劃才會(huì)被啟用，當(dāng)由于種種原因(如統(tǒng)計(jì)信息的變更)而導(dǎo)致目標(biāo)SQL產(chǎn)生了新的執(zhí)行計(jì)劃后，這個(gè)新的執(zhí)行計(jì)劃并不會(huì)被馬上啟用，直到它已經(jīng)被我們驗(yàn)證過(guò)其執(zhí)行效率會(huì)比原先執(zhí)行計(jì)劃高才會(huì)被啟用。

隨著Oracle數(shù)據(jù)庫(kù)版本的不段推進(jìn)，其CBO的算法、功能也在一直不斷進(jìn)化和增加，所以同樣的SQL有可能在新版本的Oralce數(shù)據(jù)庫(kù)中執(zhí)行效率更高，如果我們使用了SQL Profile(特別是使用了Manual類型的SQL Profile)來(lái)穩(wěn)定目標(biāo)SQL的執(zhí)行計(jì)劃，那就意味著可能失去了繼續(xù)優(yōu)化上述SQL的執(zhí)行效率的機(jī)會(huì)。而SPM的推出可以說(shuō)徹底解決了執(zhí)行計(jì)劃穩(wěn)定性的問(wèn)題，它既能主動(dòng)地穩(wěn)定執(zhí)行計(jì)劃，又保留了繼續(xù)使用新的執(zhí)行效率可能更高的執(zhí)行計(jì)劃的機(jī)會(huì)。

當(dāng)啟用了SPM后，每一個(gè)SQL都會(huì)存在對(duì)應(yīng)的SQL Plan Baseline，這個(gè)SQL Plan Baseline里存儲(chǔ)的就是該SQL的執(zhí)行計(jì)劃，如果一個(gè)SQL有多個(gè)執(zhí)行計(jì)劃，那么該SQL就可能會(huì)有多個(gè)SQL Plan Baseline，可以從DBA_SQL_PLAN_BASELINES中查看目標(biāo)SQL所有的SQL Plan Baseline。

DBA_SQL_PLAN_BASELINES中的列ENABLED和ACCEPTED用來(lái)描述一個(gè)SQL Plan Baseline所對(duì)應(yīng)的執(zhí)行計(jì)劃是否能被Oracle啟用，只有ENABLED和ACCEPTED的值均為“YES”的SQL Plan Baseline所對(duì)應(yīng)的執(zhí)行計(jì)劃才會(huì)被Oracle啟用，如果一具SQL有超過(guò)1個(gè)以上的SQL Plan Baseline的ENABLED和ACCEPTED的值均為YES，則Oracle會(huì)從中選擇成本值最小的一個(gè)所對(duì)應(yīng)的執(zhí)行墳?zāi)箒?lái)作為該SQL的執(zhí)行計(jì)劃。

在Oracle 11g及其以上的版本中，有如下兩種方法可以產(chǎn)生目標(biāo)SQL的SQL Plan Baseline。

• 自動(dòng)捕獲

• 手工生成/批量導(dǎo)入(批量導(dǎo)入尤其適用于Oracle數(shù)據(jù)庫(kù)大版本的升級(jí)，它可以確保升級(jí)后原有系統(tǒng)所胡SQL的執(zhí)行計(jì)劃不會(huì)發(fā)生變化)

下面分別介紹如何自動(dòng)捕獲和手工的方式來(lái)產(chǎn)生SQL Plan Baseline。

1 自動(dòng)捕獲SQL Plan Baseline

參數(shù)OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES用于控制是否開(kāi)啟自動(dòng)捕獲SQL Plan Baseline，其默認(rèn)值為FALSE，表示在默認(rèn)情況下，Oracle并不會(huì)自動(dòng)捕獲SQL Plan Baseline。這個(gè)參數(shù)可以在session或系統(tǒng)級(jí)別動(dòng)態(tài)修改。當(dāng)修改為T(mén)RUE后，則Oracle會(huì)對(duì)上述參數(shù)影響范圍內(nèi)所有重復(fù)執(zhí)行的SQL自動(dòng)捕獲其SQL Plan Baseline，并且針對(duì)目標(biāo)SQL第一次捕獲的SQL Plan Baseline的ENABLED和ACCEPTED的值均為“YES”。隨后如果該SQL的執(zhí)行計(jì)劃發(fā)生了變更，則再次捕獲到的SQL Plan Baseline的ENABLED的值依然為YES，但ACCEPTED的值變?yōu)榱薔O，這表示后續(xù)變更的執(zhí)行計(jì)劃雖然被捕獲了，但Oracle不會(huì)將其作為該SQL的執(zhí)行計(jì)劃來(lái)執(zhí)行，即此時(shí)Oracle會(huì)永遠(yuǎn)沿用該SQL第一次被捕獲的SQL Plan Baseline所對(duì)應(yīng)的執(zhí)行計(jì)劃(除非后續(xù)做了手工調(diào)整)。

參數(shù)OPTIMIZER_USE_SQL_PLAN_BASELINES用于控制是否啟用SQL Plan Baseline，其默認(rèn)值為T(mén)RUE，表示在默認(rèn)情況下，Oracle在生成執(zhí)行計(jì)劃時(shí)就會(huì)啟用SPM，使用已有的SQL Plan Baseline，這個(gè)參數(shù)也可以在session或系統(tǒng)級(jí)別動(dòng)態(tài)修改。

下面看一下實(shí)例：

查看上述兩個(gè)參數(shù)的默認(rèn)值

zx@MYDB>show parameter sql_plan

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_capture_sql_plan_baselines boolean                           FALSE
optimizer_use_sql_plan_baselines     boolean                           TRUE

在當(dāng)前session中禁掉SPM并同時(shí)開(kāi)啟自動(dòng)捕獲SQL Plan Baseline：

zx@MYDB>alter session set optimizer_use_sql_plan_baselines=FALSE;

Session altered.

zx@MYDB>alter session set optimizer_capture_sql_plan_baselines=TRUE;

Session altered.

創(chuàng)建測(cè)試表T2

zx@MYDB>create table t2 as select * from dba_objects;

Table created.

zx@MYDB>create index idx_t2 on t2(object_id);

Index created.

zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T2',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

zx@MYDB>select object_id,object_name from t2 where object_id between 103 and 108;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
       103 MIGRATE$
       104 DEPENDENCY$
       105 ACCESS$
       106 I_DEPENDENCY1
       107 I_DEPENDENCY2
       108 I_ACCESS1

6 rows selected.

從執(zhí)行計(jì)劃上看，走的是索引IDX_T2上的索引范圍掃描，因?yàn)镾QL只執(zhí)行了一次，所以O(shè)racle不會(huì)自動(dòng)捕獲SQL Plan Baseline，DBA_SQL_PLAN_BASELINES中沒(méi)有記錄

zx@MYDB>col sql_handle for a30
zx@MYDB>col plan_name for a30
zx@MYDB>col origin for a20
zx@MYDB>col sql_text for a70
zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines;

no rows selected

再次執(zhí)行上述SQL，因?yàn)橹貜?fù)執(zhí)行該SQL，Oracle自動(dòng)捕獲了這個(gè)SQL的SQL Plan Baseline 

zx@MYDB>select object_id,object_name from t2 where object_id between 103 and 108;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
       103 MIGRATE$
       104 DEPENDENCY$
       105 ACCESS$
       106 I_DEPENDENCY1
       107 I_DEPENDENCY2
       108 I_ACCESS1

6 rows selected.

zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ORIGIN                ENABLED   ACCEPTED  SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ------------------------------------------------------------
SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE          YES       YES       select object_id,object_name from t2 where object_id between 103 and 108

現(xiàn)在將索引IDX_T2的聚簇因子修改為2400萬(wàn)，目的是為了能讓SQL的執(zhí)行計(jì)劃變?yōu)閷?duì)表T2的全表掃描(為何修改聚簇因子，參考http://hbxztc.blog.51cto.com/1587495/1901258)。修改完后再執(zhí)行上述SQL，并查看執(zhí)行計(jì)劃：

zx@MYDB>exec dbms_stats.set_index_stats(ownname=>USER,indname=>'IDX_T2',clstfct=>24000000,no_invalidate=>false);

PL/SQL procedure successfully completed.

zx@MYDB>select index_name,clustering_factor from dba_indexes where index_name='IDX_T2';

INDEX_NAME                                                                                 CLUSTERING_FACTOR
------------------------------------------------------------------------------------------ -----------------
IDX_T2                                                                                              24000000

zx@MYDB>select object_id,object_name from t2 where object_id between 103 and 108;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
       103 MIGRATE$
       104 DEPENDENCY$
       105 ACCESS$
       106 I_DEPENDENCY1
       107 I_DEPENDENCY2
       108 I_ACCESS1

6 rows selected.

從執(zhí)行計(jì)劃中可以看出該SQL的執(zhí)行計(jì)劃已經(jīng)變?yōu)槿頀呙琛Ｒ驗(yàn)槟繕?biāo)SQL已經(jīng)重復(fù)執(zhí)行且同時(shí)又產(chǎn)生了一個(gè)新的執(zhí)行計(jì)劃，所以現(xiàn)在Oracle就會(huì)自動(dòng)捕獲并創(chuàng)建這個(gè)新的執(zhí)行計(jì)劃所對(duì)應(yīng)的SQL Plan Baseline了。從如下查詢可以看出Oracle對(duì)新的執(zhí)行計(jì)劃產(chǎn)生了一個(gè)新的SQL Plan Baseline，其ENABLED的值依然為YES，但ACCEPTED的值變?yōu)榱薔O：

現(xiàn)在我們對(duì)當(dāng)前Session關(guān)閉自動(dòng)捕獲SQL Plan Baseline并同時(shí)開(kāi)啟SPM，現(xiàn)在索引IDX_T2的聚簇因子依然為2400萬(wàn)，再次執(zhí)行目標(biāo)SQL，并查看執(zhí)行計(jì)劃：

zx@MYDB>alter session set optimizer_use_sql_plan_baselines=TRUE;

Session altered.

zx@MYDB>alter session set optimizer_capture_sql_plan_baselines=FALSE;

Session altered.


zx@MYDB>select index_name,clustering_factor from dba_indexes where index_name='IDX_T2';

INDEX_NAME                                                                                 CLUSTERING_FACTOR
------------------------------------------------------------------------------------------ -----------------
IDX_T2                                                                                              24000000

zx@MYDB>select object_id,object_name from t2 where object_id between 103 and 108;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
       103 MIGRATE$
       104 DEPENDENCY$
       105 ACCESS$
       106 I_DEPENDENCY1
       107 I_DEPENDENCY2
       108 I_ACCESS1

6 rows selected.

從上面的顯示內(nèi)容可以看出，現(xiàn)在目標(biāo)SQL的執(zhí)行又從全表掃描恢復(fù)為了索引范圍掃描，并且執(zhí)行計(jì)劃中的Note部分有“SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement”內(nèi)容，說(shuō)明SPM開(kāi)啟的情況下，即便目標(biāo)SQL產(chǎn)生了新的執(zhí)行計(jì)劃，Oracle依然只會(huì)應(yīng)用該SQL的ENABLED和ACCEPTED的值均為YES的SQL Plan Baselline。

如果想啟用目標(biāo)SQL新的執(zhí)行計(jì)劃(即全表掃描)，應(yīng)該如何做呢？

針對(duì)不同的Oracle版本，會(huì)有不同的處理方法。比如這里想啟用目標(biāo)SQL的新的執(zhí)行計(jì)劃，如果是11gR1的環(huán)境，則只需要將目標(biāo)SQL所采用的名為SQL_PLAN_asnmb3t5yfk4024c6dbb6的SQL Plan Baseline(即索引范圍掃描)的ACCEPTED的值設(shè)為NO就可以了。但對(duì)于11gR2環(huán)境，上述方法會(huì)報(bào)錯(cuò)，因?yàn)樵?1gR2中，所有已經(jīng)被ACCEPTED的SQL Plan Baseline的ACCEPTED的值將不再能夠被設(shè)為NO：

zx@MYDB>var temp varchar2(1000);
zx@MYDB>exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'accepted',attribute_value=>'NO');
BEGIN :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'accepted',attribute_value=>'NO'); END;

*
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2469
ORA-06512: at line 1

在11gR2中，我們可以聯(lián)合使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE和DBMS_SPM.ALTER_SQL_PLAN_BASELINE達(dá)到啟用目標(biāo)SQL新的執(zhí)行計(jì)劃的目的。

先用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE將新的執(zhí)行計(jì)劃(全表掃描)所對(duì)應(yīng)的SQL Plan Baseline的ACCEPTED值設(shè)為“YES”：

zx@MYDB>exec :temp := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk40b860bcf2',verify=>'NO',commit=>'YES');

PL/SQL procedure successfully completed.

從上面顯示的內(nèi)容看到如下信息：“Plan: SQL_PLAN_asnmb3t5yfk40b860bcf2----Plan was changed to an accepted plan.”，這表明已經(jīng)將新的執(zhí)行計(jì)劃(全表掃描)所對(duì)應(yīng)的SQL Plan Baseline的ACCEPTED值設(shè)為YES

從下面的查詢結(jié)果也可以證明：

zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

SQL_HANDLE		       PLAN_NAME		      ORIGIN		   ENABLED   ACCEPTED  SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE	   YES	     YES       select object_id,object_name from t2 where object_id between 103 and 108


SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE	   YES	     YES       select object_id,object_name from t2 where object_id between 103 and 108

然后再使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE將原先的執(zhí)行計(jì)劃(索引范圍掃描)對(duì)應(yīng)的SQL Plan Baseline的ENABLED的值設(shè)為NO：

zx@MYDB>exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'enabled',attribute_value=>'NO');

PL/SQL procedure successfully completed.

zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

SQL_HANDLE		       PLAN_NAME		      ORIGIN		   ENABLED   ACCEPTED  SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE	   NO	     YES       select object_id,object_name from t2 where object_id between 103 and 108


SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE	   YES	     YES       select object_id,object_name from t2 where object_id between 103 and 108

再次執(zhí)行目標(biāo)SQL

zx@MYDB>select object_id,object_name from t2 where object_id between 103 and 108;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
       103 MIGRATE$
       104 DEPENDENCY$
       105 ACCESS$
       106 I_DEPENDENCY1
       107 I_DEPENDENCY2
       108 I_ACCESS1

6 rows selected.

從上述顯示可以看出，現(xiàn)在SQL的執(zhí)行計(jì)劃已經(jīng)變?yōu)榱巳頀呙?，我們要啟用新的?zhí)行計(jì)劃(全表掃描)的目的已經(jīng)實(shí)現(xiàn)，Note部分也有了提示。

從上述測(cè)試結(jié)果可以看出，實(shí)際上我們可以輕易地在目標(biāo)SQL的多個(gè)執(zhí)行計(jì)劃中切換，所以SPM確實(shí)是既能夠主動(dòng)地穩(wěn)定執(zhí)行計(jì)劃，又保留了繼續(xù)使用新的執(zhí)行計(jì)劃的機(jī)會(huì)，并且我們很容易就能啟用新的執(zhí)行計(jì)劃。

下面介紹手工生成SQL Plan Baseline：

手工生成目標(biāo)SQL的SQL Plan Baseline其實(shí)非常簡(jiǎn)單，其核心就是調(diào)用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE。這里只討論針對(duì)單個(gè)SQL的SQL Plan Baseline的手工生成。

之前介紹過(guò)用Manual類型的SQL Profile可以在不改變目標(biāo)SQL的SQL文本的情況下調(diào)整其執(zhí)行計(jì)劃。實(shí)際上，用手工生成SQL Plan Baseline的方式也完全可以實(shí)現(xiàn)同樣的目的，甚至?xí)仁褂肕anual類型的SQL Profile更加簡(jiǎn)潔。

手工生成目標(biāo)SQL的SQL Plan Baseline的具體步驟為：

1)針對(duì)目標(biāo)SQL使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成其初始執(zhí)行計(jì)劃所對(duì)應(yīng)的SQL Plan Baseline。此時(shí)，使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE傳入的參數(shù)如下所示：

dbms_spm.load_plans_from_cursor_cache(sql_id=>'原目標(biāo)SQL的SQL_ID',plan_hash_value=>原目標(biāo)SQL的PLAN HASH VALUE)

2)改寫(xiě)原目標(biāo)SQL的SQL文本，在其中加入合適的Hint，直到加入Hint后的所改寫(xiě)的SQL能走出我們想要的執(zhí)行計(jì)劃，然后對(duì)改寫(xiě)后的SQL使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成新的執(zhí)行計(jì)劃所對(duì)應(yīng)的SQL Plan Baseline。此時(shí)傳入的參數(shù)如下所示：

dbms_spm.load_plans_from_cursor_cache(sql_id=>'加入合適Hint后改寫(xiě)SQL的SQL_ID',plan_hash_value=>加入合適Hint后改寫(xiě)SQL的PLAN HASH VALUE,sql_handle=>'原目標(biāo)SQL在步驟(1)中所產(chǎn)生的SQL Plan Baseline的sql_handle')

3)使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE刪除步驟(1)中手工生成的原目標(biāo)SQL的初始執(zhí)行計(jì)劃所對(duì)應(yīng)的SQL Plan Baseline。此時(shí)傳入的參數(shù)如下所示：

dbms_spm.drop_sql_plan_baseline(sql_handle=>'原目標(biāo)SQL在步驟(1)中所產(chǎn)生的SQL Plan Baseline的sql_handle',plan_name=>'原目標(biāo)SQL在步驟(1)中所產(chǎn)生的SQL Plan Baseline的plan_name')

下面使用一個(gè)實(shí)例演示：

zx@MYDB>select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_id=4;

OBJECT_NAME			OBJECT_ID
------------------------------ ----------
TAB$					4

zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	0n5z3wmf8qpgn, child number 0
-------------------------------------
select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where
object_id=4

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |	 |	 |   287 (100)| 	 |
|*  1 |  TABLE ACCESS FULL| T2	 |     1 |    30 |   287   (1)| 00:00:04 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T2@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=4)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]


43 rows selected.

zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t2 idx_t2)%';

no rows selected

zx@MYDB>var temp number
zx@MYDB>exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0n5z3wmf8qpgn',plan_hash_value=>1513984157);

PL/SQL procedure successfully completed.

zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t2 idx_t2)%';

SQL_HANDLE		       PLAN_NAME		      ORIGIN		   ENABLED   ACCEPTED  SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SYS_SQL_75b06ae056223f5f       SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD	   YES	     YES       select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_i
												       d=4

從上述顯示目標(biāo)SQL初始執(zhí)行計(jì)劃為全表掃描，sql_id和plan hash value可以從執(zhí)行計(jì)劃中找到，由于沒(méi)有啟用自動(dòng)捕獲SQL Plan Baseline，一開(kāi)始沒(méi)有查到目標(biāo)SQL對(duì)應(yīng)的SQL Plan Baseline，手工生成后，可以查到全表掃描對(duì)應(yīng)的SQL Plan Baseline。

改寫(xiě)原目標(biāo)SQL，加入Hint后重新執(zhí)行：

zx@MYDB>select /*+ index(t2 idx_t2) */ object_name,object_id from t2 where object_id=4;

OBJECT_NAME			OBJECT_ID
------------------------------ ----------
TAB$					4

zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	60txg87j30pvw, child number 0
-------------------------------------
select /*+ index(t2 idx_t2) */ object_name,object_id from t2 where
object_id=4

Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	     |	     |	 335 (100)|	     |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |	   1 |	  30 |	 335   (0)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN	    | IDX_T2 |	   1 |	     |	   1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T2@SEL$1
   2 - SEL$1 / T2@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=4)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]


46 rows selected.

zx@MYDB>exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'60txg87j30pvw',plan_hash_value=>2008370210,sql_handle=>'SYS_SQL_75b06ae056223f5f');

PL/SQL procedure successfully completed.

zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t2 idx_t2)%';

SQL_HANDLE		       PLAN_NAME		      ORIGIN		   ENABLED   ACCEPTED  SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SYS_SQL_75b06ae056223f5f       SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD	   YES	     YES       select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_i
												       d=4

SYS_SQL_75b06ae056223f5f       SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD	   YES	     YES       select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_i
												       d=4

從上述輸出可以看出把改寫(xiě)過(guò)的SQL的新的執(zhí)行計(jì)劃所對(duì)應(yīng)的SQL Plan Baseline已經(jīng)成功生成，而且所有手工生成的SQL Plan Baseline的ENABLED和ACCEPTED的值均為YES，這是和自動(dòng)捕獲的SQL Plan Baseline不一樣的地方。

Drop掉原執(zhí)行計(jì)劃(全表掃描)所對(duì)應(yīng)的SQL Plan Baseline：

zx@MYDB>exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_75b06ae056223f5f',plan_name=>'SQL_PLAN_7bc3aw1b24guzb860bcf2');

PL/SQL procedure successfully completed.

zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t2 idx_t2)%';

SQL_HANDLE		       PLAN_NAME		      ORIGIN		   ENABLED   ACCEPTED  SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SYS_SQL_75b06ae056223f5f       SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD	   YES	     YES       select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_i
												       d=4

再次執(zhí)行原目標(biāo)SQL，并查看執(zhí)行計(jì)劃

zx@MYDB>select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_id=4;

OBJECT_NAME			OBJECT_ID
------------------------------ ----------
TAB$					4

zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	0n5z3wmf8qpgn, child number 2
-------------------------------------
select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where
object_id=4

Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	     |	     |	 335 (100)|	     |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |	   1 |	  30 |	 335   (0)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN	    | IDX_T2 |	   1 |	     |	   1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T2@SEL$1
   2 - SEL$1 / T2@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=4)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

Note
-----
   - SQL plan baseline SQL_PLAN_7bc3aw1b24guz24c6dbb6 used for this statement


50 rows selected.

從上述輸出可以看出，原目標(biāo)SQL已經(jīng)走了新的執(zhí)行計(jì)劃(索引范圍掃描)，而且Note部分也有提示“SQL plan baseline SQL_PLAN_7bc3aw1b24guz24c6dbb6 used for this statement”說(shuō)明走了SPM。