SQL Patch IV – Why is the SQL Patch applied but not effective?
March 29, 2012 9 Comments
(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.
OraStory 
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
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
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
Good points Mohamed, thanks for stepping up with those.
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.
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
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.
Pingback: Dynamic Sampling – 2 | Oracle Scratchpad
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.