X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Additional Information on SQL Patches

Our last post on SQL patches generated a lot of comments and questions.  Today I will address a couple of questions that are quick to answer.  For those that require more discussion, we'll post more details in the next few weeks.

1) Do we need additional licenses or the SQL diag pack to use this?  No.  No additional licenses are needed to use SQL Repair Advisor or SQL patches. SQL Repair Advisor is available as part of Oracle Database Enterprise Edition 11g.

2) Can I supply a SQL_ID instead of SQL text?  The API requires that the SQL text be passed in.  Keep in mind that the SQL patch will be applied to SQL statements that match this SQL text after normalization.  We use the same normalization that is used for SQL profiles, where the whitespace and the case (of non-literals) are normalized.

Here's a quick example to show what I mean.  In the last post, we showed an example SQL patch created with an index hint, and saw the patch kick in for this query:

If we add some whitespace, and change the case of some of the query, we will still use the SQL patch:

So even though the SQL ID of the two queries is different, they both use the SQL patch.  This is why you cannot supply a SQL ID -- we aren't associating the patch with a SQL ID, but a (normalized) SQL text.

You can get a copy of the script I used to generate this post here.

Join the discussion

Comments ( 8 )
  • guest Friday, March 9, 2012

    Fantastic! :)


  • Js Monday, March 12, 2012

    Thanks for taking followup questions,

    How to drop the patch ?


  • Craig Friday, March 16, 2012

    If you wanted to add multiple hints in the same SQL Patch, what would that syntax look like when executing dbms_sqldiag_internal.i_create_patch?


  • guest Thursday, April 19, 2012

    I just wanted to let you know that we used this sql patching mechanism our 11.1.0.7 database in production and it worked like a charm. We actually used it to NOT use a particular index. Query was using a wrong index.

    I think this is the best feature in the database since they added analytic functions. This beats SQL plan management hands down. :) Thank you! You are awesome!


  • Peter Wiseman Monday, June 25, 2012

    How do you drop the patch?


  • guest Monday, July 16, 2012

    It would be nice if I could use this feature to add GATHER_PLAN_STATISTICS hint dynamically to any sql. I tested it and it doesn't work. I tested MONITOR hint and it works. But someone of us don't have Tuning license to use that.


  • guest Friday, May 24, 2013

    I was able to inject gather_plan_statistics into a sql statement – that could be handy

    set linesize 256;

    set pagesize 100;

    select count(*) from dual;

    select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

    ------------------------------------------

    | Id | Operation | Name | E-Rows |

    ------------------------------------------

    | 0 | SELECT STATEMENT | | |

    | 1 | SORT AGGREGATE | | 1 |

    | 2 | FAST DUAL | | 1 |

    ------------------------------------------

    Note

    -----

    - Warning: basic plan statistics not available. These are only collected when:

    * hint 'gather_plan_statistics' is used for the statement or

    * parameter 'statistics_level' is set to 'ALL', at session or system level

    begin

    sys.dbms_sqldiag_internal.i_create_patch

    (sql_text => 'select count(*) from dual',

    hint_text => 'gather_plan_statistics',

    name => 'nicks test');

    end;

    /

    I did have to force a reparse

    alter system flush shared_pool;

    set linesize 256;

    set pagesize 100;

    select count(*) from dual;

    select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

    Plan hash value: 3910148636

    -------------------------------------------------------------------------

    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |

    -------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |

    | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |

    | 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 |

    -------------------------------------------------------------------------

    Note

    -----

    - SQL patch "nicks test" used for this statement


  • Allison Friday, May 24, 2013

    As Nick pointed out, this should work with the gather_plan_statistics hint.

    To drop a SQL patch, you can use the (documented) function dbms_sqldiag.drop_sql_patch. You can also disable a SQL patch using dbms_sqldiag.alter_sql_patch.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services