sql tuning – Persistent Storage Solutions

Tracing Single SQL in Oracle

Published on May 25, 2015 by Advait1 Comment

Many times, while doing SQL tuning, we want to trace (event 10046) single SQL in database. Instead of going for module level tracing or session level using DBMS_MONITOR, we can simply use below alter system command to trace specific SQL

Example: I have a table T1 and index T_I_TABLE_NAME on that table.
I am running following SQL and I want to trace on this SQL

select * from T1 where table_name = 'SINGLE_PRODUCT_GROUPS';

I can just find out the SQL ID of above SQL

select sql_id, sql_text from v$sql where sql_text like '%SINGLE_PRODUCT_GROUPS%'

SQL_ID	      SQL_TEXT
------------- --------------------------------------------------------------------------------
8kybysnu4nn34 select * from T1 where table_name = 'SINGLE_PRODUCT_GROUPS'

Once I have the SQL ID, I can use below alter system to trace this SQL


alter system set events 'sql_trace[SQL:8kybysnu4nn34] plan_stat=all_executions,wait=true,bind=true';

Note that even though this is alter system, other SQL IDs run will not have any effect on tracing because we are tracing only specific SQL ID. So unless SQL ID 8kybysnu4nn34 is run, it will not generate any trace file.

Once SQL ID is run and trace is generated, you can turn off tracing using following statement


alter system set events 'sql_trace[SQL:8kybysnu4nn34] off';

This might generate multiple trace files as multiple sessions might run same SQL ID (depending on the application).

Hope this helps !!!

Oracle SQL Patch – I

Published on December 14, 2012 by Advait5 Comments

In my previous posts we have seen fixing plans by applying baselines and profiles.
For profiles we saw in details how to generate the same using SQL hints.

This article is about another feature of Oracle 11.2, called SQL Patch.
I am not sure if this is supported by Oracle, but in days to come they will make this official.
This is a kind of silver bullet for doing minor changes in the plan which is difficult to get it done using baselines and profiles.

What is SQL Patch:

A SQL patch is a SQL manageability object that can be generated by the SQL Repair Advisor, in order to circumvent a plan which causes a failure. In essence, a SQL patch tells the optimizer to change the plan in some way or avoid an optimization so that the failure does not occur. For instance, if a failure happens during index access, then generating a plan without that index (or without any index) may work around the failure. You can read more about this in the documentation here

But we are going to use SQL Patch to fix a query plan.

Lets take an example:

SQL>create table T as select * from dba_objects;

Table created.

SQL>create index T_IDX on T(object_ID);

Index created.

SQL>exec dbms_stats.gather_table_stats(USER,'T',cascade=>true);

PL/SQL procedure successfully completed.

SQL>explain plan for 
  2  select * from T where object_id = 10;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     1 |    89 |    62   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T     |     1 |    89 |    62   (2)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=10)

13 rows selected.

SQL>

We see that its going for a FTS for accessing the table.

Now, lets try to use patch so that same query will start using index.

We can use sys.dbms_sqldiag_internal.i_create_patch procedure to create patch
This procedure needs

– sql text
– hints to be applied
– catagory in which to save the patch
– name of the SQL Patch

If SQL text is too big, I have given a procedure at the end of this artical which can be used. It ask for SQL ID, child number and hint to be applied. Its very easy to use.

So lets try to create a SQL patch using sys.dbms_sqldiag_internal.i_create_patch procedure

SQL>exec sys.dbms_sqldiag_internal.i_create_patch(sql_text  => 'select * from T where object_id = 10',hint_text => 'INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))',category  => 'DEFAULT',name => 'PATCH_gz85dtvwaj4fw');

PL/SQL procedure successfully completed.

SQL>select count(1) from dba_sql_patches where name = 'PATCH_gz85dtvwaj4fw';

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

1 row selected.

SQL>

Here are the meanings of values I provided

sql_text – This is the text of the SQL. If text is too long, conside using PLSQL procedure provided at the end of the article.

hint_text – This is the hint we want to provide. Now this will seem different that what we usually provide in the SQL. If you are not sure of the exact hint, here is what you can do.

I used normal hint in my SQL to generate a plan

explain plan for
select /*+ index(T_IDX T) */ * from T where object_id = 10;

based on this you can parse other_xml column in plan_table using following SQL

SELECT regexp_replace(extractvalue(value(d), '/hint'),'''','''''') plan_hint
        from
        xmltable('/*/outline_data/hint'
                passing (
                        select
                                xmltype(other_xml) as xmlval
                        from    plan_table
                        where   other_xml is not null
                        and     plan_id = (select max(plan_id) from plan_table)
                        and     rownum=1
                        )
                ) d;

PLAN_HINT
--------------------------------------------------------------------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
OPT_PARAM(''optimizer_index_caching'' 80)
OPT_PARAM(''optimizer_index_cost_adj'' 1)
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

10 rows selected.

Once you get the output, you can pick the index hint from above – INDEX_RS_ASC(@”SEL$1″ “T”@”SEL$1” (“T”.”OBJECT_ID”))

Catagory can be any catagory you want to have. Usually everything should go to default if not specified.

Name of the patch can also be anything you want to give.

Now, if we check the plan for original query, it will start using index.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |      1 |     89 |    200   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |      1 |     89 |    200   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN        | T_IDX |      1 |        |    100   (0)| 00:00:02 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=10)

Note
-----
   - SQL patch "PATCH_gz85dtvwaj4fw" used for this statement
   - SQL plan baseline "SQL_PLAN_2anpx5hbuf3cbae82cf72" used for this statement

19 rows selected.

SQL>

Also, in the Note section you can see patch “PATCH_gz85dtvwaj4fw” is getting used for this statement.

Hope this helps !!

Fixing SQL Plans: The hard way – Part 3

Published on July 13, 2012July 13, 2012 by Advait3 Comments

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

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

Auto-Capture Baseline Behaviour

Published on June 28, 2012 by Advait1 Comment

This article is about demonstrating Auto-Capture baseline behavior, which I was surprised that many people are not aware.

Auto-Capture of baseline behaves in following way

Case 1) optimizer_capture_sql_plan_baselines parameter is TRUE

If you have optimizer_capture_sql_plan_baselines parameter set to TRUE, baselines for the SQLs will get captured automatically if SQL query runs 2 or more times. I believe all of us know about this feature

Case 2) optimizer_capture_sql_plan_baselines parameter is FALSE

If optimizer_capture_sql_plan_baselines parameter is set to FALSE, many people think that no new baseline will be captured in the database. This is WRONG. Even when this parameter is set to FALSE, baselines will be captured for following case

*** If a query has one or more baselines with ENABLED=YES ***

Lets take an example

SQL>create table T as select * from dba_objects;

Table created.

SQL>explain plan for 
  2  select count(1) from T where data_object_id between 1000 and 2000;

Explained.

SQL>@display

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2966233522

-----------------------------------
| Id  | Operation       | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|*  2 |   TABLE ACCESS FULL| T      |
-----------------------------------

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

   2 - filter("DATA_OBJECT_ID"<=2000 AND "DATA_OBJECT_ID">=1000)

Note
-----
   - rule based optimizer used (consider using cbo)

18 rows selected.

SQL>select count(1) from T where data_object_id between 1000 and 2000;

  COUNT(1)
----------
     0

SQL>/

  COUNT(1)
----------
     0
         
SQL>select sql_id from v$sql where sql_text like 'select count(1) from T where data_object_id between 1000 and%';

SQL_ID
-------------
7fbxxz894w0mg

SQL>@baseline
Enter sql ID:- 7fbxxz894w0mg

SQL_TEXT
----------------------------------------------------------------------------------------------------
select count(1) from T where data_object_id between 1000 and 2000


SQL_HANDLE               PLAN_NAME              CREATOR          ORIGIN         LAST_MODIFIED            ENA ACC FIX
------------------------------ ------------------------------ --------------- -------------- ------------------------------ --- --- ---
SYS_SQL_46f1e9bfbc331557       SYS_SQL_PLAN_bc3315573fdbb376  ORACLE_DBA      AUTO-CAPTURE   26-JUN-12 11.30.08.000000 AM   YES YES NO

SQL_HANDLE            PLAN_NAME                  PLAN_HASH_VALUE     ENABLED    ACCEPTED     FIXED
-------------------------   ----------------------------      --------------     -------    -------     -------
SYS_SQL_46f1e9bfbc331557    SYS_SQL_PLAN_bc3315573fdbb376     2966233522      YES         YES       NO

PL/SQL procedure successfully completed.

SQL>show parameters  capture

optimizer_capture_sql_plan_baselines    boolean         TRUE

SQL>alter system set optimizer_capture_sql_plan_baselines=false;

System altered.

SQL>create index T_IDX on T(data_object_id);

Index created.

SQL>select count(1) from T where data_object_id between 1000 and 2000;

  COUNT(1)
----------
     0

SQL>/

  COUNT(1)
----------
     0

         
SQL>@baseline
Enter sql ID:- 7fbxxz894w0mg

SQL_TEXT
----------------------------------------------------------------------------------------------------
select count(1) from T where data_object_id between 1000 and 2000


SQL_HANDLE               PLAN_NAME              CREATOR          ORIGIN         LAST_MODIFIED            ENA ACC FIX
------------------------------ ------------------------------ --------------- -------------- ------------------------------ --- --- ---
SYS_SQL_46f1e9bfbc331557       SYS_SQL_PLAN_bc3315573fdbb376  ORACLE_DBA      AUTO-CAPTURE   26-JUN-12 11.30.08.000000 AM   YES YES NO
SYS_SQL_46f1e9bfbc331557       SYS_SQL_PLAN_bc331557ded8ae2f  ORACLE_DBA      AUTO-CAPTURE   26-JUN-12 11.31.20.000000 AM   YES NO  NO

SQL_HANDLE            PLAN_NAME                  PLAN_HASH_VALUE     ENABLED    ACCEPTED     FIXED
-------------------------   ----------------------------      --------------     -------    -------     -------
SYS_SQL_46f1e9bfbc331557    SYS_SQL_PLAN_bc3315573fdbb376     2966233522      YES         YES       NO
SYS_SQL_46f1e9bfbc331557    SYS_SQL_PLAN_bc331557ded8ae2f     293504097      YES        NO         NO

PL/SQL procedure successfully completed.

SQL>

This proves that even when optimizer_capture_sql_plan_baselines parameter is set to false we can have baselines captured for our old SQL.

If we think about this feature, this is perfect.

Imagine you are rolling out a new module and new queries are going to run against new tables.

At first, initially you will not be having enough data in new tables and your queries will go with FTS (Full Table Scan) and will not use index, even if you create them. This is because based on value of optimizer_index_cost_adj parameter optimizer might make a decision to get data from table rather than going to index and than table.

If you have optimizer_capture_sql_plan_baselines set to TRUE, this is going to create baselines on those queries (which will be automatically accepted as first baselines are automatically accepted).

At later point when data grows and you really want the indexes to get used, your query will still be doing FTS (because of the auto-capture baseline in ACCEPTED state). So even if new baselines are getting captured, they are not getting used.

So you have to manually (or automatically) evolve those baselines to correct the plans.

Instead, my approach would be to enable optimizer_capture_sql_plan_baselines parameter when your database is really baked and you have enough data available. This way it will create baselines which will be first time right.

If your database is still not baked enough and you are looking for plan stability, I would suggest getting baselines from other similar prod database which is baked for long time.

Once you have baselines in place for almost all queries turn off optimizer_capture_sql_plan_baselines parameter. This will prevent baseline capture for new queries, at the same time this doesn’t stop new baselines from getting captured for old SQLs.

Hope this helps !!

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