In my previous posts we have seen fixing plans by applying baselines and profiles.
For profiles we saw in details how to generate the same using SQL hints.
This article is about another feature of Oracle 11.2, called SQL Patch.
I am not sure if this is supported by Oracle, but in days to come they will make this official.
This is a kind of silver bullet for doing minor changes in the plan which is difficult to get it done using baselines and profiles.
What is SQL Patch:
A SQL patch is a SQL manageability object that can be generated by the SQL Repair Advisor, in order to circumvent a plan which causes a failure. In essence, a SQL patch tells the optimizer to change the plan in some way or avoid an optimization so that the failure does not occur. For instance, if a failure happens during index access, then generating a plan without that index (or without any index) may work around the failure. You can read more about this in the documentation here
But we are going to use SQL Patch to fix a query plan.
Lets take an example:
SQL>create table T as select * from dba_objects;
Table created.
SQL>create index T_IDX on T(object_ID);
Index created.
SQL>exec dbms_stats.gather_table_stats(USER,'T',cascade=>true);
PL/SQL procedure successfully completed.
SQL>explain plan for
2 select * from T where object_id = 10;
Explained.
SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 89 | 62 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 89 | 62 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
13 rows selected.
SQL>
We see that its going for a FTS for accessing the table.
Now, lets try to use patch so that same query will start using index.
We can use sys.dbms_sqldiag_internal.i_create_patch procedure to create patch
This procedure needs
– sql text
– hints to be applied
– catagory in which to save the patch
– name of the SQL Patch
If SQL text is too big, I have given a procedure at the end of this artical which can be used. It ask for SQL ID, child number and hint to be applied. Its very easy to use.
So lets try to create a SQL patch using sys.dbms_sqldiag_internal.i_create_patch procedure
SQL>exec sys.dbms_sqldiag_internal.i_create_patch(sql_text => 'select * from T where object_id = 10',hint_text => 'INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))',category => 'DEFAULT',name => 'PATCH_gz85dtvwaj4fw');
PL/SQL procedure successfully completed.
SQL>select count(1) from dba_sql_patches where name = 'PATCH_gz85dtvwaj4fw';
COUNT(1)
----------
1
1 row selected.
SQL>
Here are the meanings of values I provided
sql_text – This is the text of the SQL. If text is too long, conside using PLSQL procedure provided at the end of the article.
hint_text – This is the hint we want to provide. Now this will seem different that what we usually provide in the SQL. If you are not sure of the exact hint, here is what you can do.
I used normal hint in my SQL to generate a plan
explain plan for
select /*+ index(T_IDX T) */ * from T where object_id = 10;
based on this you can parse other_xml column in plan_table using following SQL
SELECT regexp_replace(extractvalue(value(d), '/hint'),'''','''''') plan_hint
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from plan_table
where other_xml is not null
and plan_id = (select max(plan_id) from plan_table)
and rownum=1
)
) d;
PLAN_HINT
--------------------------------------------------------------------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
OPT_PARAM(''optimizer_index_caching'' 80)
OPT_PARAM(''optimizer_index_cost_adj'' 1)
OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')
OPT_PARAM(''_optim_peek_user_binds'' ''false'')
OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')
DB_VERSION(''11.2.0.2'')
OPTIMIZER_FEATURES_ENABLE(''11.2.0.2'')
IGNORE_OPTIM_EMBEDDED_HINTS
10 rows selected.
Once you get the output, you can pick the index hint from above – INDEX_RS_ASC(@”SEL$1″ “T”@”SEL$1” (“T”.”OBJECT_ID”))
Catagory can be any catagory you want to have. Usually everything should go to default if not specified.
Name of the patch can also be anything you want to give.
Now, if we check the plan for original query, it will start using index.
SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 89 | 200 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 89 | 200 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 100 (0)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
Note
-----
- SQL patch "PATCH_gz85dtvwaj4fw" used for this statement
- SQL plan baseline "SQL_PLAN_2anpx5hbuf3cbae82cf72" used for this statement
19 rows selected.
SQL>
Also, in the Note section you can see patch “PATCH_gz85dtvwaj4fw” is getting used for this statement.
Hope this helps !!
Persistent Storage Solutions 