SQL Patch and RESULT_CACHE hint

Oracle provide us a lot of “cool” features to meet some more “hidden” needs, one example is SQL Patch. The problem with SQL Patch is that is not documented and it is basically an internal function from dbms_sqldiag_internal package. On the other hand you have the well know, well documented SQL Plan Baselines that also can fit your needs.

On this particular case, i’m only using SQL Patch in conjunction with RESULT CACHE. The ideia behind it, it to create an SQL Patch for a particular statement to use result_cache hint, without modifying any code at all.

First time is create a table and do some random query on it.

SQL> create table t1 as select dbms_random.value(0,10) N1, dbms_random.value(0,20) N2 from dual connect by level < 100000;
SQL> select count(*) from t1;

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   189   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| T1   | 88330 |   189   (0)| 00:00:03 |
-------------------------------------------------------------------

Now just run the same, but using the hint RESULT_CACHE and check if Oracle respect your will:


SQL> select /*+ RESULT_CACHE */ count(*) from t1;

------------------------------------------------------------------------------------------
| Id  | Operation	    | Name			 | Rows  | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |				 |     1 |   189   (0)| 00:00:03 |
|   1 |  RESULT CACHE	    | a10q47t7wrdhh6npx1qkqzugk0 |	 |	      | 	 |
|   2 |   SORT AGGREGATE    |				 |     1 |	      | 	 |
|   3 |    TABLE ACCESS FULL| T1			 | 88330 |   189   (0)| 00:00:03 |
------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=1; dependencies=(LCMARQUES.T1); attributes=(single-row); name="select /*+ result_cache*/ count(*) from t1"

As you can see, everything worked as expected. Let’s now add the SQL Patch instead of changing our SQL to include hint RESULT_CACHE. The ideia is to affect CBO decision before the execution:

SQL> begin
  2  SYS.dbms_sqldiag_internal.i_create_patch(sql_text => 'select count(*) from t1',
  3  hint_text => 'RESULT_CACHE',
  4  name => 'result_cache_patch');
  5  end;
  6  /
PL/SQL procedure successfully completed.

SQL Patch is now created, let’s see the query plan for the statement that we used:

SQL> select count(*) from t1;

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   189   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| T1   | 88330 |   189   (0)| 00:00:03 |
-------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement (level=2)
   - SQL patch "result_cache_patch" used for this statement

As you can see, the last line of the explain plan explicitly indicates that “result_cache_patch” will be used for this the statement. That is a little bit weird because the plan itself doesn’t contain any reference to RESULT_CACHE.
A 10046 trace file will show that even explain plan indicates that “result_cache_patch” will be used, it is ignored by the optimizer. The following traces shows a query with RESULT_CACHE hint and the other with the “result_cache_patch”

* Code changed to use RESULT_CACHE hint: select /*+ result_cache*/ count(*) from t1;

WAIT #139796640671976: nam='db file scattered read' ela= 817 file#=4 block#=1326 blocks=7 obj#=88605 tim=1399684414120606
FETCH #139796640671976:c=28996,e=221184,p=420,cr=678,cu=0,mis=0,r=1,dep=0,og=1,plh=3724264953,tim=1399684414121561
STAT #139796640671976 id=1 cnt=1 pid=0 pos=1 obj=0 op='RESULT CACHE  a10q47t7wrdhh6npx1qkqzugk0 (cr=678 pr=420 pw=0 time=221168 us)'
STAT #139796640671976 id=2 cnt=1 pid=1 pos=1 obj=0 op='SORT AGGREGATE (cr=678 pr=420 pw=0 time=221074 us)'

* Code without hint but with SQLPatch inplace:

WAIT #140032170080016: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=209 tim=1399684491153455
FETCH #140032170080016:c=13998,e=15248,p=0,cr=678,cu=0,mis=0,r=1,dep=0,og=1,plh=3724264953,tim=1399684491168743
STAT #140032170080016 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=678 pr=0 pw=0 time=15240 us)'
STAT #140032170080016 id=2 cnt=99999 pid=1 pos=1 obj=88605 op='TABLE ACCESS FULL T1 (cr=678 pr=0 pw=0 time=478162 us cost=189 size=0 card=88330)'

As seen,no RESULT CACHE was used (also easily seen by time taken to count the rows) even if SQLPatch inplace. This is actually result of a bug: Bug 16974854 : RESULT CACHE HINT DOES NOT WORK WITH SQL PATCH . Oracle also promised a patch soon (and included in some BP for 11.2.0.3/4). It will eventually be fixed also in Oracle 12.2.x according to bug description.

As side note, the same behavior applies using SQL Profiles (as well not good documented for this particular use case) and it doesn’t work at all.

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

PL/SQL procedure successfully completed.

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => 'select count(*) from t1',
PROFILE => SQLPROF_ATTR('RESULT_CACHE'),
NAME => 'PROFILE_RESULTC_T1',
REPLACE => TRUE,
FORCE_MATCH => TRUE);
END;

SQL> select count(*) from t1;
-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   189   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| T1   | 88330 |   189   (0)| 00:00:03 |
-------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement (level=2)
   - SQL profile "PROFILE_RESULTC_T1" used for this statement

Exact the same symptomes and exact the same behavior and outcome. Hope this can save you some time in the future.

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