Oracle SQL Patch – I

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;


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

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.


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';


1 row selected.



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
                passing (
                                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;

INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))
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'')

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

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

19 rows selected.



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

Hope this helps !!

5 thoughts on “Oracle SQL Patch – I

  1. mdinh December 15, 2012 / 6:23 pm

    Nice Post. Does this require licensing for D&T pack? Thanks.

  2. Faisal January 6, 2013 / 7:41 pm

    You talk about a procedure to cater for large SQL_IDs. Can you please advice where that could be found.

    • Faisal January 6, 2013 / 7:42 pm

      Apologies, I meant a proc for large SQL Text

  3. Nilesh January 16, 2013 / 10:52 am

    Where is that proc for large SQL Text?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s