需要通过脚本coe_xfr_sql_profile.sql 来生成sql profile文本。该脚本来自oracle metalink[ID 215187.1]“

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

执行计划错误的sql：

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

执行计划正确的sql :

select /*+index(t_cs_ctm)*/* from t_cs_ctm 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_cs_ctm t where t.product_no = '13411111111' ； sql_id=br1mv51g21cxw

执行计划正确的sql :

select /*+index(t_cs_ctm)*/* from t_cs_ctm 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 different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );。
最后我们运行coe_xfr_sql_profile_br1mv51g21cxw_3677037978.sql这个脚本文件：

SQL> @ coe_xfr_sql_profile_br1mv51g21cxw_3677037978.sql

4、补充知识

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

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

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

exec dbms_sqltune.drop_sql_profile('coe_br1mv51g21cxw_3677037978');
                               -------------------------------------------------------------------------- write by Sunny Hu