When one of the db got upgraded from 10202 to 10205, we found out one of the query is very slow. Didn’t taken the export of stats before upgrade. This is one way we forced a profile to this query.
declare
pln_sql_id varchar2(20) :='fathgmks3vb1g';
pln_plan_hash_value number := 3396554601;
orig_sql_id varchar2(20) := 'fathgmks3vb1g';
new_prof_name varchar2(20) := 'SQL_PROFILE_1';
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 dba_hist_sql_plan
where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value
and other_xml is not null)) d;
select sql_text into cl_sql_text
from dba_hist_sqltext where sql_id = orig_sql_id;
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
name => new_prof_name,
force_match => true);
end;
/
The below script can be used to see what’s the access path for a hash_value. This can be seen when you create outln and query ol_hint$
select
substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
hash_value = 2378699969
and child_number = 0
and other_xml is not null
)
) d;
You need to drop any sql_profile created before forcing a plan.
exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_014a4a1e346a8004', ignore => TRUE);
convert this post to pdf.