SQL Patch – fast way to add a hint without changing the code

SQL Patches are internally used by SQL Recovery advisor to fix SQL statements that fail with critical errors at run time. SQL Recovery advisor does this job by patching / hints the SQL statement, basically telling the optimizer to avoid an access path for example. The same undocumented package can also be used to insert hints into a query that is having performance problems. Please see the following example

1. Lets create a test table

create table test_dba_tables as select * from dba_tables;

2. Execute a simple query and look at the plan

create index test_dba_name on test_dba_tables (table_name);

select owner,table_name,num_rows from test_dba_tables where table_name = 'JOB';

select * from table(dbms_xplan.display_cursor(format=>'ADVANCED'));

SQL_ID 740ka6qsms0q4, child number 0
-------------------------------------
select * from table(dbms_xplan.display_cursor(format=>'ADVANCED'))

Plan hash value: 3713220770

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 19 (100)| |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 8168 | 16336 | 19 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$F5BB74E1 / KOKBF$0@SEL$2

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
FORCE_XML_QUERY_REWRITE
XML_DML_RWT_STMT
XMLINDEX_REWRITE
XMLINDEX_REWRITE_IN_SELECT
NO_COST_XML_QUERY_REWRITE
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "KOKBF$0"@"SEL$2")
END_OUTLINE_DATA
*/

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - VALUE(A0)[300]

The execution plan is using index

3. Lets now create a SQL Patch to insert full table scan hint

declare
v_sql_text CLOB;
BEGIN
select sql_text into v_sql_text from v$sql where sql_id = 'bu18sp8k0wcvv';</blockquote>
sys.dbms_sqldiag_internal.i_create_patch(
sql_text=&gt;v_sql_text,
hint_text=&gt;'full(@SEL$1 test_dba_tables)',
name=&gt;'tst_patch');
END;
/

4. Execute the same query and check the execution plan

select owner,table_name,num_rows from test_dba_tables where table_name = 'JOB';

select * from table(dbms_xplan.display_cursor(format=&gt;'ADVANCED'));

SQL_ID bu18sp8k0wcvv, child number 0
-------------------------------------
select owner,table_name,num_rows from test_dba_tables where table_name
= 'JOB'

Plan hash value: 458784909

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 33 (100)| |
|* 1 | TABLE ACCESS FULL| TEST_DBA_TABLES | 3 | 141 | 33 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / TEST_DBA_TABLES@SEL$1

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST_DBA_TABLES"@"SEL$1")
END_OUTLINE_DATA
*/

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

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

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30], "NUM_ROWS"[NUMBER,22]

Note
-----
- dynamic sampling used for this statement (level=2)
- SQL patch "tst_patch" used for this statement

Viola! as you can see you are able to insert hints into query without changing the code. Please note that the hints required query block name which you can get it from display_cursor with format => ‘ADVANCED’

Advertisements

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