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; 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’);
exec :sqlid :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( SQL_HANDLE=> 'SQL_d3318f33dfac7bc2', PLAN_NAME => 'SQL_PLAN_d6ccg6ggusyy2ee7f0c12', ATTRIBUTE_NAME=> 'enabled', ATTRIBUTE_VALUE => 'NO');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
Hi Ram,
Tuning scripts are very useful to work for daily day to day performance activity.
I really appreciate for you great effort and work. Keep post these kind of stuffs.
Regards
Santhoshkumar
Thanks for your support
Thanks for your efforts..!
Can you explain what is sql baseline and sql profile ?
SQL Profile
In Oracle a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure best performance. In essence the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables etc. When you run the SQL Tuning Advisor for the list of recommendation you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick best execution plans at run time.
SQL Baseline
SQL Baseline on the other hand is more of a brute force method, when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but instances where data skew is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.
Thanks for your efforts..!
Can you explain what is sql baseline and sql profile ?
SQL Profile
In Oracle a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure best performance. In essence the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables etc. When you run the SQL Tuning Advisor for the list of recommendation you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick best execution plans at run time.
SQL Baseline
SQL Baseline on the other hand is more of a brute force method, when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but instances where data skew is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.
What is difference between sql profile and sql base line?
SQL PROFILES : Adjust Optimizer cardinality estimations
SPM BASELINES : Direct SQL to follow specific execution plan
SQL Profile
In Oracle a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure best performance. In essence the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables etc. When you run the SQL Tuning Advisor for the list of recommendation you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick best execution plans at run time.
SQL Baseline
SQL Baseline on the other hand is more of a brute force method, when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but instances where data skew is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.
SQL PROFILES : Adjust Optimizer cardinality estimations
SPM BASELINES : Direct SQL to follow specific execution plan
SQL Profile
In Oracle a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure best performance. In essence the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables etc. When you run the SQL Tuning Advisor for the list of recommendation you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick best execution plans at run time.
SQL Baseline
SQL Baseline on the other hand is more of a brute force method, when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but instances where data skew is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.
What is difference between sql profile and sql base line?
SQL PROFILES : Adjust Optimizer cardinality estimations
SPM BASELINES : Direct SQL to follow specific execution plan
SQL Profile
In Oracle a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure best performance. In essence the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables etc. When you run the SQL Tuning Advisor for the list of recommendation you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick best execution plans at run time.
SQL Baseline
SQL Baseline on the other hand is more of a brute force method, when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but instances where data skew is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.
SQL PROFILES : Adjust Optimizer cardinality estimations
SPM BASELINES : Direct SQL to follow specific execution plan
SQL Profile
In Oracle a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure best performance. In essence the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables etc. When you run the SQL Tuning Advisor for the list of recommendation you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick best execution plans at run time.
SQL Baseline
SQL Baseline on the other hand is more of a brute force method, when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but instances where data skew is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.
nice job
nice job
good job, I love oracleagent.wordpress.com !
https://oracleagent.wordpress.com/2021/02/01/sql-profiles-baselines/
good job, I love oracleagent.wordpress.com !
https://oracleagent.wordpress.com/2021/02/01/sql-profiles-baselines/