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.

About these ads

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

  1. Josh Collier says:

    have you been able to figure out a way to do this with inserts. such as getting an insert into table values (:bind) to pickup an append hint? i cannot

    • Dom Brooks says:

      Hi Josh,

      Thanks for the comment.
      I suppose it’s not completely surprising that append would not work.

      But there’s definitely more to find out in this area, for example can we use something like v$sql_hint to determine what can and can’t be injected via a patch? Not obviously so.

      I’ve another one for you courtesy of Jinwen Zou – result_cache.
      There’s a hint that you might think might be reasonable to inject into third party application code but you don’t seem to be able to do it with a sql patch.

      Cheers,
      Dominic

    • hourim says:

      First, remember that the append hint (direct path insert) works only with insert/select before 11gR2 and not with insert/values (: bind). In 11gR2, there is a new hint (append_values) that allows insert /values (:bind) to be done via a direct path

      Second, there are many situations where the append hint is silently ignored such as in the presence of trigger or foreign key constraint on the target table.

      Consequently, I can’t work out the utility of using SQL patch to force a direct path during insert. You have to hard code the append hint in the insert and Oracle will always obey it when there are no situations such those indicated above impeaching the direct path to happen

      • Dom Brooks says:

        Good points Mohamed, thanks for stepping up with those.

      • Dom Brooks says:

        There might not be much utility but according to my tests:
        1. Inserting APPEND into an appropriate INSERT (i.e. INSERT SELECT) – works.

        SQL> create table t1
          2  (col1 number);
        
        Table created.
        
        SQL> begin  
          2   sys.dbms_sqldiag_internal.i_create_patch  
          3   (sql_text  => 'insert into t1 select 1 from dual',  
          4    hint_text => 'append',  
          5    name      => 'patch_test');  
          6  end;  
          7  /  
        
        PL/SQL procedure successfully completed.
        
        SQL> insert into t1 select 1 from dual;
        
        1 row created.
        
        SQL> select * from table(dbms_xplan.display_cursor);
        
        PLAN_TABLE_OUTPUT
        ----------------------------------------------------------------------------
        SQL_ID  3cu19z56hudmf, child number 0
        -------------------------------------
        insert into t1 select 1 from dual
        
        Plan hash value: 2781518217
        
        -----------------------------------------------------------------
        | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
        -----------------------------------------------------------------
        |   0 | INSERT STATEMENT |      |       |     2 (100)|          |
        |   1 |  LOAD AS SELECT  |      |       |            |          |
        |   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
        -----------------------------------------------------------------
        
        Note
        -----
           - SQL patch "patch_test" used for this statement
        
        
        18 rows selected.
        
        SQL> insert into t1 select 1 from dual;
        insert into t1 select 1 from dual
                    *
        ERROR at line 1:
        ORA-12838: cannot read/modify an object after modifying it in parallel
        
        
        SQL> 
        

        2. Injecting APPEND_VALUES into an INSERT VALUES does not work.

        SQL> begin  
          2   sys.dbms_sqldiag.drop_sql_patch  
          3   (name      => 'patch_test');  
          4  end;  
          5  /  
        
        PL/SQL procedure successfully completed.
        
        SQL> var n number
        SQL> exec :n := 1
        
        PL/SQL procedure successfully completed.
        
        SQL> begin  
          2   sys.dbms_sqldiag_internal.i_create_patch  
          3   (sql_text  => 'insert into t1 values (:n)',  
          4    hint_text => 'append_values',  
          5    name      => 'patch_test');  
          6  end;  
          7  /  
        
        PL/SQL procedure successfully completed.
        
        SQL> insert into t1 values (:n);
        
        1 row created.
        
        SQL> insert into t1 values (:n);
        
        1 row created.
        
        SQL> select * from table(dbms_xplan.display_cursor);
        
        PLAN_TABLE_OUTPUT
        ------------------------------------------------------------
        SQL_ID  45t5dfh0pcp3g, child number 0
        -------------------------------------
        insert into t1 values (:n)
        
        
        -------------------------------------------------
        | Id  | Operation                | Name | Cost  |
        -------------------------------------------------
        |   0 | INSERT STATEMENT         |      |     1 |
        |   1 |  LOAD TABLE CONVENTIONAL |      |       |
        -------------------------------------------------
        
        SQL> insert /*+append_values */ into t1 values (:n);
        
        1 row created.
        
        SQL> select * from table(dbms_xplan.display_cursor);
        
        PLAN_TABLE_OUTPUT
        -----------------------------------------------------------------
        SQL_ID  f70cy8t9r7b1s, child number 0
        -------------------------------------
        insert /*+append_values */ into t1 values (:n)
        
        Plan hash value: 3581094869
        
        -----------------------------------------
        | Id  | Operation        | Name | Cost  |
        -----------------------------------------
        |   0 | INSERT STATEMENT |      |     1 |
        |   1 |  LOAD AS SELECT  |      |       |
        |   2 |   BULK BINDS GET |      |       |
        -----------------------------------------
        
        SQL> 
        
  2. hourim says:

    Dom,

    I don’t have at hand a 11gR2 right now. If so, I would have tested

    insert /*+ append_values */ into t1 values (:n);

    to see if, in this, case it will work. It should thought. Just to confirm that with sql patch (when considered) append_values is ignored.

    http://antognini.ch/2009/10/hints-for-direct-path-insert-statements/

    Cheers

    • Dom Brooks says:

      I think you’ll find that’s what I’ve shown above.

      1. You could (doesn’t mean you should) use a sql_patch to inject append into a valid append scenario.

      2. You can’t use a sql_patch to inject append_values into a valid append_values scenario.

      I did edit my comments and add extra. Maybe your comment and my edits overlapped.

  3. Pingback: Dynamic Sampling – 2 | Oracle Scratchpad

  4. Michael says:

    You do not really need complete hint specification, you just need to specify the query block for the hint, which is understandable, b/c oracle has no idea where in the query this hint suppose to appear – you can have multiple selects , views , etc…

    So if you’d use “HINT(…)” directly in the statement you’ll need to use “HINT(@ …)” in sql patch or profile definition.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

Join 75 other followers

%d bloggers like this: