接上文，上文通过了spm进行了执行计划的固定，本文通过sqlprofile进行固定。sqlprofile 10g中使用的比较多。

Oracle优化器辅助手段的发展

Oracle 8：HINTOracle 8&9: STORED OUTLINESOracle 10: SQL PROFILEOracle 11: SPM(SQL Plan Management)Oracle 12:自适应计划

实验sql

select object_id,object_name from yz11.a1 a where object_id=46; 由于手动修改了统计信息，导致此sql使用全表扫描select /*+ index(a,A_IND1) */object_id,object_name from yz11.a1 a where object_id=46; 强制使用索引 性能好

全表扫描的sql

set linesize 200set termout offalter session set statistics_level=all;select object_id,object_name from yz11.a1 a where object_id=46;select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));

由于统计信息的问题，此sql使用了全表扫描

记录sqlid及hashvalueSQL_ID18hq9454u23s3, child number 0-------------------------------------select object_id,object_name from yz11.a1 a where object_id=46Plan hash value: 2676145672

使用hint强制使用索引

set linesize 200set termout offalter session set statistics_level=all;select /*+ index(a,A_IND1) */object_id,object_name from yz11.a1 a where object_id=46select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));

SQL_ID975v7hgh9fj6q, child number 0-------------------------------------select /*+ index(a,A_IND1) */object_id,object_name from yz11.a1 a whereobject_id=46Plan hash value: 59315297优化的sql执行计划

sqlprofile绑定的格式

使用sql profile绑定执行计划--参考declare ar_profile_hints sys.sqlprof_attr; clsql_text CLOB;beginselect extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval from dba_hist_sql_plan where sql_id = 'SQLID 值'   --好的执行计划对应的SQL ID and plan_hash_value = value 值  --好的执行计划对应SQL ID 的hash value值 and other_xml is not null)) d; SELECT sql_text INTO clsql_text FROM dba_hist_sqltext where sql_id = ' SQLID 值';   --需要绑定的SQL ID的值,通过视图查询该SQL 对应的文本 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => clsql_text, profile => ar_profile_hints, name => 'PROFILE_ SQLID 值',  --SQL Profile绑定的标记 force_match => TRUE,——true 表示对于谓词部分 具体值变化后的 SQL_ID 也能使用该 SQL profile REPLACE => TRUE);end;/

执行绑定sqlprofile

declare ar_profile_hints sys.sqlprof_attr; clsql_text CLOB;beginselect extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval from v$sql_plan where sql_id = '975v7hgh9fj6q' and plan_hash_value = '59315297' and other_xml is not null)) d; SELECT sql_text INTO clsql_text FROM v$sql where sql_id = '18hq9454u23s3'; DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => clsql_text,  profile => ar_profile_hints,  name => 'PROFILE_ SQLID 18hq9454u23s3', force_match => TRUE, REPLACE => TRUE);end;/

查看sqlprofile信息

select * from dba_sql_profiles WHERE name ='PROFILE_ SQLID 18hq9454u23s3';

验证sqlprofile是否生效

再次执行sql语句select object_id,object_name from yz11.a1 a where object_id=46

已经使用了sqlprofile进行绑定执行计划了。