利用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');