执行计划固定（sql profile）

查看sql是否被执行计划绑定
select sql_profile,sql_plan_baseline from v$sql where sql_id=‘xx‘

declare
v_hints sys.sqlprof_attr;
cl_sql_text clob;
wrong_sql_id varchar2(50):=‘259au46k8mbku‘;--错误的sql_id
right_sql_id varchar2(50):=‘259au46k8mbku‘;--正确的sql_id
right_child_num number:=4;
right_PLAN_HASH_VALUE number :=2573941063;--正确执行计划的hash plan 
begin
select extractvalue(VALUE(d), ‘/hint‘) bulk collect
into v_hints
from xmltable(‘/*/outline_data/hint‘ passing
(select xmltype(other_xml) as xmlval
from dba_hist_sql_plan
where sql_id = right_sql_id
and PLAN_HASH_VALUE=right_PLAN_HASH_VALUE
and other_xml is not null)) d;
select sql_fulltext
into cl_sql_text
from v$sql
where sql_id = wrong_sql_id
and Rownum = 1;
dbms_sqltune.import_sql_profile(cl_sql_text,
v_hints,
‘P_‘||wrong_sql_id,
force_match => true,
replace => true);
end;
/

执行计划固定（sql profile）

原文：https://www.cnblogs.com/muzisanshi/p/11889727.html