脚本内容
-- 给特定SQL绑上AWR中已有的特定执行计划,需要事先查询得到对应的sql_id 和 plan_hash_value, COE_profile.sql的极简版
declare
v_hint SYS.SQLPROF_ATTR;
v_sql_text clob;
p_bad_sqlid varchar2(32) default '&bad_sqlid';
begin
select sql_text into v_sql_text from dba_hist_sqltext where sql_id = p_bad_sqlid and rownum=1;
select extractvalue(value(d), '/hint') as outline_hints bulk collect
into v_hint
from xmltable('/*/outline_data/hint' passing
(select xmltype(other_xml) as xmlval
from dba_hist_sql_plan
where sql_id = '&good_sqlid' and plan_hash_value = '&good_hash'
and other_xml is not null)) d;
dbms_sqltune.import_sql_profile(
v_sql_text,v_hint,'sql_profile_&bad_sqlid',force_match=>true,replace=>true);
end;
/
评论
贡献排行榜