To check SQL Profile :
select NAME,SIGNATURE ,STATUS,FORCE_MATCHING from dba_sql_profiles;
ENABLE/DISABLE/DROP
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(‘coe_5273fz2cqkk80_3455548535′,’STATUS’,’DISABLED’);
exec dbms_sqltune.drop_sql_profile(‘coe_5273fz2cqkk80_3455548535’);
SQL Profiles for a sql_id
set lines 1000 pages 9999
col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150
col signature for 9999999999999999999999999
select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created, prof.task_exec_name,prof.FORCE_MATCHING, prof.status, prof.SIGNATURE,prof.SQL_TEXT
from
dba_sql_profiles prof,
gv$sql sql
where sql.sql_id in (‘&sql_id’)
order by
created;
SQL Baselines:
Check the current baseline:
select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED ,sql_text from dba_sql_plan_baselines;
select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>’SQL_PLAN_b7pnq6yp8da6a29d0d9b7′));
Drop the baseline:
set serveroutput on
DECLARE
i NATURAL;
BEGIN
i := dbms_spm.drop_sql_plan_baseline(‘SQL_b3d69637aa86a8ca’);
dbms_output.put_line(i);
END;
/
Load the cursor:
variable sqlid number;
execute :sqlid :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>’5qbbnv0abm2vx’, PLAN_HASH_VALUE=> 4197102931, SQL_HANDLE => ‘SQL_d3318f33dfac7bc2’);
Enable/Disable:
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => ‘SYS_SQL_1447ba3a1d83920f’,
plan_name => ‘SYS_SQL_PLAN_1d83920fae82cf72’,
attribute_name => ‘ENABLED’,
attribute_value => ‘YES’);
DBMS_OUTPUT.put_line(‘Plans Altered: ‘ || l_plans_altered);
END;
/
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => ‘SYS_SQL_1447ba3a1d83920f’,
plan_name => ‘SYS_SQL_PLAN_1d83920fae82cf72’,
attribute_name => ‘ACCEPTED’,
attribute_value => ‘YES’);
DBMS_OUTPUT.put_line(‘Plans Altered: ‘ || l_plans_altered);
END;
/
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => ‘SQL_b3d69637aa86a8ca’,
plan_name => ‘SQL_PLAN_b7pnq6yp8da6a29d0d9b7’,
attribute_name => ‘FIXED’,
attribute_value => ‘YES’);
DBMS_OUTPUT.put_line(‘Plans Altered: ‘ || l_plans_altered);
END;
/
Fix baseline of one sql_id to another
variable sqlid number;
execute :sqlid :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>’31pux6bymf1d4’);
SQL> select sql_handle, plan_name, enabled from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA
SQL_d3318f33dfac7bc2 SQL_PLAN_d6ccg6ggusyy2ee7f0c12 YES
variable sqlid number;
exec :sqlid :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( SQL_HANDLE=> ‘SQL_d3318f33dfac7bc2’, PLAN_NAME => ‘SQL_PLAN_d6ccg6ggusyy2ee7f0c12’, ATTRIBUTE_NAME=> ‘enabled’, ATTRIBUTE_VALUE => ‘NO’);
Now fix the plan of 31pux6bymf1d4 to 5qbbnv0abm2vx
5qbbnv0abm2vx
variable sqlid number;
execute :sqlid :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>’5qbbnv0abm2vx’, PLAN_HASH_VALUE=> 4197102931, SQL_HANDLE => ‘SQL_d3318f33dfac7bc2’);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘5qbbnv0abm2vx’,&childnumber,’ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +OUTLINE +PREDICATE +COST +BYTES’));
4197102931
Extract hint from all profile/baseline/patch :
All in one script:
set pagesize 60
set linesize 180
set trimspool on
column plan_name format a32
column signature format 999,999,999,999,999,999,999
column category format a10
column hint format a70 wrap word
break on plan_name skip 1 on signature skip 1 on opt_type skip 1 on category skip 1 on plan_id skip 1
spool sql_profile_baseline_11g
select prf.plan_name,prf.signature,decode(obj_type,1,’Profile’,2,’Baseline’,3,’Patch’,
‘Other’) opt_type,prf.category,prf.plan_id,extractvalue(value(hnt),’.’) hint from
( select /*+ no_eliminate_oby */ so.name plan_name, so.signature,so.category,
so.obj_type,so.plan_id,sod.comp_data from
sqlobj$ so,sqlobj$data sod where so.name = ‘&m_plan_name’
and sod.signature = so.signature and sod.category = so.category
and sod.obj_type = so.obj_type and sod.plan_id = so.plan_id
order by signature, obj_type, plan_id ) prf, table ( select
xmlsequence( extract(xmltype(prf.comp_data),’/outline_data/hint’)
)from dual ) hnt;
Taking SQL HINTS from memory:
SELECT chr(9)||chr(9)||””||regexp_replace(extractvalue(value(d), ‘/hint’),””,”””)||”’,’
from
xmltable(‘/*/outline_data/hint’
passing ( select xmltype(other_xml) as xmlval
from v$sql_plan where
sql_id like nvl(‘&sql_id’,sql_id)
and child_number = &child_no
and other_xml is not null ) ) d;
Taking SQL HINTS from AWR:
SELECT chr(9)||chr(9)||””||regexp_replace(extractvalue(value(d), ‘/hint’),””,”””)||”’,’
from xmltable(‘/*/outline_data/hint’
passing ( select xmltype(other_xml) as xmlval
from
— v$sql_plan
dba_hist_sql_plan where sql_id like nvl(‘&sql_id’,sql_id) and other_xml is not null )) d;
Taking SQL HINTS from PROFILES
select hint as outline_hints
from (select p.name, p.signature, p.category, row_number()
over (partition by sd.signature, sd.category order by sd.signature) row_num,
extractValue(value(t), ‘/hint’) hint
from sqlobj$data sd, dba_sql_profiles p,
table(xmlsequence(extract(xmltype(sd.comp_data),
‘/outline_data/hint’))) t
where sd.obj_type = 1
and p.signature = sd.signature
and p.category = sd.category
and p.name like (‘&profile_name’))
order by row_num;
Taking SQL HINTS from PLAN_TABLE:
SELECT regexp_replace(extractvalue(value(d), ‘/hint’),””,”””) plan_hint
from xmltable(‘/*/outline_data/hint’
passing ( select xmltype(other_xml) as xmlval
from plan_table where other_xml is not null
and plan_id = (select max(plan_id) from plan_table)
and rownum=1 ) ) d;
Fixing the PLAN HASH VALUE:
Resolution normally, is to fix the execution plan in 11g by running
variable x number
begin
😡 :=
dbms_spm.load_plans_from_cursor_cache(
sql_id=>’&sql_id’,
plan_hash_value=>&plan_hash,
fixed=>’YES’);
end;
/
To see the hints from sql_profile
select hint from (
select s.sql_id, sd.obj_type,
row_number() over (partition by sd.signature, sd.category order by sd.signature) row_num,
extractValue(value(t), ‘/hint’) hint
from sqlobj$data sd, v$sql s,
table(xmlsequence(extract(xmltype(sd.comp_data), ‘/outline_data/hint’))) t
where sd.obj_type = 1
and s.exact_matching_signature = sd.signature
and s.sql_id = ‘&sql_id’)
order by row_num
/
Purge the old plan hash value from memory:
BEGIN
FOR i IN (SELECT address, hash_value
FROM gv$sqlarea WHERE sql_id = ‘&sql_id.’)
LOOP
SYS.DBMS_SHARED_POOL.PURGE(i.address||’,’||i.hash_value, ‘C’);
END LOOP;
END;
/
