Oracle固定SQL的执行计划(一)(3)

注意到Note部分有这样的内容“SQL profile SYS_SQLPROF_015a82b353490000 used for this statement”这说明我们刚才接受的SQL Profile已经起了作用,该SQL Profile的名字为SYS_SQLPROF_015a82b353490000。从执行计划中也可以看到,执行计划确实已经改变了。

另外,DBMS_SQLTUNE.ACCEPT_SQL_PROFILE的输入参数force_match的默认值为FALSE,表示只有在SQL文本完全匹配的情况下才会应用SQL Profile,这种情况下只要目标SQL的SQL文本发生一点变动,原有的SQL Profile将会失去作用,如果设置为TRUE,即使SQL有变动SQL Profile也会强制生效。

删除SQL Profile

zx@MYDB>exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_015a82b353490000');

 

PL/SQL procedure successfully completed.

2. Manual类型的SQL Profile

Manual类型的SQL Profile本质上就是一堆Hint的组合,这一堆Hint的组合实际上来源于执行计划中的Outline Data部分的Hint组合。Manual类型的SQL Profile同样可以在不更改目标SQL的SQL文本的情况下,调整其执行计划,而且更为重要的是,Manual类型的SQL Profile可以起到很好稳定目标SQL的执行计划的作用,这一点是Automatic类型的SQL Profile所不具备的。

看一个使用Manual类型的SQL Profile实例固定执行计划的实例,使用上面的t1表,删除上面的SQL Profile,再次执行SQL

zx@MYDB>select /*+no_index(t1 idx_t1) */ * from t1 where n=1;

 

         N

----------

         1

wKioL1i09kTDbaLYAAAon2Umz2g502.png

从上述输出可以看出执行计划仍然走全表扫描。

现在来创建Manual类型的SQL Profile。这里使用了MOS上的一个脚本coe_xfr_sql_profile.sql。这个脚本用于从Shared Pool、AWR Repository中指定SQL的指定执行计划的Outline Data部分的Hint组合,来创建Manual类型的SQL Profile。

使用coe_xfr_sql_profile.sql脚本的步骤为

针对目标SQL使用coe_xfr_sql_profile.sql产生能生成其Manual类型的SQL Profile的脚本A。

改写目标SQL的文本,在其中使用合适的Hint,直到加入Hint后的SQL能走出我们想要的执行计划。然后对加入合适Hint后的SQL使用脚本coe_xfr_sql_profile.sql,产生能生成其Manual类型的SQL Profile的脚本B。

用脚本B中的Outline Data部分的Hint组合替换掉脚本A的Outline Data部分的Hint组合。

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

现在改写上面的SQL,强制走索引:

zx@MYDB>select /*+index(t1 idx_t1) */ * from t1 where n=1;

 

         N

----------

         1

wKioL1i09FKC2sQHAAAp5hXwOKc945.png

从执行计划中可以看出SQL Id和对应的Plan hash value。

全表扫描的SQL Id:6chcc0pvvhqqm Plan hash value:3617692013

索引扫描的SQL Id:2ufquy7xs5nm5 Plan hash value:1369807930

a. 先使用coe_xfr_sql_profile.sql生成全表扫描SQL对应的脚本

zx@MYDB>@scripts/coe_xfr_sql_profile.sql

 

Parameter 1:

SQL_ID (required)

 

Enter value for 1: 6chcc0pvvhqqm

 

 

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

     3617692013        .002

 

Parameter 2:

PLAN_HASH_VALUE (required)

 

Enter value for 2: 3617692013

 

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID         : "6chcc0pvvhqqm"

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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/6c687b34ebc8b4a4f1d8d6283235a4d8.html