spm（oracle固定执行计划）

执行计划得改变导致走了索引反而性能下降了！那么我们是否有办法让SQL走一个固定的执行计划，不会受某些环境变动从而生成并走新的执行计划呢？常用的是sqlprofile和spm（sql plan management）,如下采用spm方式进行执行计划的固定。

1、模拟测试数据

conn test/test@pdb create table tab2(c1 number, c2 number, c3 varchar2(10)); declare a number; begin a := 1; for i in 1 .. 50 loop for j in 1 .. 100 loop insert into tab2 values(a,j,'a'); commit; a := a+1; end loop; end loop; end; / create index ind2_2 on tab2(c2); exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TAB2',cascade=>TRUE);

查看sql语句的执行计划

set autot on SQL> select count(*) from tab2 where c2=1; COUNT(*) ---------- 50 Execution Plan ---------------------------------------------------------- Plan hash value: 3563712581 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| IND2_2 | 50 | 150 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C2"=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 550 bytes sent via SQL*Net to client 398 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 当前的sql语句使用的是索引访问。

加载当前的sql到spm

1、查看对象SQL文的sql_id和plan_hash_value。 SQL> set autot off SQL> select sql_id, child_number, plan_hash_value from v$sql where sql_text = 'select count(*) from tab2 where c2=1'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE ------------- ------------ --------------- 3uat1k9ssur9p 0 3563712581 2.把对象SQL文的执行计划Load进SPM。 SQL> var cnt number; SQL> exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id', plan_hash_value => '&plan_hash_value'); Enter value for sql_id: 3uat1k9ssur9p Enter value for plan_hash_value: 3563712581 PL/SQL procedure successfully completed. col sql_handle for a30 col SQL_TEXT for a50 col PLAN_NAME for a30 set lin 120 pages 999 加载后baseline的状态为no，此时sql不会使用此执行计划。 select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1'; SQL_HANDLE SQL_TEXT PLAN_NAME FIX ------------------------------ -------------------------------------------------- ------------------------------ --- SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stx910d0b22 NO 3.固定执行计划。 SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle', plan_name => '&plan_name', attribute_name => 'FIXED', attribute_value => 'YES'); Enter value for sql_handle: SQL_156e46a7ed64e33d Enter value for plan_name: SQL_PLAN_1avk6nzqq9stx910d0b22 PL/SQL procedure successfully completed. 再次查看plan baseline已经变为yes。 SQL> select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1'; SQL_HANDLE SQL_TEXT PLAN_NAME FIX ------------------------------ -------------------------------------------------- ------------------------------ --- SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stx910d0b22 YES Fixed(控制优先级)： + YES (如果是“Enabled”并且“Accepted”，会优先选择这个计划，这个计划会被视为不需要改变的) + NO (普通的计划，无需优先)

再次执行原sql查看是否使用plan

sql已经使用了绑定的sqlplan了。

固定新的执行计划是sql语句可以使用新的执行计划

SQL> select /*+ FULL (TAB2) */ count(*) from tab2 where c2=1; COUNT(*) ---------- 50 通过使用hint方式强制使用全表扫描。

SQL> set autot off SQL> select sql_id, child_number, plan_hash_value from v$sql where sql_text like 'select /*+ FULL (TAB2) */ count(*) from tab2 where c2=1'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE ------------- ------------ --------------- 556psnns1a273 0 2781695375

加载新的执行计划到原sqlplan baseline

把加了Hint的SQL文的执行计划加到原始SQL文的BaseLine上。 SQL> var cnt number; SQL> exec :cnt := dbms_spm.load_plans_from_cursor_cache( sql_id => '&hinted_SQL_ID', plan_hash_value => &hinted_plan_hash_value, sql_handle => '&sql_handle_for_original'); Enter value for hinted_sql_id: 556psnns1a273 Enter value for hinted_plan_hash_value: 2781695375 Enter value for sql_handle_for_original: SQL_156e46a7ed64e33d PL/SQL procedure successfully completed. 再次查看sqlplan中的baseline col sql_handle for a30 col SQL_TEXT for a50 col PLAN_NAME for a30 set lin 120 pages 999 select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1';

可以看到此时新加入的baseline的fixed状态是no的，不会被优化器选择。 删除之前的baseline b22 exec :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('&original_sql_handle','&original_plan_name'); Enter value for original_sql_handle: SQL_156e46a7ed64e33d Enter value for original_plan_name: SQL_PLAN_1avk6nzqq9stx910d0b22 PL/SQL procedure successfully completed. 再次固定后面的执行计划 SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle', plan_name => '&plan_name', attribute_name => 'FIXED', attribute_value => 'YES'); Enter value for sql_handle: SQL_156e46a7ed64e33d Enter value for plan_name: SQL_PLAN_1avk6nzqq9stxfbe5cdec PL/SQL procedure successfully completed.

再次执行sql，sql已经使用了新的执行计划。

select sql_handle,sql_text, plan_name,fixed,ENABLED,ACCEPTED,REPRODUCED from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1' SQL> / SQL_HANDLE SQL_TEXT PLAN_NAME FIX ENA ACC REP ------------------------------ -------------------------------------------------- ------------------------------ --- --- --- --- SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stxfbe5cdec YES YES YES YES

dba_sql_plan_baselines中几个字段的介绍

注意：enabled为yes，accepted为yes，fixed为on。另外，SQL*Plus是添加这个计划的模块。 SPM通过几个标记来实现对执行计划的控制： Enabled (控制活动)： + YES (活动的，但不一定会被使用) + NO (可以理解为被标记删除) Accepted(控制使用)： + YES (只有 “Enabled” 并且“Accepted” 的计划才会被选择使用) + NO (如果是“Enabled” 那么只有被evolve成“Accepted”才有可能被执) Fixed(控制优先级)： + YES (如果是“Enabled”并且“Accepted”，会优先选择这个计划，这个计划会被视为不需要改变的) + NO (普通的计划，无需优先) Reproduced(有效性)： + YES (优化器可以使用这个计划) + NO (计划无效，比如索引被删除)

参考《https://segmentfault.com/a/1190000039271529》