SPM and Profiles –Part 3

Fix for  ”My Explain plan and Execution plan are different”

You might think , that i got possessed with baselines and profiles but I like exploring them.

The deeper I explore, the more fun it is.

In the last post, I had covered how to convert profiles baselines into profiles.

Taking a step further this time, I tried to sync my explain plan with execution plan.

A bit of history:-

Some days back, I ran into issue where explain plan was looking very much optimal but when I executed the query, it was picking different plan.

So, i thought why not get a way to force the plan of explain plan during execution.

In other words, why not sync my explain plan and execution plan.

So, i started exploring plan_table and voila….there it was. My favorite column other_xml.

By now, you must have realized, that oracle stores all the hints and some other stuff like (plan_hash2->This i will explain in some other post) in this column. When you use dbms_xplan, oracle parses this column and print in readable format.

I too extracted the info in my desired format from this column and create a script(xplanprofile.sql) to generate profile.

xplanprofile.sql

Session1> explain plan for select * from master_log where task_id=:b1;
Session1>@xplanprofile
enter original sql_id:- 9ghy678ujy345
enter bad plan hash value to be tuned :- 3709384
'===================OUTPUT========================='

declare
ar_profile_hints sys.sqlprof_attr;
begin
 ar_profile_hints := sys.sqlprof_attr(
 'BEGIN_OUTLINE_DATA',
 'INDEX_RS_ASC(@"SEL$1" "MASTER_LOG"@"SEL$1" ("MASTER_LOG"."TASK_ID"))',
 'OUTLINE_LEAF(@"SEL$1")',
 'OPT_PARAM(''optimizer_index_caching'' 80)',
 'OPT_PARAM(''optimizer_index_cost_adj'' 1)',
 '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.2.0.2'')',
 '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 = 'sehy678ujy345' and p.plan_hash_value = 3709384 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;
/