sql_profile固定执行计划

利用sql_profile得到正确的sql执行计划

select * fromt_XXXX  t where t.pno='18900000001'执行计划很慢，原因是此sql执行计划错误，本来应该走pno的索引，但是实际执行过程中是全表扫描。

需要通过脚本coe_xfr_sql_profile.sql （下载sqlt.zip 解压，sqlt/utl/）来生成sql profile文本。该脚本来自oracle metalink[ID 215187.1]

 

1、得到正确执行计划的outdata

执行计划错误的sql：

select * from t_info_customer t where t.product_no= '13411111111' ； sql_id=br1mv51g21cxw 

执行计划正确的sql :

select /*+index(t_info_customer)*/* fromt_info_customer t where t.product_no = '13411111111' ; sql_id=afag4gj277b39

1.1 使用coe_xfr_sql_profile.sql脚本来提取我们构造的SQL的Outline Data

运行coe_xfr_sql_profile.sql

SQL> @coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

-------有正确执行计划的sql_id

Enter value for 1: afag4gj277b39

PLAN_HASH_VALUE AVG_ET_SECS

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

     3536615365 .023

Parameter 2:

PLAN_HASH_VALUE (required)

Enter value for 2: 3536615365

Values passed to coe_xfr_sql_profile:

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

SQL_ID : "afag4gj277b39"

PLAN_HASH_VALUE: "3536615365"

这里会得到一个命名为coe_xfr_sql_profile_+sql_id+_+PLAN_HASH_VALUE.sql的文件，即：coe_xfr_sql_profile_afag4gj277b39_3536615365.sql

1.2得到正确sql_profile

SQL>@coe_xfr_sql_profile_afag4gj277b39_3536615365.sql

SQL>WHENEVER SQLERROR CONTINUE

SQL>SET ECHO OFF;

           SIGNATURE

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

 17293648302761054394

           SIGNATUREF

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

  2880340287165392331

... manual custom SQL Profile has been created

2、得到执行计划错误的sql的outline data

2.1 使用coe_xfr_sql_profile.sql脚本来提取我们构造的SQL的Outline Data

运行coe_xfr_sql_profile.sql

SQL> @coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

-------有正确执行计划的sql_id

Enter value for 1: br1mv51g21cxw

PLAN_HASH_VALUE AVG_ET_SECS

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

     3677037978 .023

Parameter 2:

PLAN_HASH_VALUE (required)

Enter value for 2: 3677037978

Values passed to coe_xfr_sql_profile:

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

SQL_ID : "afag4gj277b39"

PLAN_HASH_VALUE: "3677037978"

执行结果：coe_xfr_sql_profile_br1mv51g21cxw_3677037978.sql

2.2得到正确sql_profile

SQL>@ coe_xfr_sql_profile_br1mv51g21cxw_3677037978.sql

3、替换正确的sql_profile
执行计划错误的sql：

select * from t_info_customer t where t.product_no= '13411111111' ； sql_id=br1mv51g21cxw 

执行计划正确的sql :

select /*+index(t_info_customer)*/* fromt_info_customer t where t.product_no = '13411111111' ; sql_id=afag4gj277b39

 

 

coe_xfr_sql_profile_afag4gj277b39_3536615365.sql 好的执行计划

coe_xfr_sql_profile_br1mv51g21cxw_3677037978.sql 坏的执行计划

 

坏的执行计划的sql 生成的sql_profile是coe_xfr_sql_profile_br1mv51g21cxw_3677037978.sql，手工修改这个文件，将里面h:= SYS.SQLPROF_ATTR…那一段替换成coe_xfr_sql_profile_afag4gj277b39_3536615365.sql中得到的那一段。

将这个文件中的force_match从FALSE改成TRUE （force_match => FALSE /* TRUE:FORCE (match even when differentliterals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );。
最后我们运行coe_xfr_sql_profile_br1mv51g21cxw_3677037978.sql这个脚本文件：

SQL> @ coe_xfr_sql_profile_br1mv51g21cxw_3677037978.sql

 4、验证执行计划

explain plan for select * from t_info_customer t where t.product_no = '13411111111'; 

select * from table(dbms_xplan.display);
-------select * from table(dbms_xplan.display_cursor('fy9h3hwjvfy1c',null,'outline'));

5 、补充知识

DBMS_SQLTUNE.DROP_SQL_PROFILE( name =>'my_sql_profile' );

通过select * from dba_sql_profiles a;查询出PROFILE 的NAME,

然后执行下面的可以删除对应的sql_profile

execdbms_sqltune.drop_sql_profile('coe_br1mv51g21cxw_3677037978');