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