import sql profile – Persistent Storage Solutions

Fixing SQL Plans: The hard way – Part 2

Published on July 4, 2012 by Advait3 Comments

In my previous article – https://avdeo.com/2012/06/20/fixing-sql-plans-the-hard-way-part-1/ – I showed you a way to fix query plan if you have a good plan available for that query in some other similar prod DB.

But what if you don’t have any other prod DB, or this query is so unique that it runs in only 1 database.

Fixing such SQLs needs deeper look into the SQL and the plan.

We have various methods to fix those SQLs like:-

– Creating Index

– Adding Hint (may be at the code level).

– Gathering stats etc.

Lets say you have all index in place, but query is not picking the index. Again, there could be many reasons why index is not picked by the query. Common one includes incorrect stats or missing stats for index. Or optimizer_index_cost_adj value is too high.

Changing any parameters or gathering stats or any changes to optimizer environment can have adverse affect to other queries.

Atleast in prod its not advisable to change any of optimizer environment.

One of the best way to fix queries in such scenario is to use hint so that index will be picked by CBO (cost based optimizer).

Having said that even if we use hints in query for CBO to pick the right index, how are we going to push such plan to actual query. We cannot (and should not) change application code and put a hint in application code.

Query should essentially remains the same but it should pick the index.

Fixing query using Hints

We want a solution where we will put hints in a query and generate the desired plan. We want original SQL ID to pick our newly generated plan.

Here is the example:

SQL>create table T as select * from dba_objects;

Table created.

SQL>col name format a30;
SQL>col value format a30;
SQL>select name, value from v$parameter where name = 'optimizer_index_cost_adj';

NAME                   VALUE
------------------------------ ------------------------------
optimizer_index_cost_adj       1

SQL>alter session set optimizer_index_cost_adj = 10000;

Session altered.

SQL>create index T_IDX on T(data_object_id);

Index created.

SQL>

SQL>exec dbms_stats.gather_table_stats('ORACLE_DBA','T');

PL/SQL procedure successfully completed.

SQL>exec dbms_stats.gather_index_stats('ORACLE_DBA','T_IDX');

PL/SQL procedure successfully completed.

SQL>select count(1) from T where data_object_id > 65000 and status = 'VALID';

  COUNT(1)
----------
     32103

SQL>select count(1) from T where data_object_id > 65000 and status = 'VALID';

  COUNT(1)
----------
     32103

SQL>select sql_id from v$sql where sql_text like 'select count(1) from T where data_object_id > 65000 and status%';

SQL_ID
-------------
cj4sqr25b6b8k

SQL>@explain
Enter SQL ID:- cj4sqr25b6b8k

PL/SQL procedure successfully completed.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    cj4sqr25b6b8k, child number 0
-------------------------------------
select count(1) from T where data_object_id > 65000 and status = 'VALID'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      |      |   258 (100)|      |
|   1 |  SORT AGGREGATE    |      |    1 |    11 |           |      |
|*  2 |   TABLE ACCESS FULL| T      | 17690 |   190K|   258   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("STATUS"='VALID' AND "DATA_OBJECT_ID">65000))

19 rows selected.

SQL>

Now we know that since optimizer_index_cost_adj value is too high, it will not try to use index T_IDX on data_object_id column, because query will be expensive.

But what if we want our query to use the index. This may not be a good test case, but there could be scenario where using index will actually improve the performance of query. CBO may not be able to find that (based on cost), but we know that.

So lets force a index usage using a hint.

SQL>select /*+ index(T T_IDX) */ count(1) from T where data_object_id > 65000 and status = 'VALID';

  COUNT(1)
----------
     32103

SQL>select /*+ index(T T_IDX) */ count(1) from T where data_object_id > 65000 and status = 'VALID';

  COUNT(1)
----------
     32103

SQL>select sql_id from v$sql where sql_text like 'select /*+ index(T T_IDX) */ count(1) from T where data_object_id > 65000 and status%';

SQL_ID
-------------
dut61hdv6b12t

SQL>@explain
Enter SQL ID:- dut61hdv6b12t

PL/SQL procedure successfully completed.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    dut61hdv6b12t, child number 0
-------------------------------------
select /*+ index(T T_IDX) */ count(1) from T where data_object_id >
65000 and status = 'VALID'

Plan hash value: 1789076273

--------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |         |         |     267K(100)|         |
|   1 |  SORT AGGREGATE          |         |       1 |      11 |          |         |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T     | 17690 |     190K|     267K  (1)| 00:53:31 |
|*  3 |    INDEX RANGE SCAN         | T_IDX | 35380 |         |    8253   (1)| 00:01:40 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"='VALID')
   3 - access("DATA_OBJECT_ID">65000)

22 rows selected.

SQL>

We can see that using index hint here, it has forced a plan to use index.
Now, we can easily get the required query hints from v$sql_plan view as we have other_xml column.
You can refer to the same SQLs as I mentioned in previous post (https://avdeo.com/2012/06/20/fixing-sql-plans-the-hard-way-part-1/) and get the required hints

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 this case GOOD_SQL_ID = dut61hdv6b12t and child number = 0

    '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'' 10000)',
    'OPT_PARAM(''optimizer_index_caching'' 80)',
    'OUTLINE_LEAF(@"SEL$1")',
    'INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."DATA_OBJECT_ID"))',

Rest of the procedure remains the same. Only change I did in the below PLSQL code compared to PLSQL code in my previous post is that, I changed dba_hist_sqltext table with v$sql and dba_hist_sql_plan table with v$sql_plan view. This is because new query we ran with hints may not be part of DBA_HIST* views as they are new query and will be present in only v$ views.

Following PLSQL code can be used

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'' 10000)',
    'OPT_PARAM(''optimizer_index_caching'' 80)',
    'OUTLINE_LEAF(@"SEL$1")',
    'INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."DATA_OBJECT_ID"))',
    'END_OUTLINE_DATA');
    for sql_rec in (
    select t.sql_id, t.sql_text
    from v$sql t, v$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_&&SQL_ID_TO_FIX');

end loop;
end;
/
Enter value for sql_id_to_fix: cj4sqr25b6b8k
Enter value for bad_plan_hash_value: 2966233522
Enter value for sql_id_to_fix: cj4sqr25b6b8k

PL/SQL procedure successfully completed.

In this case SQL_ID_TO_FIX will be our original SQL = cj4sqr25b6b8k and BAD_PLAN_HASH_VALUE will be plan hash of original SQL = 2966233522

So the code at the end that I mentioned in my previous post – https://avdeo.com/2012/06/20/fixing-sql-plans-the-hard-way-part-1/ can be run on same database instead of running on other database. Only change will be to use v$ views instead of DBA_HIST* views.

If we check the plan of original query

First purge the sql from shared pool using “sys.dbms_shared_pool.purge” so that old plan will get flushed out and than run the query to see new plan

SQL>@purgesql
Enter SQL_ID:- cj4sqr25b6b8k

PL/SQL procedure successfully completed.

SQL>select count(1) from T where data_object_id > 65000 and status = 'VALID';

  COUNT(1)
----------
     32103

SQL>select count(1) from T where data_object_id > 65000 and status = 'VALID';

  COUNT(1)
----------
     32103

SQL>@explain
Enter SQL ID:- cj4sqr25b6b8k

PL/SQL procedure successfully completed.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    cj4sqr25b6b8k, child number 0
-------------------------------------
select count(1) from T where data_object_id > 65000 and status = 'VALID'

Plan hash value: 1789076273

--------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |         |         |     267K(100)|         |
|   1 |  SORT AGGREGATE          |         |       1 |      11 |          |         |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T     | 17690 |     190K|     267K  (1)| 00:53:31 |
|*  3 |    INDEX RANGE SCAN         | T_IDX | 35380 |         |    8253   (1)| 00:01:40 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"='VALID')
   3 - access("DATA_OBJECT_ID">65000)

Note
-----
   - SQL profile PROFILE_cj4sqr25b6b8k used for this statement


25 rows selected.

SQL>

We basically tried to simulate a different plan (using index) to get the required hints from V$SQL_PLAN. We then forced those hints on original SQL to create a profile so that original SQL will start using index.

Hope above procedure if clear in understanding !!

Fixing SQL Plans: The hard way – Part 1

Published on June 20, 2012July 4, 2012 by Advait14 Comments

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

we create a SQL tuning set
Pack sql_ids into SQL tuning sets
we create tuning task and provide SQL tuning set as inputs
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 !!