SPM and Profiles -Part2

Converting baselines into profiles

In my last post, we have discussed various workarounds to fix the execution plan of the query if its not picking baseline.

Of the given 3 methods, I was interested in the 3rd one. What got me interested is the idea if we can create profiles from  baselines. 

The next obvious question is “why you need to do so”.

The 2 reasons are

1. Baselines not getting used. In this case you can create a profile out of baseline and then impose oracle to use this profile.

2. In 11g, we have ADG (active standby database). Guess what!!! Baselines doesn’t work in ADG. So if you fix query on primary, it won’t pick the plan in standby. So you better create a profile.  Also you may need to bounce your standby in order for profile to get used (because of another bug).

Before I talk about script to convert baselines into profiles, lets step back and think whats is that thing which is common in baselines and profiles(if at all there is) and what is that thing which makes them different.

Both baselines and profiles store data in form of hints.

Only difference is in case of baselines oracle tries to regenerate the same plan using hints and if it can’t then it will not use the baseline.

However, in case of profile, oracle will use the hints to generate the execution plan which it will use. Therefore, there is no guarantee that oracle will land on same plan again and again and hence plan flip can occur.

I will soon write about hints and how oracle stores them for a given plan.

Anyway, coming back to our point, oracle stores the execution plan in form of hints. Therefore, for each plan it captures or generates, it must store them somewhere. The idea is to get those hints and create the profile.

planprofile.sql:-

Session1>select plan_name from dba_sql_plan_baselines where rownum < 2;

PLAN_NAME
------------------------------
SYS_SQL_PLAN_086da2eaeec7bdef

1 row selected.

Session1>@planprofile
enter sql_id to be fixed:- dgh672sad89j4
baseline plan name:- SYS_SQL_PLAN_086da2eaeec7bdef
enter bad plan hash value to be tuned :- 12345678
'===================OUTPUT========================='

declare
ar_profile_hints sys.sqlprof_attr;
begin
 ar_profile_hints := sys.sqlprof_attr(
 'BEGIN_OUTLINE_DATA',
 'FULL(@"SEL$4" "MRH"@"SEL$4")',
 'NO_ACCESS(@"SEL$3" "from$_subquery$_007"@"SEL$3")',
 'FULL(@"SEL$6" "MRH"@"SEL$6")',
 'NO_ACCESS(@"SEL$5" "from$_subquery$_004"@"SEL$5")',
 'PQ_DISTRIBUTE(@"SEL$F5BB74E1" "RMS"@"SEL$2" NONE BROADCAST)',
 'USE_NL(@"SEL$F5BB74E1" "RMS"@"SEL$2")',
 'LEADING(@"SEL$F5BB74E1" "H"@"SEL$1" "RMS"@"SEL$2")',
 'INDEX_RS_ASC(@"SEL$F5BB74E1" "RMS"@"SEL$2" ("METRICS_SUMMARY95"."MVIEW_NAME" "METRICS_SUMMARY95"."OWNER"))',
 'NO_ACCESS(@"SEL$F5BB74E1" "H"@"SEL$1")',
 'PQ_DISTRIBUTE(@"MRG$1" "H"@"MRG$1" BROADCAST NONE)',
 'PQ_DISTRIBUTE(@"MRG$1" "RMS"@"MRG$1" NONE BROADCAST)',
 'USE_MERGE_CARTESIAN(@"MRG$1" "H"@"MRG$1")',
 'USE_MERGE_CARTESIAN(@"MRG$1" "RMS"@"MRG$1")',
 'LEADING(@"MRG$1" "from$_subquery$_010"@"MRG$1" "RMS"@"MRG$1" "H"@"MRG$1")',
 'NO_ACCESS(@"MRG$1" "H"@"MRG$1")',
 'FULL(@"MRG$1" "RMS"@"MRG$1")',
 'NO_ACCESS(@"MRG$1" "from$_subquery$_010"@"MRG$1")',
 'OUTLINE(@"SEL$2")',
 'OUTLINE(@"SEL$1")',
 'OUTLINE_LEAF(@"MRG$1")',
 'OUTLINE_LEAF(@"SEL$5")',
 'OUTLINE_LEAF(@"SEL$6")',
 'MERGE(@"SEL$2")',
 'OUTLINE_LEAF(@"SEL$F5BB74E1")',
 'OUTLINE_LEAF(@"SEL$3")',
 'OUTLINE_LEAF(@"SEL$4")',
 'OPT_PARAM(''optimizer_index_caching'' 80)',
 'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
 'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
 'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
 'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
 'DB_VERSION(''11.1.0.7'')',
 'OPTIMIZER_FEATURES_ENABLE(''11.1.0.7'')',
 'IGNORE_OPTIM_EMBEDDED_HINTS',
 'END_OUTLINE_DATA'
);
for sql_rec in (
 select t.sql_id, t.sql_text
 from dba_hist_sqltext t, dba_hist_sql_plan p
 where t.sql_id = p.sql_id and p.sql_id = 'dgh672sad89j4' and p.plan_hash_value = 12345678 and p.parent_id is null
 ) loop
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
 sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => 'PROFILE_'||sql_rec.sql_id
 );
 end loop;
end;
/

That was simple. Isn’t it??

But i was not satisfied. I wanted to take this a step further. Now i want oracle to use whatever plan I want.

In case oracle had changed plan , I want oracle to use previous plan. 

In one of my database, planflip caused cpu spikes. Buffer_gets/exec increased from thousands to millions. The idea was to force old to use previous plan .

oldprofile.sql

</pre>
Sesion1>@oldprofile
enter sql_id:- 9ddv4vbt2zrxy
enter old plan hash value:- 1236769290
enter bad plan hash value to be tuned :- 3891475932
'===================OUTPUT========================='

declare
ar_profile_hints sys.sqlprof_attr;
begin
 ar_profile_hints := sys.sqlprof_attr(
 'BEGIN_OUTLINE_DATA',
 'IGNORE_OPTIM_EMBEDDED_HINTS',
 'OPTIMIZER_FEATURES_ENABLE(''11.1.0.7'')',
 'DB_VERSION(''11.1.0.7'')',
 'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
 'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
 'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
 'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
 'OPT_PARAM(''optimizer_index_caching'' 80)',
 'OUTLINE_LEAF(@"SEL$1")',
 'INDEX(@"SEL$1" "CCS"@"SEL$1" ("COMPLETED_CUSTOMER_SHIPMENTS"."CONDITION" "COMPLETED_CUSTOMER_SHIPMENTS"."LAST_UPDATED"))',
 'END_OUTLINE_DATA'
);
for sql_rec in (
 select t.sql_id, t.sql_text
 from dba_hist_sqltext t, dba_hist_sql_plan p
 where t.sql_id = p.sql_id and p.sql_id = '9ddv4vbt2zrxy' and p.plan_hash_value = 3891475932 and p.parent_id is null
 ) loop
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
 sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => 'PROFILE_'||sql_rec.sql_id
 );
 end loop;
end;
/

Oldprofile.sql

OldProfile.sql:- This script can be used to force oracle to use the given plan by creating profile over top of it.

/*
Name :- Oldprofile.sql
Db Version :- 11.1.0.7
Author :- Sumit Bhatia
Version :- 1.1
Purpose :- Create a profile to force the old plan
Input :- sql_id, old plan hash value, bad plan hash value
*/
set feedback off
set echo off
set timing off
set pagesize 0
set heading off verify off
accept sql_id prompt ‘enter sql_id:- ‘
accept old_plan_hash_value prompt’enter old plan hash value:- ‘
accept plan_hash prompt’enter bad plan hash value to be tuned :- ‘
prompt ‘===================OUTPUT=========================’
select chr(10) from dual;

select ‘declare’ from dual;
select ‘ar_profile_hints sys.sqlprof_attr;’ from dual;
select ‘begin’ from dual;
select chr(9)||’ar_profile_hints := sys.sqlprof_attr(‘ from dual;
select chr(9)||chr(9)||”’BEGIN_OUTLINE_DATA”,’ from dual;

SELECT chr(9)||chr(9)||””||regexp_replace(extractvalue(value(d), ‘/hint’),””,”””)||”’,’
from
xmltable(‘/*/outline_data/hint’
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id like nvl(‘&sql_id’,sql_id)
and plan_hash_value=&old_plan_hash_value
and other_xml is not null
and rownum < 2
)
)
d;

select chr(9)||chr(9)||”’END_OUTLINE_DATA”’||chr(10)||’);’||chr(10)||’for sql_rec in (‘||chr(10)||chr(9)||’select t.sql_id, t.sql_text’||chr(10)||chr(9)||’ from dba_hist_sqltext t, dba_hist_sql_plan p’ ||chr(10)||chr(9)|| ‘where t.sql_id = p.sql_id and p.sql_id = ”&sql_id” and p.plan_hash_value = &plan_hash and p.parent_id is null’||chr(10)||chr(9)||chr(9)||’) loop’||chr(10)||chr(9)||’ DBMS_SQLTUNE.IMPORT_SQL_PROFILE(‘||chr(10)||chr(9)||chr(9)||’sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => ”PROFILE_”||sql_rec.sql_id’||CHR(10)||chr(9)||chr(9)||chr(9)||chr(9)||chr(9)||’);’||chr(10)||chr(9)||’end loop;’||chr(10)||’end;’||chr(10)||’/’ FROM DUAL;
prompt ‘===================OUTPUT=========================’
set heading on
set pages 500
undef sql_id
undef old_plan_hash_value
undef plan_hash

planprofile.sql

Profile.sql:- This script can be used to convert baseline into profile. Output can be directly pasted to create a profile.

/*
Name :- PlanProfile.sql
Db Version :- 11.1.0.7
Author :- Sumit Bhatia
Version :- 1.1
Purpose :- Create a profile from baseline
Input :- sql_id, baseline plan name, bad plan hash value
*/
set feedback off
set echo off
set timing off
set pagesize 0 lines 500
set heading off verify off
accept sql_id prompt ‘enter sql_id to be fixed:- ‘
accept plan prompt’baseline plan name:- ‘
accept plan_hash prompt’enter bad plan hash value to be tuned :- ‘
prompt ‘===================OUTPUT=========================’
select chr(10) from dual;

select ‘declare’ from dual;

select ‘ar_profile_hints sys.sqlprof_attr;’ from dual;

select ‘begin’ from dual;
select chr(9)||’ar_profile_hints := sys.sqlprof_attr(‘ from dual;
select chr(9)||chr(9)||”’BEGIN_OUTLINE_DATA”,’ from dual;
SELECT chr(9)||chr(9)||””||regexp_replace(extractValue(value(h),’.'),””,”””)||”’,’ FROM sys.sqlobj$data od, sys.sqlobj$ so, table(xmlsequence(extract(xmltype(od.comp_data),’/outline_data/hint’))) h WHERE so.name = ‘&plan’ AND so.signature = od.signature AND so.category = od.category AND so.obj_type = od.obj_type AND so.plan_id = od.plan_id;

select chr(9)||chr(9)||”’END_OUTLINE_DATA”’||chr(10)||’);’||chr(10)||’for sql_rec in (‘||chr(10)||chr(9)||’select t.sql_id, t.sql_text’||chr(10)||chr(9)||’ from dba_hist_sqltext t, dba_hist_sql_plan p’ ||chr(10)||chr(9)|| ‘where t.sql_id = p.sql_id and p.sql_id = ”&sql_id” and p.plan_hash_value = &plan_hash and p.parent_id is null’||chr(10)||chr(9)||chr(9)||’) loop’||chr(10)||chr(9)||’ DBMS_SQLTUNE.IMPORT_SQL_PROFILE(‘||chr(10)||chr(9)||chr(9)||’sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => ”PROFILE_”||sql_rec.sql_id’||CHR(10)||chr(9)||chr(9)||chr(9)||chr(9)||chr(9)||’);’||chr(10)||chr(9)||’end loop;’||chr(10)||’end;’||chr(10)||’/’ FROM DUAL;
prompt ‘===================OUTPUT=========================’
set heading on
set pages 500
undef sql_id
undef plan
undef plan_hash