Fixing SQL Plans: The hard way – Part 1

Published on by Advait

This article is about fixing a plan for SQL statement in a hard way.

Till now we have seen that profiles can be created using DBMS_SQLTUNE package where

  1. we create a SQL tuning set
  2. Pack sql_ids into SQL tuning sets
  3. we create tuning task and provide SQL tuning set as inputs
  4. Finally after running tuning task we accept the profiles created.

Profiles consists of hints that a SQL can use it to arrive at a plan.

We can use same DBMS_SQLTUNE package and IMPORT_SQL_PROFILE procedure to import the required hints and apply to the SQL where we have to fix the plan.

Where are these hints stored ?

If you are taking about a sql, check V$SQL_PLAN, you will see a column OTHER_XML (CLOB). This has all the hints stored in XML form.

So logically if we have a good plan in any other database, we can just get the hints from that plan and use  it in our database so that same plan comes in our database (provided we have all the objects used by that plan exists in our database).

This scenario happens many times. We have several similar production databases and sometimes plan is good in some DBs where as in few DBs plan is entirely different (may be because of different stats or different environment or some parameter difference).

Making changes to stats or parameters or DB environment has a very big impact as it affects other SQLs. So if you have 1 or 2 SQLs which are having bad plans, its better to gett a better plan from some other DB and just apply to the affected DBs.

Here is another way to create a profile.

If we have a better plan in some other DB, we need to parse the OTHER_XML column for the SQL and extract the hints from that

This can be done using following SQL

select  CHR(9)||''''
        ||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
        || ''','
from    xmltable('/*/outline_data/hint'
                passing (select     xmltype(other_xml) as xmlval
                        from        v$sql_plan
                        where       sql_id = '&GOOD_SQL_ID'
                        and         CHILD_NUMBER = &CHILD_NO
                        and         other_xml is not null)) d;

In my case sql_id = 33fndgzsas09k and child_no = 0

I got following output

            'IGNORE_OPTIM_EMBEDDED_HINTS',
            'OPTIMIZER_FEATURES_ENABLE(''11.2.0.2'')',
            'DB_VERSION(''11.2.0.2'')',
            '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_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("TEST_ITEMS"."ISBN" "TEST_ITEMS"."TEST_ORDER_ID" "TEST_ITEMS"."OWNER"))',
            'INDEX_RS_ASC(@"SEL$1" "PCS"@"SEL$1" ("TEST_CUSTS"."TEST_WORK_ID"))',
            'INDEX(@"SEL$1" "SR"@"SEL$1" ("CUST_REQUESTS"."TEST_WORK_ID"))',
            'LEADING(@"SEL$1" "BI"@"SEL$1" "PCS"@"SEL$1" "SR"@"SEL$1")',
            'USE_NL(@"SEL$1" "PCS"@"SEL$1")',
            'USE_NL(@"SEL$1" "SR"@"SEL$1")',
            'NLJ_BATCHING(@"SEL$1" "SR"@"SEL$1")',

Above are the hints that used in the SQL. Note that these are not the sql hints that user supply. CBO internally creates a tree of hints (which we call a plan) and uses them to get the required data.

How do we make use of these hints ?

We will define a variable of type sys.sqlprof_attr (This is a VARRAY defined already in database) and put all the hints into this VARRAY

Once we get this VARRAY with all hints, we can apply the same to all SQLs that matches our criteria

Following PLSQL code will do that

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.2.0.2'')',
        'DB_VERSION(''11.2.0.2'')',
        '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_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("TEST_ITEMS"."ISBN" "TEST_ITEMS"."TEST_ORDER_ID" "TEST_ITEMS"."OWNER"))',
        'INDEX_RS_ASC(@"SEL$1" "PCS"@"SEL$1" ("TEST_CUSTS"."TEST_WORK_ID"))',
        'INDEX(@"SEL$1" "SR"@"SEL$1" ("CUST_REQUESTS"."TEST_WORK_ID"))',
        'LEADING(@"SEL$1" "BI"@"SEL$1" "PCS"@"SEL$1" "SR"@"SEL$1")',
        'USE_NL(@"SEL$1" "PCS"@"SEL$1")',
        'USE_NL(@"SEL$1" "SR"@"SEL$1")',
        'NLJ_BATCHING(@"SEL$1" "SR"@"SEL$1")',
        '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 = '&SQL_ID_TO_FIX'
	and p.plan_hash_value = &BAD_PLAN_HASH_VALUE
	and p.parent_id is null
)
loop
        DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
	sql_text    => sql_rec.sql_text,
	profile     => ar_profile_hints,
	name	    => 'PROFILE_33fndgzsas09k');

end loop;
end;
/

You can run above PLSQL code in a database where plan is bad and it should create a profile for that SQL. Once profile is created you can purge that SQL from shared_pool so that existing plan will be wiped off and run the SQL again.

This will create a new plan (good plan) same as the one we copied from other DB.

Above PLSQL code can generated directly using following SQL

Run the following SQL in database where plan is good and provide required inputs. This will output PLSQL code same as above (with all values in place). You can just copy and paste that PLSQL code on affected DB where plan is bad.

*** NOTE :- You need to run below SQL code in DB where plan is good.

accept HINTED_SQL_ID prompt 'Enter good SQL ID:- '
accept CHILD_NO prompt 'Enter child number of good SQL:- '
accept BAD_SQL_ID prompt 'Enter bad SQL ID to be fixed:- '
accept PLAN_HASH_VALUE prompt 'Enter bad SQL plan_hash_value:- '
set pagesize 0
set line 9999
set verify off;
set heading off;
set feedback off;
set echo off;
set pagesize 0
prompt '======================= OUTPUT ======================='
select CHR(10) from dual;
select  'declare'
        ||CHR(10)||CHR(9)
        ||'ar_profile_hints sys.sqlprof_attr;'
        ||CHR(10)
        ||'begin'||CHR(10)||CHR(9)
        ||'ar_profile_hints := sys.sqlprof_attr('||CHR(10)||CHR(9)
        ||'''BEGIN_OUTLINE_DATA'','
from    dual;
select  CHR(9)||''''
        ||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
        || ''','
from    xmltable('/*/outline_data/hint'
                passing (select     xmltype(other_xml) as xmlval
                        from        v$sql_plan
                        where       sql_id = '33fndgzsas09k'
                        and         CHILD_NUMBER = 0
                        and         other_xml is not null)) d;
select  CHR(9)
        ||'''END_OUTLINE_DATA'');'||CHR(10)||CHR(9)
        ||'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'||CHR(10)||CHR(9)
        ||'and p.sql_id = '''||'&BAD_SQL_ID'||'''' ||CHR(10)||CHR(9)
        ||'and p.plan_hash_value = '||&PLAN_HASH_VALUE ||CHR(10)||CHR(9)
        ||'and p.parent_id is null'||CHR(10)||') loop' ||CHR(10)
        ||'DBMS_SQLTUNE.IMPORT_SQL_PROFILE(' ||CHR(10)||CHR(9)
        ||'sql_text    => sql_rec.sql_text,'||CHR(10)||CHR(9)
        ||'profile     => ar_profile_hints,' ||CHR(10)||CHR(9)
        ||'name        => ''PROFILE_'||'&BAD_SQL_ID'||'''); '||CHR(10)||CHR(9)
        || 'end loop;'||CHR(10)|| 'end;'|| CHR(10)||'/'
from    dual;
select CHR(10) from dual;
prompt '======================= OUTPUT ======================='

Enter good SQL ID:- <This should be the SQL_ID with good plan. In our case its the same SQL ID but in different DB>

Enter child number of hinted SQL:- <This is the child number of the SQL in DB where plan is good>

Enter bad SQL ID to be fixed:- <This is same SQL ID as input 1 since we are checking plan for same SQL ID in other DB>

Enter bad SQL plan_hash_value:- <This is the plan hash value of SQL ID where plan is bad>

Example Run:

SQL>@create_profile_code.sql
Enter good SQL ID:- 33fndgzsas09k
Enter child number of good SQL:- 0
Enter bad SQL ID to be fixed:- 33fndgzsas09k
Enter bad SQL plan_hash_value:- 3225275398
'======================= 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.2.0.2'')',
        'DB_VERSION(''11.2.0.2'')',
        '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_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("TEST_ITEMS"."ISBN" "TEST_ITEMS"."TEST_ORDER_ID" "TEST_ITEMS"."OWNER"))',
        'INDEX_RS_ASC(@"SEL$1" "PCS"@"SEL$1" ("TEST_CUSTS"."TEST_WORK_ID"))',
        'INDEX(@"SEL$1" "SR"@"SEL$1" ("CUST_REQUESTS"."TEST_WORK_ID"))',
        'LEADING(@"SEL$1" "BI"@"SEL$1" "PCS"@"SEL$1" "SR"@"SEL$1")',
        'USE_NL(@"SEL$1" "PCS"@"SEL$1")',
        'USE_NL(@"SEL$1" "SR"@"SEL$1")',
        'NLJ_BATCHING(@"SEL$1" "SR"@"SEL$1")',
	'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 = '33fndgzsas09k'
	and p.plan_hash_value = 3225275398
	and p.parent_id is null
) loop
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
	sql_text    => sql_rec.sql_text,
	profile     => ar_profile_hints,
	name	    => 'PROFILE_33fndgzsas09k');
	end loop;
end;
/

'======================= OUTPUT ======================='

Hope this helps !!

14 thoughts on “Fixing SQL Plans: The hard way – Part 1”

  1. Pingback: Fixing SQL Plans: The hard way – Part 2 « IN ORACLE MILIEU …
  2. Pingback: Loading SQL Plans into SPM using AWR « Anand's Blog

  6. Hi Nilesh,

    Let me clear this up for you.

    Lets say you have a bad plan in your database. Good plan could be present in some other database (if you have more than 1 similar prod DBs).
    You take SQL hints from good plan and apply those SQL hints to SQL with bad plan.
    This way you create a profile and that fixes the plan. Same is explained in above article.

    Let me know if you got it, else I can give more detailed steps with example.

    Regards,

    Advait Deo

    Reply

    2. Hi Advait,

      Its still confusing for me. Could you please elaborate more on this.. Im not getting why we are providing the bad plan. And how it will work ?

      Reply
  7. Pingback: Fixing SQL Plans: The hard way – Part 3 « IN ORACLE MILIEU …
  8. Pingback: Fixing SQL plans on ADG using SQL Profiles – Persistent Storage Solutions

  10. If you have a plan to migrate the prod db to an another server and after successful migration the app team wants to execute the job on the migrated database, that same sql_id will be generated since both source and target db’s are same.

    Reply

  11. Nice post, very informative.
    Just to confirm, these steps will work only if we have same plan(plan_hash_value) which is performing good on DB1, but performing bad on DB2. correct ?
    DB1 – phv1 – good.
    DB2 – phv1 – bad.

    Reply

Leave a reply to Dba From France Cancel reply