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

SPM & Profiles-Part 1

SPM And Profiles-Part 1

Ever encountered issue when your query does not pick baseline. In some cases, even after setting fixed=Yes, it does not pick baseline.

This may happen when you try fixing the query using hints and try to link the plan of hinted query with original query.

This post is intended to provide you the solutions for some of those issues. (And like every post a script for the dba’s.)

And most important, do we need profiles now.

Why oracle is not picking my baseline

There are several reasons(and bugs) why oracle  does not pick baselines.

In my case, I tried to manually tune the query using hints and linked the plan with the original query. However, oracle refused to pick the baseline. 

In many cases reason could be

1. Bind Variables used in the query
2. Aliases used  for the table

Solution 1:-

Using bind variables prevents oracle from generating multiple versions of the same query and helps in reducing hard parsing. But what if the same bind variables creates trouble for you.

If  your application is using bind variable or cursor_sharing is set to “similar”or “force”, then your query should look like this.

For the test case, I will use mytab table

Session1> select  * from mytab a where a.generated=:b1;

no rows selected

You can always check the values of bind variables using following query.

Session2>select name,value_string from v$sql_bind_capture where sql_id='&sql_id' and child_number='&child';

Enter value for sql_id: 3uj38fuby1shm
Enter value for child: 0
NAME                 VALUE_STRING
-------------------- ------------------------------
:B1                  FOR
1 row selected.

In cases, where query is using bind variables like the one given above, use following approach to tune the query and the link the baseline

var b1 varchar2(20);
exec :b1:='FOR';
Session1 >select /*+ index(a MYTAB_IDX2) */ * from mytab a where a.generated=:b1;
no rows selected

Now, we can link the plan of the tuned sql with the original sql as described in earlier post.

Solution 2:-

You may need to tune the sql’s running inside plsql packages and procedures. In those cases, bind variables name could be like “:1″ which is hard to tune using traditional approach of baselines.

e.g. select * from mytab a where a.generated=:1;

To tune those sql’s you need to create a anonymous block and create a cursor like one given below.

declare
sql_stmt varchar2(4000);
id varchar2(20);
TYPE CurTyp  IS REF CURSOR;
tmpcursor    CurTyp;
begin
   id:='&id_val';
  sql_stmt:='select /*+ index(a MYTAB_IDX2) */ * from mytab where generated=:1';
  open tmpcursor for sql_stmt using id;
end;
/
Enter value for id_val: FOR

PL/SQL procedure successfully completed.

Now, you can find the sql_id of the query and link the baseline with original sql.

Session1>select sql_id,module,sql_text from v$sql where upper(sql_text) like upper('%&text%') and ( sql_text not like lower('%v$sql%') and sql_text not like '%explain plan%') group by sql_id,module,sql_text;
Enter value for text: select /*+ index(a MYTAB_IDX2) */ * from mytab where generated=:1

SQL_ID        MODULE                    SQL_TEXT
------------- ------------------------- ------------------------------------------------------------------------------------------
5vkq50nxn4gsj SQL*Plus                  declare sql_stmt varchar2(4000); id varchar2(20); TYPE CurTyp  IS REF CURSOR; tmpcursor
                                         CurTyp; begin    id:='FOR';   sql_stmt:='select /*+ index(a MYTAB_IDX2) */ * from mytab w
                                        here generated=:1';   open tmpcursor for sql_stmt using id; end;

9z3dv649q3gvy SQL*Plus                  select /*+ index(a MYTAB_IDX2) */ * from mytab where generated=:1

2 rows selected.

You can link the plan of 9z3dv649q3gvy with original query to tune it.

Solution 3:-

The second solution of using cursor may work well if query is small or using limited number of bind variables. But for page long queries, its cumbersome to concatenate multiple bind variables.

We can use profiles to bypass second approach. (profile.sql)

Profile.sql will ask for sql_id and child number of hinted sql and the sql_id and plan hash value of the bad sql_id. You can paste the output directly to create a profile.

Session1>@profile
enter hinted sql_id:- 9z3dv649q3gvy
enter child number:- 0
enter original sql_id:- 9jutgyhjutefd
enter bad plan hash value to be tuned :- 663241269
'===================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'' 1)',
                'OPT_PARAM(''optimizer_index_caching'' 80)',
                'OUTLINE_LEAF(@"SEL$1")',
                'INDEX_RS_ASC(@"SEL$1" "MYTAB"@"SEL$1" ("MYTAB"."GENERATED"))',
                '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 = '9jutgyhjutefd' and p.plan_hash_value = 663241269 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;
/

You can paste the output directly to fix the sql.