Using Result Cache and SQL Patch together

Result Caching is a new feature in 11g that caches the complete result sets ( Not the database blocks).To use the result cache, you must set the initialization parameter RESULT_CACHE_MODE  to FORCE or use the hint /*+ RESULT_CACHE */ in the query. Wondering if you are using a packaged application and you can't really edit the query ? Here is when SQL Patch feature comes to the rescue. We can use both these features combined to use result cache without having to edit the query.

SQL> drop table test;
SQL> create table test (a number); 
SQL> insert into test values(1); 
SQL> commit; 
SQL> exec sys.dbms_sqldiag_internal.i_create_patch 
    (sql_text  => 'select count(*) from test', 
     hint_text=> 'OPT_PARAM(''result_cache_mode'' ''FORCE'')',  
     name => 'MYPATCH');
 
 SQL> select count(*) from test; 
 SQL> select * from table(dbms_xplan.display_cursor); 

Plan hash value: 1950795681
--------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)|
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                            |       |     3 (100)|
         
|   1 |  RESULT CACHE       | 6wgaf3vqwzvzx1sfmk7cw30v0v |       |            |
         
|   2 |   SORT AGGREGATE    |                            |     1 |            |
         
|   3 |    TABLE ACCESS FULL| TEST                       |     1 |     3   (0)| 
--------------------------------------------------------------------------------

Execution Plan confirms that a cache ID is generated and subsequent executions of this SQL would use the result cache.

NOTE: If your database version is lower than 12.1.0.2 you must install Patch 16974854 to use this feature. Without this fix, Optimizer ignores the result cache hint.