Oracle重新绑定执行计划,Oracle 执行计划快速绑定

declare

ar_profile_hints sys.sqlprof_attr;

cl_sql_text clob;

begin

select 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 = ‘0g1n4yx6yprgk’

and PLAN_HASH_VALUE = ‘4170700152’

and other_xml is not null)) d;

select sql_fulltext

into cl_sql_text

from v$sql

where sql_id = ‘dtgz3afzvz5rc’ --需要绑定的sql_id

and PLAN_HASH_VALUE = 3956160932;

dbms_sqltune.import_sql_profile(sql_text => cl_sql_text,

profile => ar_profile_hints,

–category => ‘DEFAULT’,

name => ‘profile_dtgz3afzvz5rc’,

force_match => true,–该参数为true对于未绑定变量的SQL可以使用相同的profile

replace => true);

end;

/