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
:x :=
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;
/

All in one script

Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter: https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8