delmatch oracle_oracle稳定执行计划（更改）的方法

应用于那些执行计划已经发生了的不好的变更的SQL上(在不改变SQL文本的情况下，改变其执行计划)，即便通过创建SQL Profile解决了目标SQL执行计划变更的问题，依然不能保证系统后续执行的SQL的执行计划会发生不好的变更。

a. Automatic类型的SQL Profile

1、先针对SQL创建一个名为my_sql_tuning_task_1的自动调整任务：

declare

my_task_name varchar2(30);

my_sqltext clob;

begin

my_sqltext := '目标SQL';

my_task_name := dbms_sqltune.create_tuning_task(

sql_text => my_sqltext,

user_name => 'SCOTT',

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => my_task_name,

description => 'Task to tune a query on table t1');

end;

/

2、接着执行上述自动调整任务

begin

dbms_sqltune.execute_tuning_task(task_name => 'my_sql_tuning_task_1');

end;

/

3、然后使用dbms_sqltune.report_tuning_task来查看上述自动调整任务的调整结果

SET LONG 9000

SET LONGCHUNKSIZE 1000

SET LINESIZE 800

select dbms_sqltune.report_tuning_task('my_sql_tuning_task_1') from dual;

4、上述Automatic类型的SQL Profile所产生的调整结果如果是我们想要的，只需要按Oracle的提示接受这个SQL Profile

execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_1', task_owner => 'SCOTT',replace => TRUE, fore_match => true)

备注：这里fore_match的值设为true的含义，是指即使目标SQL的where条件中具体的输入值发生了改变(比如n=1变为n=2)，原SQL Profile依然有效，这就相当于将目标SQL的where条件中具体的输入值用绑定变量替换了。

b. Manual 类型的SQL Profile(一堆Hint的组合)

同样可以在不该SQL文本的情况下，改变其执行计划。更重要的是，Manual类型的SQL Profile可以起到很好的稳定目标SQL的执行计划的作用，这一点是Automatic类型的SQL Profile所不具备的。

我们使用脚本 coe_xfr_sql_profile.sql 可以针对目标SQL生成Manual类型的SQL Profile，并且通过“偷梁换柱”的方式在不修改目标SQL文本的情况下调整其执行计划。

1、改写SQL文本，在其中加入合适的Hint,走出我们想要的执行计划

2、查询出原目标SQL和改写后的SQL所对应的SQL ID 和 PLAN HASH VALUE

select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'SQL文本';

3、针对目标SQL使用脚本 coe_xfr_sql_profile.sql 产生能生成其Manual类型的SQL Profile的脚本A (传入相应的SQL ID 和 PLAN HASH VALUE)

4、对加入合适Hint后的SQL使用脚本 coe_xfr_sql_profile.sql 产生能生成其Manual类型的SQL Profile脚本B (传入相应的SQL ID 和 PLAN HASH VALUE)

5、用脚本B中Outline Data部分的Hint组合替换掉脚本A中的Outline Data部分的Hint组合。(注意将参数force_match改为force_match => TRUE)

6、执行脚本A生成针对原目标SQL的Manual类型SQL Profile.