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.

Comments:

Fantastic! :)

Posted by guest on March 09, 2012 at 08:13 AM PST #

Thanks for taking followup questions,

How to drop the patch ?

Posted by Js on March 12, 2012 at 09:37 AM PDT #

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?

Posted by Craig on March 16, 2012 at 11:36 AM PDT #

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!

Posted by guest on April 19, 2012 at 04:08 AM PDT #

How do you drop the patch?

Posted by Peter Wiseman on June 24, 2012 at 07:51 PM PDT #

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.

Posted by guest on July 16, 2012 at 08:07 AM PDT #

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

Posted by guest on May 24, 2013 at 01:14 PM PDT #

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.

Posted by Allison on May 24, 2013 at 01:53 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today