SQL Patch IV – Why is the SQL Patch applied but not effective?

(or Part II Addendum)

In a comment on one of my previous articles about SQL Patches, a reader (hurrah!) effectively asks the question “Why is my SQL Patch being applied but the hint not obeyed?”

The original article itself was long enough without me adding a long comment so I’d like to break out into a fresh post.

Here is the test case.

Setup:

SQL> create table a_test as select * from dba_tables;

Table created.

SQL> create index a_test_idx on a_test(table_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'a_test');

PL/SQL procedure successfully completed.

SQL> select table_name from a_test where table_name='xxx';

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d4knkaxjhqpgw, child number 0
-------------------------------------
select table_name from a_test where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("TABLE_NAME"='xxx')

Create a patch to hint a FULL TABLE SCAN:

SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch
  3     (sql_text  => 'select table_name from a_test a where table_name=''xxx''',
  4      hint_text => 'full(a)',
  5      name      => 'patch_test');
  6  end;
  7  /

PL/SQL procedure successfully completed.

See that the patch is applied but the hint apparently not obeyed:

SQL> select table_name from a_test a where table_name='xxx';

no rows selected

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("TABLE_NAME"='xxx')

Note
-----
   - SQL patch "patch_test" used for this statement

The issue is that to use a SQL Patch to hint a statement, it seems that you have to be very careful to use the complete hint specification.

The complete hint specification is an ugly beast and should be considered a strong indicator that you really want to think twice before doing any hinting, and especially as Oracle versions march onwards and the Optimizer gets more complicated, the complete hint specification becomes ever more important.
See Jonathan Lewis’s How to Hint post for a good illustration.

Back to the test case…

If we drop the existing sql patch:

SQL> begin
  2   sys.dbms_sqldiag.drop_sql_patch('patch_test');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Use the +OUTLINE format option for DBMS_XPLAN to get a nudge in the right direction:

SQL> select table_name from a_test a where table_name='xxx';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(format=> '+OUTLINE'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 4)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "A"@"SEL$1" ("A_TEST"."TABLE_NAME"))
      END_OUTLINE_DATA
  */

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

   1 - access("TABLE_NAME"='xxx')

And create our patch with the complete specification:

SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch
  3     (sql_text  => 'select table_name from a_test a where table_name=''xxx''',
  4      hint_text => 'FULL(@"SEL$1" "A"@"SEL$1")',
  5      name      => 'patch_test');
  6  end;
  7  /

PL/SQL procedure successfully completed.

We should find that it is more effective:

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 3679270240

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |    40 (100)|          |
|*  1 |  TABLE ACCESS FULL| A_TEST |     1 |    18 |    40   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("TABLE_NAME"='xxx')

Note
-----
   - SQL patch "patch_test" used for this statement


22 rows selected.

SQL> 

Bear in mind that the above is a test case only and consider what I said in the original article about what I consider to be the difference between a SQL Patch and a SQL Plan Baseline is and the different use cases.

SQL Patch III / Parallel Baselines

From my previous investigation, I had one outstanding question at the back of my mind:

Can we apply more than one hint with a SQL Patch?

In addition, I had previously noted that SQL Plan baselines have issues if you have a plan that has to use a parallel hint to get parallel operations.

By investigating the latter question, we can answer the former.

Initial setup, same as examples in previous post.

SQL> create table t1  
  2  (col1  number  
  3  ,col2  varchar2(200)  
  4  ,flag  varchar2(1));  

Table created.

SQL> 
SQL> insert into t1  
  2  select rownum  
  3  ,      lpad('X',200,'X')  
  4  ,      case when mod(rownum,10000) = 0  
  5              then 'Y'  
  6              else 'N'  
  7         end  
  8  from   dual  
  9  connect by rownum <= 100000;  

100000 rows created.

SQL> create index i1 on t1 (col1);

Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'T1'); 

PL/SQL procedure successfully completed.

SQL> 

Let’s run a piece of code that does an index access:

SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   208 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)


19 rows selected.

SQL> 

What about if I want to hint this to a parallel full table scan?

SQL> select /*+ full(t1) parallel(t1 4) */ * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  chhkmc32mdkak, child number 0
-------------------------------------
select /*+ full(t1) parallel(t1 4) */ * from t1 where col1 = 1

Plan hash value: 2494645258

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   264 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | P->S | QC
|   3 |    PX BLOCK ITERATOR |          |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

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

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1"=1)


22 rows selected.

SQL> 

As expected.

Now, can I preserve this with a baseline?

SQL> declare     
  2    sqltext clob;     
  3    spm_op pls_integer;     
  4  begin     
  5    sqltext := 'select * from t1 where col1 = 1';     
  6    spm_op  :=     
  7    dbms_spm.load_plans_from_cursor_cache     
  8    (sql_id => 'chhkmc32mdkak',     
  9     plan_hash_value => 2494645258,     
 10     sql_text => sqltext);     
 11  end;     
 12  /

PL/SQL procedure successfully completed.

SQL> 

What happens when we re-run the original SQL?

SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 1
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   208 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)


19 rows selected.

SQL> 

Nothing. No baseline used.

The reason for this is that parallel is not in the plan hints but is essential to force our parallel plan and get the appropriate plan hash value.

Remember that a baseline must reproduce the stored plan hash or it will be ignored and the stored plan hash is SQLOBJ$.PLAN_ID which maps to the PLAN_HASH_2 value found in V$SQL_PLAN.OTHER_XML.

Let’s try to get the hints in the baseline:

SQL> select to_char(exact_matching_signature) from v$sql where sql_id = '81qv4d7vkb571';

TO_CHAR(EXACT_MATCHING_SIGNATURE)
----------------------------------------
9005682359107037619

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints  
  2  from   xmltable('/outline_data/hint'  
  3         passing (select xmltype(comp_data) xml  
  4                  from   sys.sqlobj$data  
  5                  where  signature = 9005682359107037619)) x; 
       passing (select xmltype(comp_data) xml
                *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

SQL> 

Is that interesting? There’s now more than one baseline – should not be surprising if you are familiar with the baseline mechanism.

DBA_SQL_PLAN_BASELINES doesn’t give us everything we need to investigate so back under the covers:

SQL> SELECT TO_CHAR(so.signature) signature
  2  ,      so.plan_id
  3  ,      DECODE(ad.origin, 1, 'MANUAL-LOAD',
  4                        2, 'AUTO-CAPTURE',
  5                        3, 'MANUAL-SQLTUNE',
  6                        4, 'AUTO-SQLTUNE',
  7                        5, 'STORED-OUTLINE',
  8                           'UNKNOWN') origin
  9  ,      DECODE(BITAND(so.flags, 1), 1, 'YES', 'NO') enabled
 10  ,      DECODE(BITAND(so.flags, 2), 2, 'YES', 'NO') accepted
 11  ,      DECODE(BITAND(so.flags, 64), 64, 'NO', 'YES') reproduced
 12  FROM   sys.sqlobj$        so
 13  ,      sys.sqlobj$auxdata ad
 14  WHERE  ad.signature = so.signature 
 15  AND    ad.plan_id   = so.plan_id
 16  AND    so.signature = 9005682359107037619;

SIGNATURE                                   PLAN_ID ORIGIN         ENA ACC REP
---------------------------------------- ---------- -------------- --- --- ---
9005682359107037619                       263533726 MANUAL-LOAD    YES YES NO
9005682359107037619                      3860916006 AUTO-CAPTURE   YES NO  YES

SQL> 

The plan for our manually created baseline was not reproducible so the optimizer ignores it, generates its best plan and stores that as an AUTO_CAPTURE plan for future evolution and acceptance. The AUTO_CAPTURE plan gives the outline for the original index scan that makes the most sense but we’re not interested in that.

Here are the hints for our manually loaded baseline that failed to reproduce the plan:

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints  
  2  from   xmltable('/outline_data/hint'  
  3         passing (select xmltype(comp_data) xml  
  4                  from   sys.sqlobj$data  
  5                  where  signature = 9005682359107037619
  6                  and    plan_id   = 263533726)) x;  

OUTLINE_HINTS
---------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 4)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")

7 rows selected.

SQL> 

You can see the FULL hint but no PARALLEL.
With no PARALLEL hint, we can’t get the hash for the parallel plan that our baseline requires.

Shall we just check a SPM trace to verify what we see when a baseline cannot be reproduced?

SQL> alter session set tracefile_identifier = 'dom_spm';

Session altered.

SQL> alter session set events 'trace[RDBMS.SQL_Plan_Management.*]';

Session altered.

SQL> 
SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


SQL> 
SQL> alter session set events 'trace[RDBMS.SQL_Plan_Management.*] off';

Session altered.

SQL> 

And in the trace:

SPM: statement found in SMB
SPM: planId's of plan baseline are: 263533726
SPM: using qksan to reproduce, cost and select accepted plan, sig = 9005682359107037619
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 263533726
SPM: planId in plan baseline = 263533726, planId of reproduced plan = 3688435342
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : RIMS
  plan_baseline signature  : 9005682359107037619
  plan_baseline plan_id    : 263533726
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
    hint num  3 len 22 text: DB_VERSION('11.2.0.3')
    hint num  4 len 41 text: OPT_PARAM('optimizer_dynamic_sampling' 4)
    hint num  5 len  8 text: ALL_ROWS
    hint num  6 len 22 text: OUTLINE_LEAF(@"SEL$1")
    hint num  7 len 27 text: FULL(@"SEL$1" "T1"@"SEL$1")
SPM: generated non-matching plan:
...
SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, planId = 263533726
SPM: planId in plan baseline = 263533726, planId of reproduced plan = 3860916006
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : RIMS
  plan_baseline signature  : 9005682359107037619
  plan_baseline plan_id    : 263533726
  plan_baseline hintset    :
    hint num  1 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
SPM: generated non-matching plan:
...
------- END SPM Plan Dump -------
SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 3860916006

The Optimizer has two attempts are reproducing the required plan – the first one with the full set of hints, the second with just the OFE hint before reverting back to the best cost plan and storing that as mentioned as an AUTO-CAPTURE plan.

So, it seems as if using just a baseline to preserve parallelism does not work.

And perhaps that’s not surprising if you know what the PARALLEL hint does – it doesn’t force parallelism.

I understand what’s going here but but I’m not convinced by this baseline behaviour.

If a PARALLEL hint is required to get a specific plan, I’d expect it to be preserved in the baseline but the bottom line, I suppose, is that it’s not preserved in the hints in V$SQL_PLAN.OTHER_XML.

So, can SQL Patch help here?

Leaving our baseline in place and just adding a SQL Patch (i’ve said before that a SQL Patch can seem quite fussy
about using the proper hint syntax):

SQL> begin  
  2    sys.dbms_sqldiag_internal.i_create_patch  
  3    (sql_text  => 'select * from t1 where col1 = 1',  
  4     hint_text => 'PARALLEL(@"SEL$1" "T1"@"SEL$1" 4)',  
  5     name      => 'patch_test');   
  6  end;  
  7  /  

PL/SQL procedure successfully completed.

SQL> 

Shall we try the SQL again?

SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   208 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

Note
-----
   - SQL patch "patch_test" used for this statement


23 rows selected.

SQL> 

So SQL Patch + SQL Baseline does not work as desired – the Baseline by itself can’t reproduce the desired plan and the Patch hint is irrelevant once the best cost plan is picked.

So, how about just using a SQL Patch with multiple hints? Can that work?

SQL> declare
  2   l_op pls_integer;
  3  begin
  4   l_op :=
  5   dbms_spm.drop_sql_plan_baseline('SQL_7cfa9c643693a9b3');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin  
  2    sys.dbms_sqldiag.drop_sql_patch('patch_test');   
  3  end;  
  4  / 

PL/SQL procedure successfully completed.

SQL> begin  
  2    sys.dbms_sqldiag_internal.i_create_patch  
  3    (sql_text  => 'select * from t1 where col1 = 1',  
  4     hint_text => 'PARALLEL(@"SEL$1" "T1"@"SEL$1" 4) FULL(@"SEL$1" "T1"@"SEL$1")',  
  5     name      => 'patch_test');   
  6  end;  
  7  / 

PL/SQL procedure successfully completed.

SQL> select * from t1 where col1 = 1;  

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 2494645258

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   264 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | P->S | QC
|   3 |    PX BLOCK ITERATOR |          |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

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

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1"=1)

Note
-----
   - SQL patch "patch_test" used for this statement


26 rows selected.

SQL>

Success!

So, in summary, it looks like SQL Plan baselines cannot preserve PARALLEL but a SQL Patch can apply it.

And, at the same time, this shows that you can apply multiple hints in one SQL Patch.

By the way, above I made a throwaway comment above that the PARALLEL hint is not preserved in V$SQL_PLAN.OTHER_XML which is why it’s not preserved in a baseline. We can use the ‘+OUTLINE’ format to prove this from that last example above with the SQL Patch:

SQL> select * from table(dbms_xplan.display_cursor(format=>'+OUTLINE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 2494645258

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   264 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | P->S | QC
|   3 |    PX BLOCK ITERATOR |          |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 4)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1"=1)

Note
-----
   - SQL patch "patch_test" used for this statement


41 rows selected.

SQL> 

And finally…

If you’ve been looking at the undocumented DBMS_SQLDIAG_INTERNAL package that you need to create a SQL Patch, you might have noticed that alongside I_CREATE_PATCH there is an I_CREATE_HINTSET procedure.

This isn’t another new type of SQL Object but an internal api that probably all these SQL Objects use. So, if you try to create a hintset with I_CREATE_HINTSET then by default it will create a SQL Profile that can be dropped via DBMS_SQLTUNE.DROP_SQL_PROFILE.
Note that DBMS_SPM_INTERNAL has an interface to GET_SPM_HINTSET that amongst others takes an OBJ_TYPE parameter.

SQL Patch II

Following on from my previous post, let’s look at some of the workings of a SQL patch to try to get a bigger picture about this little-known feature.

Warning – this is quite a long post.

Let’s start with a similar example to that on the Oracle Optimizer blog article.

First, let’s setup a table that might work with the BIND_AWARE hint as per the Optimizer blog example.

SQL> create table t1
  2  (col1  number
  3  ,col2  varchar2(200)
  4  ,flag  varchar2(1));

Table created.

SQL> insert into t1
  2  select rownum
  3  ,      lpad('X',200,'X')
  4  ,      case when mod(rownum,10000) = 0
  5              then 'Y'
  6              else 'N'
  7         end
  8  from   dual
  9  connect by rownum <= 100000;

100000 rows created.

SQL> select flag, count(*) from t1 group by flag;  

F   COUNT(*)
- ----------
Y         10
N      99990

SQL> create index i1 on t1 (flag);

Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE 5');

PL/SQL procedure successfully completed.

And now a query with some bind variables.
Let’s execute this three or four times per bind variable:

SQL> var n varchar2(1)
SQL> exec :n := 'N'

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
     99990 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> /
.... etc...

SQL> exec :n := 'Y';

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> /
.... etc...

Let’s double check that we have a sql statement that the BIND_AWARE hint might be applicable to:

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)


20 rows selected.

SQL> select sql_id, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id = '731b98a8u0knf';

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf N 1292784087274697613                               6
731b98a8u0knf Y 1292784087274697613                               1

So far so good – we know we have a table and a statement that can be bind aware.

The next steps – as per the Optimizer blog – are to show how we can get the BIND_AWARE hint to apply from the first execution.

So, how about we flush this statement above and use the SQL Patch functionality to inject the BIND_AWARE hint.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id = '731b98a8u0knf';

no rows selected

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'BIND_AWARE',
  5    name      => 'patch_test'); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)

Note
-----
   - SQL patch "patch_test" used for this statement


24 rows selected.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id = '731b98a8u0knf';

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf Y 1292784087274697613                               1

SQL> 

Great – it all works as per the Optimizer team told us it would.

The execution plan has one of those invaluable Notes confirming that a SQL patch was used.

So, next up, why can’t we use a baseline to do this?

Well, as mentioned previously, it’s not because baselines can’t change execution plans in a similar way.

Au contraire, they can and, in most circumstances, they are probably the best placed feature to do this using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.

What they can’t do is inject the BIND_AWARE hint.

Allow me to demonstrate.

The approach with a baseline is slightly more complicated because we have to generate the required plan with one bit of sql and then, while it’s still in the cursor cache, transfer the whole plan into a baseline for the target sql statement.

First, let me delete that SQL Patch and verify that nothing is affecting our target statement.

SQL> exec dbms_sqldiag.drop_sql_patch('patch_test');

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  5ya8tff6fdrsa, child number 0
-------------------------------------
select count(*), max(col2) from t1 t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)


20 rows selected.

SQL> 

Then let’s get a plan for a statement with the BIND_AWARE hint.

SQL> select /*+ bind_aware */ count(*), max(col2) from t1 t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7qzu8gp22qr0v, child number 0
-------------------------------------
select /*+ bind_aware */ count(*), max(col2) from t1 t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)


20 rows selected.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id in ('731b98a8u0knf','7qzu8gp22qr0v');

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
7qzu8gp22qr0v Y 17219147247362199511                              1

SQL> 

Our new hinted statement is instantly bind aware when hinted as such.

Now, let’s load this plan from the cursor cache to the unhinted target statement using the sql_id and plan_hash_value from the DBMS_XPLAN output above.

SQL> declare   
  2    sqltext clob;   
  3    spm_op pls_integer;   
  4  begin   
  5    sqltext := 'select count(*), max(col2) from t1 where flag = :n';   
  6    spm_op  :=   
  7    dbms_spm.load_plans_from_cursor_cache   
  8    (sql_id => '7qzu8gp22qr0v',   
  9     plan_hash_value => 3625400295,   
 10     sql_text => sqltext);   
 11  end;   
 12  /   

PL/SQL procedure successfully completed.

And let’s see what difference the baseline makes:

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)

Note
-----
   - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement


24 rows selected.

Great! So our baseline is in full effect.

But, is the statement bind aware as it was the SQL Patch was applied?

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id in ('731b98a8u0knf');

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf N 1292784087274697613                               1

No, it’s not.

Now, why should this be?

Let’s recap the different intentions of SQL Baselines and SQL Patches

SQL Baselines exist to reproduce a specific plan. In fact, a plan hash exists as part of the baseline.
If, on application of the baseline, the Optimizer is unable to reproduce the desired plan, the baseline is rejected outright.

On the other hand, SQL Patches have been developed primarily to get the Optimizer to avoid a particular problem path in an execution plan, specifically to avoid failures due to certain access methods, join methods, etc.

Or to put it another way, the answer is in the internals.

Let me recreate the patch again:

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'BIND_AWARE',
  5    name      => 'patch_test'); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

You could look at DBA_SQL_PATCHES but it doesn’t tell you very much.

Or at least it does – for example the FORCE_MATCHING column indicates that SQL Patches have the same force matching ability as SQL Profiles and which Baselines don’t have – but it doesn’t show what I want you to see.

It can be a bit more revealing to look around under the hood at SYS.SQLOBJ$, SYS.SQLOBJ$DATA and SYS.SQLOBJ$AUXDATA.

Using the signature from V$SQL above:

SQL> select signature, obj_type, plan_id, name, flags, last_executed
  2  from sys.sqlobj$ 
  3  where signature = 1292784087274697613;

 SIGNATURE   OBJ_TYPE    PLAN_ID NAME                                FLAGS LAST_EXECUTED
---------- ---------- ---------- ------------------------------ ---------- -------------------------
1.2928E+18          2 2239163167 SQL_PLAN_13w748wknkcwd8576eb1f         11 06-MAR-12 03.01.19.000000 PM
1.2928E+18          3          0 patch_test                              1

Profiles have an OBJ_TYPE of 1.

Our Baseline has a type of 2.

The Patch has a type of 3.

The flags translate to ENABLED/DISABLED for SQL Profiles and SQL Patches and to values for ENABLED, ACCEPTED, FIXED, REPRODUCED and AUTOPURGE for SQL Plan Baselines.

Another important aspect is that PLAN_ID.
I mentioned that Baselines are designed to reproduce a specific plan and if the optimizer is unable to reproduce the specific plan when applying the baseline then it won’t use it. Plan_id is another hash, not plan_hash_value but plan_hash_2 from V$SQL.OTHER_XML.

There’s a contrast of information elsewhere.
For example, SQLOBJ$AUXDATA will highlight the performance metrics that are tracked for Baselines to aid evolution.

But it’s the hints that really should be interesting us, for that we need to look at SQLOBJ$DATA.

Here’s what our SQL Patch uses:

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
  2  from   xmltable('/outline_data/hint'
  3         passing (select xmltype(comp_data) xml
  4                  from   sys.sqlobj$data
  5                  where  signature = 1292784087274697613
  6                  and    obj_type  = 3)) x;

OUTLINE_HINTS
-------------------------------------------------------------------------------------------
BIND_AWARE

SQL> 

Just what we asked it to do – only inject BIND_AWARE

And our baseline contains all that is required to reproduce the exact plan it was asked to baseline.

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
  2  from   xmltable('/outline_data/hint'
  3         passing (select xmltype(comp_data) xml
  4                  from   sys.sqlobj$data
  5                  where  signature = 1292784087274697613
  6                  and    obj_type  = 2)) x;

OUTLINE_HINTS
---------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 4)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG"))

7 rows selected.

SQL> 

No sign of BIND_AWARE – only the hints that are required to reproduce the exact plan we asked for. And in fact BIND_AWARE might be a threat to that plan stability.

And so to the question of whether a SQL Patch and a Baseline can work together?

And the answer is yes PROVIDED that the hints are such that the pair can happily co-exist:

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)

Note
-----
   - SQL patch "patch_test" used for this statement
   - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement


25 rows selected.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id in ('731b98a8u0knf');

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf Y 1292784087274697613                               1

SQL> 

What happens if we try to create a SQL Patch that conflicts with the instructions of the Baseline?

Well, first note that only one SQL Patch can exist for statement at any one time (in any one category as per sql profile functionality):

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'FULL(t1)',
  5    name      => 'patch_test2'); 
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-13830: SQL profile with category DEFAULT already exists for this SQL statement
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16167
ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 204
ORA-06512: at line 2

So, let’s delete the existing SQL Patch and check that we’ve still got a baseline working (I’ve recreated the baseline since the demo started so it’s got a different name):

SQL> exec dbms_sqldiag.drop_sql_patch('patch_test');

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("FLAG"=:N)

Note
-----
   - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement


24 rows selected.

SQL> 

And let’s try to create one that will conflict with the index hints in the Baseline (note that you seem to have to be very particular about the full and proper hint syntax, i.e. just a FULL(t1) wasn’t cutting it):

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'FULL(@"SEL$1" "T1"@"SEL$1")',
  5    name      => 'patch_test2'); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 

And in this scenario, it seems that Patch trumps Baseline:

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   956 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    18 |  1872 |   956   (2)| 00:00:05 |
---------------------------------------------------------------------------

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

   2 - filter("FLAG"=:N)

Note
-----
   - SQL patch "patch_test2" used for this statement


23 rows selected.

which is probably what we’d expect.

That’ll do for now.

SQL Patch I

I know I wasn’t the only one to be intrigued by the recent blog article by the Oracle Optimizer team on injecting hints using a SQL Patch.

If you’ve read the article, you’ll know that creating a SQL Patch requires the use of the undocumented package DBMS_SQLDIAG_INTERNAL which is part of the SQL Repair Advisor.

Now, whilst creating a SQL Patch may be undocumented, altering and dropping a SQL Patch are documented in the DBMS_SQLDIAG package reference.

A follow-up post by the Oracle Optimizer team has since started to address some of the questions and promises to reveal more in the next few weeks.

Some of the questions that sprang to my mind on first reading the article were:
Q. Is this supported?
A. From answers so far, this is still unclear but without a doubt the feature is as good as undocumented.

Q. What is the license situation with a SQL Patch?
A. It’s a standard part of the SQL Repair Advisor which is part of 11g Enterprise Edition, no extra licensing like Diagnostic+Tuning pack required.

Q. Why/When would you use a SQL Patch rather than a SQL Plan Baseline?
A. Unanswered.

For me, the latter has been the big question mark.

Below is my opinion based on what I’ve read and played with so far.

I think part of the confusion is related to the title and premise of the original article – “Using SQL Patch to add hints to a packaged application”. The main illustration and link to previous post concerned the BIND_AWARE hint – that, I believe, was the primary use case – injecting a single hint – being illustrated by the article, not that a SQL Patch is necessarily the best mechanism for changing execution plans for packaged application code.

To my mind, in most circumstances SQL Plan Baselines are the prefered mechanism for changing the executions plans without touching the source code. The API DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE is ideal for this as shown in this article.

But… you can’t use a baseline to inject the BIND_AWARE hint (I’ll touch on that a bit in my next post).

It all comes down to slightly different intentions and use cases.

SQL Baselines exist to reproduce a specific plan. In fact, a plan hash exists as part of the baseline. If, on application of the baseline, the Optimizer is unable to reproduce the desired plan, the baseline is rejected outright.

On the other hand, SQL Patches have been developed primarily to get the Optimizer to avoid a particular problem path in an execution plan, specifically to avoid failures due to certain access methods, join methods, etc (and when you see the wealth of wrong results bugs, it’s not surprising that such a feature has been implemented).

If you were in such a crash or wrong results scenario, maybe you can start to see why you might want the lighter touch intervention of a SQL Patch over the more prescriptive SQL Plan Baseline.

In my next post, I hope to touch on some of these internal differences and show how in a very limited set of circumstances you could have a patch and a baseline applied to the same SQL.

For further information on SQL Patch functionality see my other posts:
SQL Patch II
SQL Patch III