Fixing SQL Plans: The hard way – Part 3

In part 1 of this series https://avdeo.com/2012/06/20/fixing-sql-plans-the-hard-way-part-1/ I showed you how to fix a SQL plan by picking good plan hints from other database and applying those hints/plan to your existing bad query

In part 2 of this series https://avdeo.com/2012/07/04/fixing-sql-plans-the-hard-way-part-2/ I showed you how to fix a SQL plan even if you don’t have another database having better plan. I explained you about hint you can use in the query and execute the query to get better plan. Then we can use the new plan generated and apply to the original SQL.

In this article we can go 1 step further. What if you don’t want to execute the SQL. May be because SQL gives too much of output or takes lot of time to complete.

We can generate the profile hints without even executing the SQL. This is possible using plan table

I am just explaining the steps which will be changed in this case

Once you identify the hint to be used, put the hint in the query and take explain plan

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

Explained.

SQL>@display

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1789076273

--------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |       1 |      12 |     953K  (1)| 00:38:35 |
|   1 |  SORT AGGREGATE          |         |       1 |      12 |          |         |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T     | 16441 |     192K|     953K  (1)| 00:38:35 |
|*  3 |    INDEX RANGE SCAN         | T_IDX | 32883 |         |    8105   (3)| 00:00:20 |
--------------------------------------------------------------------------------------

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

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

16 rows selected.

SQL>

Now if you check the plan table, you will have a column in plan_table called OTHER_XML

SQL>set long 9999
SQL>select other_xml from plan_table ;

OTHER_XML
--------------------------------------------------------------------------------

<other_xml><info type="db_version">11.2.0.2</info><info type="parse_schema"><![C
DATA["ORACLE_DBA"]]></info><info type="plan_hash">1789076273</info><info type="
plan_hash_2">388377992</info><outline_data><hint><![CDATA[INDEX_RS_ASC(@"SEL$1"
"T"@"SEL$1" ("T"."DATA_OBJECT_ID"))]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1
")]]></hint><hint><![CDATA[OPT_PARAM('optimizer_index_caching' 80)]]></hint><hin
t><![CDATA[OPT_PARAM('optimizer_index_cost_adj' 9999)]]></hint><hint><![CDATA[OP
T_PARAM('_optimizer_connect_by_cost_based' 'false')]]></hint><hint><![CDATA[OPT_
PARAM('_optim_peek_user_binds' 'false')]]></hint><hint><![CDATA[OPT_PARAM('_b_tr
ee_bitmap_plans' 'false')]]></hint><hint><![CDATA[DB_VERSION('11.2.0.2')]]></hin
t><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]]></hint><hint><![CDATA[I
GNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>

SQL>

In the above query of plan table, I am assuming there will be only 1 plan.

Now we got the required plan in the form of XML which we used to get from V$SQL_PLAN.OTHER_XML

All we have to do now if follow same steps to parse this PLAN_TABLE.OTHER_XML column, get the hints and apply to original SQL.

Following are the steps

Get the required hints. Please note that I have used PLAN_TABLE now instead of V$SQL_PLAN. Also no need to give any SQL ID here as its going to get the hints of the query for which we did “explain plan for”

select  CHR(9)||''''
        ||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
        || ''','
from    xmltable('/*/outline_data/hint'
                passing (select     xmltype(other_xml) as xmlval
                        from        PLAN_TABLE
                        where       other_xml is not null
                        and         rownum < 2)) d;

So in this case I get following hints

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

Rest of the procedure remains the same. You can use either V$SQL_PLAN and V$SQL or DBA_HIST_SQL_PLAN and DBA_HIST_SQLTEXT where ever the query is present so that profile will get generated.

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.

We basically tried to get the hints of a different plan (using index) just by using “explain plan for” to get the required hints from PLAN_TABLE. 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 !!

Advertisements

ORA-00600: internal error code, arguments: [kkdlGetCkyName1] – On ADG After Renaming Index

I encountered wired error on my Active Dataguard (ADG) instance after renaming an index on primary

Here is a simple test case to reproduce the issue

On Primary:

SQL>create table T as select * from dba_objects;

Table created.

SQL>create index T_IDX on T(data_object_id);

Index created.

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>explain plan for
  2  select count(1) from T where data_object_id > 65000;

Explained.

SQL>@display

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 293504097

---------------------------------------------------------------------------
| Id  | Operation      | Name  | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    1 |    5 |    2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |    1 |    5 |           |      |
|*  2 |   INDEX RANGE SCAN| T_IDX | 29118 |   142K|    2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("DATA_OBJECT_ID">65000)

14 rows selected.

SQL>select count(1) from T where data_object_id > 65000;

  COUNT(1)
----------
     28337

On Standby:

 

SQL>select count(1) from T where data_object_id > 65000;

  COUNT(1)
----------
     28337

Now rename the index on primary

On Primary:

SQL>alter index T_IDX rename to T_IDX1;

Index altered.

SQL>

Now run the same query on standby

On Standby:

SQL>select count(1) from T where data_object_id > 65000;
select count(1) from T where data_object_id > 65000
                     *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkdlGetCkyName1], [1622537], [],
[], [], [], [], [], [], [], [], []

DCYYZ1>

This is happening for following version

SQL>select * from PRODUCT_COMPONENT_VERSION;

PRODUCT                  VERSION                  STATUS
---------------------------------------- ---------------------------------------- ----------------------------------------
NLSRTL                     11.2.0.2.0                  Production
Oracle Database 11g Enterprise Edition     11.2.0.2.0                  64bit Production
PL/SQL                     11.2.0.2.0                  Production
TNS for Linux:                 11.2.0.2.0                  Production

Solution: Oracle is working on Bug 13035388: ORA 600 [KKDLGETCKYNAME1] IN ADG for fixing this issue

Workaround: Rename back the index to original name. Or drop and recreate the index.

Hope this helps !!

Fixing SQL Plans: The hard way – Part 2

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 !!