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.