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;
/
