执行计划得改变导致走了索引反而性能下降了！那么我们是否有办法让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》

Tags：oracle 记录