Oracle固定SQL的執(zhí)行計劃(二)---SPM

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

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

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

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

當啟用了SPM后，每一個SQL都會存在對應的SQL Plan Baseline，這個SQL Plan Baseline里存儲的就是該SQL的執(zhí)行計劃，如果一個SQL有多個執(zhí)行計劃，那么該SQL就可能會有多個SQL Plan Baseline，可以從DBA_SQL_PLAN_BASELINES中查看目標SQL所有的SQL Plan Baseline。

DBA_SQL_PLAN_BASELINES中的列ENABLED和ACCEPTED用來描述一個SQL Plan Baseline所對應的執(zhí)行計劃是否能被Oracle啟用，只有ENABLED和ACCEPTED的值均為“YES”的SQL Plan Baseline所對應的執(zhí)行計劃才會被Oracle啟用，如果一具SQL有超過1個以上的SQL Plan Baseline的ENABLED和ACCEPTED的值均為YES，則Oracle會從中選擇成本值最小的一個所對應的執(zhí)行墳墓來作為該SQL的執(zhí)行計劃。

在Oracle 11g及其以上的版本中，有如下兩種方法可以產生目標SQL的SQL Plan Baseline。

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

下面分別介紹如何自動捕獲和手工的方式來產生SQL Plan Baseline。

1 自動捕獲SQL Plan Baseline

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

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

下面看一下實例：

查看上述兩個參數的默認值

zx@MYDB>show parameter sql_plan

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

在當前session中禁掉SPM并同時開啟自動捕獲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)建測試表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.

Oracle固定SQL的執(zhí)行計劃(二)---SPM

從執(zhí)行計劃上看，走的是索引IDX_T2上的索引范圍掃描，因為SQL只執(zhí)行了一次，所以Oracle不會自動捕獲SQL Plan Baseline，DBA_SQL_PLAN_BASELINES中沒有記錄

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，因為重復執(zhí)行該SQL，Oracle自動捕獲了這個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

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

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.

Oracle固定SQL的執(zhí)行計劃(二)---SPM

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

Oracle固定SQL的執(zhí)行計劃(二)---SPM 現在我們對當前Session關閉自動捕獲SQL Plan Baseline并同時開啟SPM，現在索引IDX_T2的聚簇因子依然為2400萬，再次執(zhí)行目標SQL，并查看執(zhí)行計劃：

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.

Oracle固定SQL的執(zhí)行計劃(二)---SPM

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

如果想啟用目標SQL新的執(zhí)行計劃(即全表掃描)，應該如何做呢？

針對不同的Oracle版本，會有不同的處理方法。比如這里想啟用目標SQL的新的執(zhí)行計劃，如果是11gR1的環(huán)境，則只需要將目標SQL所采用的名為SQL_PLAN_asnmb3t5yfk4024c6dbb6的SQL Plan Baseline(即索引范圍掃描)的ACCEPTED的值設為NO就可以了。但對于11gR2環(huán)境，上述方法會報錯，因為在11gR2中，所有已經被ACCEPTED的SQL Plan Baseline的ACCEPTED的值將不再能夠被設為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中，我們可以聯合使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE和DBMS_SPM.ALTER_SQL_PLAN_BASELINE達到啟用目標SQL新的執(zhí)行計劃的目的。

先用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE將新的執(zhí)行計劃(全表掃描)所對應的SQL Plan Baseline的ACCEPTED值設為“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.

Oracle固定SQL的執(zhí)行計劃(二)---SPM

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

從下面的查詢結果也可以證明：

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í)行計劃(索引范圍掃描)對應的SQL Plan Baseline的ENABLED的值設為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í)行目標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.

Oracle固定SQL的執(zhí)行計劃(二)---SPM

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

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

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

手工生成目標SQL的SQL Plan Baseline其實非常簡單，其核心就是調用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE。這里只討論針對單個SQL的SQL Plan Baseline的手工生成。

之前介紹過用Manual類型的SQL Profile可以在不改變目標SQL的SQL文本的情況下調整其執(zhí)行計劃。實際上，用手工生成SQL Plan Baseline的方式也完全可以實現同樣的目的，甚至會比使用Manual類型的SQL Profile更加簡潔。

手工生成目標SQL的SQL Plan Baseline的具體步驟為：

1)針對目標SQL使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成其初始執(zhí)行計劃所對應的SQL Plan Baseline。此時，使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE傳入的參數如下所示：

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

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

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

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

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

下面使用一個實例演示：

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

從上述顯示目標SQL初始執(zhí)行計劃為全表掃描，sql_id和plan hash value可以從執(zhí)行計劃中找到，由于沒有啟用自動捕獲SQL Plan Baseline，一開始沒有查到目標SQL對應的SQL Plan Baseline，手工生成后，可以查到全表掃描對應的SQL Plan Baseline。

改寫原目標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

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

Drop掉原執(zhí)行計劃(全表掃描)所對應的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í)行原目標SQL，并查看執(zhí)行計劃

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.

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

網站標題：Oracle固定SQL的執(zhí)行計劃(二)---SPM
網頁URL：http://muchs.cn/article40/pioieo.html

成都網站建設公司_創(chuàng)新互聯，為您提供微信小程序、做網站、網站設計、全網營銷推廣、、自適應網站

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創(chuàng)新互聯

猜你還喜歡下面的內容