oracle稳定执行计划

二、稳定执行计划
（一）sql profile的好处
稳定执行计划
在不能修改目标sql的sql文本的情况下使目标sql语句按照指定的执行计划运行。
1、automatic类型的sql profile
本质是针对目标sql的一些额外的调整信息，这些额外的调整信息需要与原目标sql的相关统计信息等内容一起作用才能得到新的执行计划，即原始sql的统计信息等内容一旦发生变化，即使原有的automatic类型的sql profile并没有改变，该sql的执行计划也可能发生变化。
SQL> exec dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_4',task_owner =>'SCOTT',replace => TRUE,force_match=>true);
说明：
force_match=>true 相当于绑定变量

（1）t1表原本有索引idx_t1，使用hint让它不走索引
SQL> grant select on v_$session to scott;
SQL> grant select on v_$sql_plan_statistics_all to scott;
SQL> grant select on v_$sql_plan to scott;
SQL> connect scott/tiger; 
SQL> create table t1(n number);
SQL> declare
  2  begin
  3  for i in 1 .. 10000
  4  loop
  5  insert into t1 values(i);
  6  commit;
  7  end loop;
  8  end;
  9  /
SQL> select count(*) from t1;
  COUNT(*)
----------
     10000
SQL> create index idx_t1 on t1(n);
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',method_opt=>'for all columns size 1',CASCADE=>true);
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

该执行计划会走全表扫描

（2）建立一个自动调整任务（my_sql_tuning_task_4）
SQL> declare
  2  my_task_name VARCHAR2(30);
  3  my_sqltext CLOB;
  4  begin
  5  my_sqltext := 'select /*+ no_index(t1 idx_t1) */ * from t1 where n=1';
  6  my_task_name := dbms_sqltune.create_tuning_task(
  7  sql_text => my_sqltext,
  8  user_name => 'SCOTT',
  9  scope => 'COMPREHENSIVE',
 10  time_limit => 60,
 11  task_name => 'my_sql_tuning_task_4',
 12  description => 'Task to tune a query on table t1');
 13   END;
 14  /
 

(备注：删除自动任务 ： exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task_4');

补充：删除sql_profile : exec dbms_sqltune.drop_sql_profile(name =>'my_sql_tuning_task_4');  

 （3）执行上述自动调整任务
 SQL> begin
  2  dbms_sqltune.execute_tuning_task(task_name => 'my_sql_tuning_task_4');
  3  end;
  4  /
  
（4）查看该自动调整任务的结果确实走了索引。
SQL> set long 900
SQL> set longchunksize 1000
SQL> set linesize 800
SQL> select dbms_sqltune.report_tuning_task('my_sql_tuning_task_4') from dual;

 （5）接受这个sql profile
SQL> exec dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_4',task_owner =>'SCOTT',replace => TRUE,force_match=>true);

（补充：删除sql_profile : exec dbms_sqltune.drop_sql_profile(name =>'my_sql_tuning_task_4');  

 （6）接着再执行如下sql时会走索引
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=20
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

2、manual类型的sql profile
本质是一堆hint的组合。这一堆hint的组合实际上来源于执行计划中outline data部分的hint组合。manual类型的sql profile可以起到很好的稳定目标sql的执行计划的作用。

使用脚本coe_xfr_sql_profile.sql针对目标sql生成manual类型的sql profile,并通过"偷梁换柱"的方式在不修改目标sql的sql文本的情况下调整其执行计划。
步骤如下：
（1）针对目标sql使用脚本coe_xfr_sql_profile.sql产生能生成其manual类型的sql profile的脚本A.
（2）改写目标sql的文本，在其中使用合适的hint，直到加入hint后的sql能走出我们想要的执行计划。然后对加入合适hint后的sql使用脚本coe_xfr_sql_profile.sql，产生能生成其manual类型的sql profile的脚本B
（3）用脚本B中的Outline Data部分的hint组合替换掉脚本A中的Outline Data部分的hint组合。
（4）执行脚本A生成针对原目标sql的manual类型的sql profile.
 
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=20;    
因为上一步automatic类型的sql profile使用了自动调整任务，所以该sql的执行计划会走索引
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
INDEX RANGE SCAN
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - SQL profile coe_gkdzg1fk71sc0_3617692013 used for this statement

删除sql profile
SQL> exec dbms_sqltune.drop_sql_profile('coe_gkdzg1fk71sc0_3617692013');

执行走索引的profile
SQL> select /*+ index(t1 idx_t1) */ * from t1 where n=20;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
SQL_ID    2a4g5h03mm877, child number 0
Plan hash value: 1369807930
|*  1 |  INDEX RANGE SCAN| IDX_T1 |    1 |    4 |    1   (0)| 00:00:01 |

SQL> col SQL_TEXT for a85
SQL> set pagesize 900;
SQL> set linesize 900;
SQL> select sql_text,sql_id,version_count from    v$sqlarea where sql_text like '%n=20%'
SQL_TEXT                                                                             SQL_ID         VERSION_COUNT
---------------------------------------------------------------------------------- ------------- -------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=20                                 fyrgtpxw26btv         1
select /*+ index(t1 idx_t1) */ * from t1 where n=20                                     2a4g5h03mm877         1
select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%n=20%'     gtfnngky67bj6         1

原sql对应的plan_hash_value
SQL> select plan_hash_value from v$sql where sql_id='fyrgtpxw26btv';
PLAN_HASH_VALUE

     3617692013
加了强制索引的sql对应的plan_hash_value
SQL> select plan_hash_value from v$sql where sql_id='2a4g5h03mm877';
PLAN_HASH_VALUE
---------------
     1369807930

针对原sql使用脚本coe_xfr_sql_profile.sql产生能生产其manual类型sql profile脚本。
SQL> @?/rdbms/admin/coe_xfr_sql_profile.sql;   （oracle官方文档ID 215187.1）
Parameter 1:
SQL_ID (required)
Enter value for 1: fyrgtpxw26btv
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3617692013        .006
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 3617692013
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID           : "fyrgtpxw26btv"
PLAN_HASH_VALUE: "3617692013"
SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3       RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3       RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql
on TARGET system in order to create a custom SQL Profile
with plan 3617692013 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>

针对强制索引的sqlsql使用脚本coe_xfr_sql_profile.sql产生能生产其manual类型sql profile脚本。
SQL>@?/rdbms/admin/coe_xfr_sql_profile.sql;
Parameter 1:
SQL_ID (required)
Enter value for 1: 2a4g5h03mm877
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1369807930        .007
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1369807930
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID           : "2a4g5h03mm877"
PLAN_HASH_VALUE: "1369807930"
SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3       RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3       RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_2a4g5h03mm877_1369807930.sql
on TARGET system in order to create a custom SQL Profile
with plan 1369807930 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>

参考coe_xfr_sql_profile_2a4g5h03mm877_1369807930.sql修改coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql脚本内容，再执行coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql脚本

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('12.1.0.2')]',
q'[DB_VERSION('12.1.0.2')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "T1"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

改为
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('12.1.0.2')]',
q'[DB_VERSION('12.1.0.2')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]',
q'[END_OUTLINE_DATA]');

force_match=FALSE
改为
force_match=TRUE

执行coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql脚本
SQL> @coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql
SQL> connect scott/tiger;
Connected.

( 补充：删除sql_profile : exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'my_sql_tuning_task_4');    )

最后可以走索引。
SQL>  select /*+ no_index(t1 idx_t1) */ * from t1 where n=20;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
|*  1 |  INDEX RANGE SCAN| IDX_T1 |    1 |    4 |    1   (0)| 00:00:01 |

二、spm稳定执行计划
oracle 11g以及以上版本上，有如下2种方法产生目标sql的sql plan baseline:
自动捕获：
手工生成/批量导入（批量导入尤其适用于oracle数据库大版本升级，它可以确保升级后原有系统所有的sql的执行计划不会发生改变）

手工生成单个sql plan baseline的步骤
（1）针对目标sql使用dbms_spm.load_plans_from_cursor_cache手工生成其初始执行计划所对应的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能走出我们想要的执行计划，然后对改写后的sql使用dbms_spm.load_plans_from_cursor_cache手工生成新的执行计划所对应的sql plan baseline.此时，使用dbms_spm.load_plans_from_cursor_cache传入的参数为如下所示：
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.drop_sql_plan_baseline删除步骤（1）中手工生成的原目标sql的初始执行计划所对应的sql plan baseline.此时，使用dbms_spm.drop_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'
)

var temp varchar2(10000);
exec:temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_75b06ae056223f5f',plan_name=>'SQL_PLAN_7bc3aw1b24guzb55f43d8');
exec:temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_75b06ae056223f5f',plan_name=>'SQL_PLAN_7bc3aw1b24guzb860bcf2');

创建测试表，建立索引，收集统计信息，执行查询
SQL> create table t2 as select * from dba_objects;
SQL> create index idx_t2 on t2(object_id);
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true);
SQL> select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
SQL_ID    76td2qkxcrn9v, child number 0
Plan hash value: 1513984157
|*  1 |  TABLE ACCESS FULL| T2     |     1 |    30 |   430   (1)| 00:00:01 |
--------------------------------------------------------------------------

没有开启sql_plan_baseline自动捕获，所以没有sql_plan_baseline
SQL> set pagesize 900;
SQL> set linesize 900;
SQL> col PLAN_NAME for a30;
SQL> col SQL_HANDLE for a25;
SQL> col SQL_TEXT for a80;
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%';
no rows selected
SQL> 

（1）
使用目标sql的初始执行计划（即对T2表的全表扫描）所对应的sql id 和plan hash_value 来手工生成对应的sql plan baseline
SQL > var temp number;
SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id =>'76td2qkxcrn9v',plan_hash_value=>1513984157);
PL/SQL procedure successfully completed
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%';
SQL_HANDLE          PLAN_NAME             ORIGIN     ENA ACC SQL_TEXT
------------------------- ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
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
SQL> 

改写目标sql，强制索引hint
SQL> select /*+ index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;
SQL>  select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
SQL_ID    795jg57h9tryx, child number 0
Plan hash value: 1306670842
|*  2 |   INDEX RANGE SCAN            | IDX_T2 |       1 |         |       1   (0)| 00:00:01 |

原目标sql现在依然只有其原执行计划（即对T2表的全表扫描）所对应的sql plan baseline
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%';
SQL_HANDLE          PLAN_NAME             ORIGIN     ENA ACC SQL_TEXT
------------------------- ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
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
SQL>

（2）
使用改写后的sql的新执行计划（强制索引hint）所对应的sql id 和plan hash_value 以及原目标sql的sql plan baseline的sql_handle来手工生成对应的sql plan baseline
SQL> exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id =>'795jg57h9tryx',plan_hash_value=>1306670842,sql_handle=>'SQL_75b06ae056223f5f');
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%';
SQL_HANDLE          PLAN_NAME             ORIGIN     ENA ACC SQL_TEXT
------------------------- ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_75b06ae056223f5f      SQL_PLAN_7bc3aw1b24guzb55f43d8 MANUAL-LOAD    YES YES select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4
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
SQL> 

（3）
drop 掉原执行计划（即对T2表的全表扫描）所对应的sql plan baseline
SQL> exec:temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_75b06ae056223f5f',plan_name=>'SQL_PLAN_7bc3aw1b24guzb860bcf2');
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%';
SQL_HANDLE          PLAN_NAME             ORIGIN     ENA ACC SQL_TEXT
------------------------- ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_75b06ae056223f5f      SQL_PLAN_7bc3aw1b24guzb55f43d8 MANUAL-LOAD    YES YES select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4
SQL> 

再次执行原目标sql
SQL> select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
SQL_ID    76td2qkxcrn9v, child number 1
Plan hash value: 1306670842
|*  2 |   INDEX RANGE SCAN            | IDX_T2 |       1 |         |       1   (0)| 00:00:01 |
Note
   - SQL plan baseline SQL_PLAN_7bc3aw1b24guzb55f43d8 used for this statement