11g推出的SPM是一種主動的穩(wěn)定執(zhí)行計劃的手段，能夠保證只有被驗證過的執(zhí)行計劃才會被啟用。SPM既能夠主動的穩(wěn)定執(zhí)行計劃，又保留了繼續(xù)使用新的執(zhí)行效率更高的執(zhí)行計劃的機(jī)會。
創(chuàng)新互聯(lián)是一家專業(yè)提供紫陽企業(yè)網(wǎng)站建設(shè),專注與成都網(wǎng)站設(shè)計、做網(wǎng)站、HTML5、小程序制作等業(yè)務(wù)。10年已為紫陽眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)絡(luò)公司優(yōu)惠進(jìn)行中。
啟用SPM后，每一個SQL都會存在對應(yīng)的SQL PLAN Baseline，存儲在DBA_SQL_PLAN_BASELINES視圖。
該視圖的enable和accept列均為YES的SQL PLAN Baseline所對應(yīng)的執(zhí)行計劃才會被執(zhí)行。如果有超過1個以上的均為YES，那么oracle會選擇其中cost值最小的為執(zhí)行計劃。
可以有2種方法產(chǎn)生SQL PLAN Baseline
1.自動捕獲
2.手動生成/批量導(dǎo)入
下面先介紹自動捕獲。參數(shù)optimizer_capture_sql_plan_baselines用于控制是否開啟自動捕獲，默認(rèn)為false。參數(shù)optimizer_use_sql_plan_baselines用于控制是否啟用SPM，默認(rèn)為TRUE，表示默認(rèn)情況下Oracle在生成執(zhí)行計劃時就會啟用SPM，使用已有的SQL PLAN Baseline。
創(chuàng)建一個自動捕獲SQL PLAN Baseline，并據(jù)此來穩(wěn)定執(zhí)行計劃。
默認(rèn)參數(shù)
sys@TEST:test> 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,同時開啟自動捕獲。
aaa@TEST:test> alter session set optimizer_capture_sql_plan_baselines=true; Session altered. aaa@TEST:test> alter session set optimizer_use_sql_plan_baselines=false; Session altered.
創(chuàng)建測試表T2，并創(chuàng)建索引
aaa@TEST:test> create table t2 as select * from dba_objects; Table created. aaa@TEST:test> create index idx_t2 on t2(object_id); Index created.
對T2收集統(tǒng)計信息
aaa@TEST:test> exec dbms_stats.gather_table_stats(ownname => 'AAA',tabname => 'T2',estimate_percent => 100,cascade => true); PL/SQL procedure successfully completed.
執(zhí)行SQL
aaa@TEST:test> 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í)行計劃如下
----------------------------------------------------------------------------------------------------------------------------------- SQL_ID 8vtdn0kgytfxr, child number 0 ------------------------------------- select object_id,object_name from t2 where object_id between 103 and 108 Plan hash value: 2008370210 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 180 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T2 | 6 | | 2 (0)| 00:00:01
因為該SQL只執(zhí)行過一次，所以O(shè)racle不會自動捕獲其SQL PLAN Baseline，驗證：
aaa@TEST:test> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%'; no rows selected
再執(zhí)行一次，執(zhí)行計劃沒有變化，因為SQL已經(jīng)重復(fù)執(zhí)行，Oracle會自動捕獲其SQL PLAN Baseline
SQL> 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
這里修改索引IDX_T2的聚簇因子修改為2400萬，改變目標(biāo)SQL的執(zhí)行計劃為全表。
SQL> exec dbms_stats.set_index_stats(ownname=>'AAA',indname=>'IDX_T2',clstfct=>24000000,no_invalidate => false); PL/SQL procedure successfully completed
重新執(zhí)行SQL，執(zhí)行計劃如下，為全表
select object_id,object_name from t2 where object_id between 103 and 108 Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 290 (100)| | |* 1 | TABLE ACCESS FULL| T2 | 6 | 180 | 290 (1)| 00:00:04 | --------------------------------------------------------------------------
因為目標(biāo)SQL已經(jīng)重復(fù)執(zhí)行且又產(chǎn)生了一個執(zhí)行計劃，所以O(shè)racle會自動捕獲并創(chuàng)建這個新的執(zhí)行計劃所對應(yīng)的SQL PLAN Baseline，查詢視圖
SQL> 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 NO select object_id,object_name from t2 where object_id between 103 and 108
然后關(guān)閉當(dāng)前session自動捕獲SQL PLAN Baseline，并啟用SPM，就是恢復(fù)11g默認(rèn)
aaa@TEST:test> alter session set optimizer_capture_sql_plan_baselines=false; Session altered. aaa@TEST:test> alter session set optimizer_use_sql_plan_baselines=true; Session altered.
現(xiàn)在索引IDX_T2的聚簇因子還是2400萬
aaa@TEST:test> select index_name,clustering_factor from user_indexes; INDEX_NAME CLUSTERING_FACTOR ------------------------------ ----------------- IDX_T2 24000000
再次執(zhí)行目標(biāo)SQL，其執(zhí)行計劃
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------- SQL_ID 8vtdn0kgytfxr, child number 2 ------------------------------------- select object_id,object_name from t2 where object_id between 103 and 108 Plan hash value: 2008370210 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2069 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 180 | 2069 (0)| 00:00:25 | |* 2 | INDEX RANGE SCAN | IDX_T2 | 6 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Note ----- - SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement
上述可以看出，現(xiàn)在目標(biāo)SQL的執(zhí)行計劃已經(jīng)從對表的全表掃描，變成了索引范圍掃描。并且NOTE下面有“SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement”，這表明在啟用SPM情況下， 即使目標(biāo)SQL產(chǎn)生了新執(zhí)行計劃，Oracle依然只會使用enabled和accepted均為YES的SQL PLAN Baseline對應(yīng)的執(zhí)行計劃。
如果想啟用新的執(zhí)行計劃，對于不同版本操作不一樣。
11gR1，只需要將目標(biāo)SQL鎖采用的名為SQL_PLAN_asnmb3t5yfk4024c6dbb6的SQL PLAN Baseline的accepted值變?yōu)镹O即可。
SQL> var temp number; SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_ac526b1e4be74880',plan_name => 'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name => 'accepted',attribute_value => 'NO');
在11gR2中，執(zhí)行上述代碼或報錯
SQL> var temp number; SQL> 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; ORA-38136: 指定的屬性名 ACCEPTED 無效 ORA-06512: 在 "SYS.DBMS_SPM", line 2469 ORA-06512: 在 line 1 temp ---------
在11gR2中，使用dbms_spm.evolve_sql_plan_baseline和dbms_spm.alter_sql_plan_baseline達(dá)到啟用新執(zhí)行計劃的目的。
先用dbms_spm.evolve_sql_plan_baseline將新執(zhí)行計劃的accepted置為YES
SQL> var temp varchar2(4000); SQL> 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 temp --------- ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SYS_SQL_ac526b1e4be74880 PLAN_NAME = SQL_PLAN_asnmb3t5yfk40b860bcf2 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = NO COMMIT = YES Plan: SQL_PLAN_asnmb3t5yfk40b860bcf2 ------------------------------------ It is already an accepted plan. ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- There were no SQL plan baselines that required processing. SQL> 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 ob
再使用dbms_spm.alter_sql_plan_baseline把PLAN_NAME為SQL_PLAN_asnmb3t5yfk4024c6dbb6的SQL PLAN Baseline的enabled置為NO。
SQL> var temp number; SQL> 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 temp --------- 1 SQL> 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
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------- SQL_ID 8vtdn0kgytfxr, child number 0 ------------------------------------- select object_id,object_name from t2 where object_id between 103 and 108 Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 290 (100)| | |* 1 | TABLE ACCESS FULL| T2 | 6 | 180 | 290 (1)| 00:00:04 | -------------------------------------------------------------------------- Note ----- - SQL plan baseline SQL_PLAN_asnmb3t5yfk40b860bcf2 used for this statement
使用了新的執(zhí)行計劃，注意NOTE中SQL_PLAN_asnmb3t5yfk40b860bcf2即SQL PLAN Baseline中enabled和accepted均為YES的SQL PLAN Baseline對應(yīng)的執(zhí)行執(zhí)行計劃名。
從測試結(jié)果看出，我們可以在目標(biāo)SQL的多個執(zhí)行計劃之間切換，所以SPM既能穩(wěn)定執(zhí)行計劃，又保留了繼續(xù)使用新的執(zhí)行計劃的機(jī)會。
接下來介紹手工生成SQL PLAN Baseline。手工生成很簡單，核心是調(diào)用dbms_spm.load_plans_from_cursor_cache。
針對目標(biāo)SQL使用dbms_spm.load_plans_from_cursor_cache手工生成其初始執(zhí)行計劃所對應(yīng)的SQL PLAN Baseline。
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------- SQL_ID 85htp4tya3uwm, 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 | | | | 290 (100)| | |* 1 | TABLE ACCESS FULL| T2 | 1 | 30 | 290 (1)| 00:00:04 |
上述SQL的SQL_ID為85htp4tya3uwm，plan_hash_value為1513984157，現(xiàn)在沒有開啟自動捕獲SQL PLAN Baseline。視圖dba_sql_plan_baselines應(yīng)該沒有對應(yīng)信息。
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_name%'; SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- -------- -----------------------------------------------------------
使用目標(biāo)SQL的SQL_ID和PLAN_HASH_VALUE來生成所對應(yīng)的SQL PLAN Baseline。
SQL> var temp number; SQL> exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'85htp4tya3uwm',plan_hash_value=>1513984157); PL/SQL procedure successfully completed temp --------- 1 SQL> 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)*/ object_name%'; 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_id=4
已經(jīng)生成了SQL_HANDLE為SYS_SQL_75b06ae056223f5f和PLAN_NAME為SQL_PLAN_7bc3aw1b24guzb860bcf2的SQL PLAN Baseline。
改寫原目標(biāo)SQL，加入強(qiáng)制走索引hint
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------- SQL_ID 0argb4cn0sybz, 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 | | | | 332 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 30 | 332 (0)| 00:00:04 | |* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
改寫后走索引，SQL_ID=0argb4cn0sybz，Plan hash value: 2008370210。
再加上原目標(biāo)SQL的SQL_HANDLE來生成新的SQL PLAN Baseline。
SQL> exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0argb4cn0sybz',plan_hash_value=>2008370210,sql_handle=>'SYS_SQL_75b06ae056223f5f'); PL/SQL procedure successfully completed temp --------- 1 SQL> 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)*/ object_name%'; 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_id=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_id=4
上述可以看出原目標(biāo)SQL生成了新的PLAN，其PLAN_NAME為SQL_PLAN_7bc3aw1b24guz24c6dbb6。注意新生成的SQL PLAN Baseline的enabled和accepted均為YES，這是個自動捕獲不同的地方。
現(xiàn)在DROP掉原目標(biāo)SQL走全表掃描所對應(yīng)的SQL PLAN Baseline。
SQL> exec :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle =>'SYS_SQL_75b06ae056223f5f',plan_name => 'SQL_PLAN_7bc3aw1b24guzb860bcf2'); PL/SQL procedure successfully completed temp --------- 1 SQL> 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)*/ object_name%'; 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_id=4
再次執(zhí)行原SQL，可以看到走了索引
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------- SQL_ID 85htp4tya3uwm, 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 | | | | 332 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 30 | 332 (0)| 00:00:04 | |* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Note ----- - SQL plan baseline SQL_PLAN_7bc3aw1b24guz24c6dbb6 used for this statement
最后注意NOTE下面，使用了SQL PLAN Baeline。
重慶分公司
重慶分公司
