Before reading this article I suggest to read article about deterministic functions

Deterministic functions

SQL Result cache is nice addition to deterministic functions.

It enables to:

• cache the result of a query or query block for future reuse

• cached results are available for all sessions unless they are staled

SQL Result cache are especially usefull for warhouses were users read many rows and return few rows. Returned rows are saved in SGA and can be reused if the same query is executed once again in the same or other session. Cached SQL results become stale when data in the objects being accessed by the query is modified.

NOTE: for RAC configuration each instance has got own private result cache and can’t be used by other instances. However invalidations work accross instances.

Parameter RESULT_CACHE_MODE

Database parameter RESULT_CACHE_MODE can be set on instance or session level with following values:

• MANUAL – sql results are not cached for sql statements

• FORCE   – all results are cached for sql statments

alter session set result_cache_mode=force;

Hints

Special hints are available to enable/disable caching for results on SQL level regardless of RESULT_CACHE_MODE parameter:

• RESULT_CACHE – it will cache result for executed query in memory

• NO_RESULT_CACHE – it will not cache result for executed query

--it's usefull when RESULT_CACHE_MODE is set to MANUAL
select /*+ RESULT_CACHE */ count(*) from big_table;

--it's usefull when RESULT_CACHE_MODE is set to FORCE
select /*+ NO_RESULT_CACHE */ count(*) from big_table;

 Managing SQL query result cache

Database parameters:

RESULT_CACHE_MAX_SIZE

• specifies memory allocated for result cache
• disables result cache if set to 0
• default size depends on other memory settings

0,25% of MEMORY_TARGET or
0,5% of SGA_TARGET or
1% of SHARED_POOL_SIZE

• can’t be grater than 75% of shared pool

RESULT_CACHE_MAX_RESULT

• specifies maximum cache memory for a single result

• defaults to 5%

RESULT_CACHE_REMOTE_EXPIRATION

• sets expiry time for cached results depending on remote database objects

• defaults to 0

In-Line View and RESULT_CACHE hint

RESULT_CACHE hint can have impact on explain plan. In case you use  RESULT_CACHE hint in subquery it will block: view merging, predicate push-down, and column projection.

Following explain plan is showing that predicate department_id=10 is pushed into subquery

explain plan for
select * from (
select department_id, sum(salary) from employees
group by department_id
) where department_id=10;

select * from table(dbms_xplan.display);

Plan hash value: 2036705853

----------------------------------------------------------
| Id  | Operation                    | Name              |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |
|   1 |  SORT GROUP BY NOSORT        |                   |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |
----------------------------------------------------------

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

   3 - access("DEPARTMENT_ID"=10)

it looks different for RESULT_CACHE hint in subquery. First is executed subquery and result from this subquery is cached in memory. In next step the cached result is filtered to select data for predicate department_id=10

explain plan for
select * from (
select /*+ RESULT_CACHE */ department_id, sum(salary) from employees
group by department_id
) where department_id=10;

select * from table(dbms_xplan.display);

Plan hash value: 2700420355

-----------------------------------------------------------
| Id  | Operation            | Name                       |
-----------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |
|*  1 |  VIEW                |                            |
|   2 |   RESULT CACHE       | 3q3armf1zrhd49k2jnb3461mz5 |
|   3 |    HASH GROUP BY     |                            |
|   4 |     TABLE ACCESS FULL| EMPLOYEES                  |
-----------------------------------------------------------

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

   1 - filter("DEPARTMENT_ID"=10)

Result Cache Information (identified by operation id):
------------------------------------------------------

"   2 - column-count=2; dependencies=(HR.EMPLOYEES); 
name="select /*+ RESULT_CACHE */ department_id, sum(salary) 
from employees
group by department_id
""

Table annotation to control result caching

 You can specify RESULT_CACHE on table level as well:

• MODE DEFAULT

• MODE FORCE

CREATE TABLE test_tbl (...) RESULT_CACHE (MODE DEFAULT);

ALTER TABLE test_tbl RESULT_CACHE(MODE FORCE);

Table annotations are in effect only for whole query and not for subqueries.

If one of table in query has got MODE DEFAULT result caching is not enabled unless RESULT_CACHE_MODE initialization parameter is set to FORCE or you are using RESULT_CACHE hint.

If all tables in query are marked as FORCE, the query result is considered for caching. The FORCE annotation takes precedence over initialization parameter RESULT_CACHE_MODE=MANUAL set on the session level.

You can override table and session settings by using hints at the query level.

New column RESULT_CACHE is added to DBA_,ALL_,USER_TABLES

DBMS_RESULT_CACHE package

Package DBMS_RESULT_CACHE delivers procedure and functions to manage result cache in database.

STATUS – check status of result cache

sql> SELECT DBMS_RESULT_CACHE.status FROM dual;
STATUS
------------------
ENABLED

MEMORY_REPORT – retrives statistics about result cache usage

sql>set serveroutput on
sql>exec DBMS_RESULT_CACHE.memory_report
anonymous block completed
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 162496 bytes [0.046% of the Shared Pool]

... Fixed Memory = 5352 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 157144 bytes [0.045% of the Shared Pool]
....... Overhead = 124376 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)

FLUSH – removes all existing results from cache memory

sql>exec DBMS_RESULT_CACHE.flush;

sql>set serveroutput on
sql>exec DBMS_RESULT_CACHE.memory_report;

anonymous block completed
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 5352 bytes [0.002% of the Shared Pool]

... Fixed Memory = 5352 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

INVALIDATE – invalidates cached results for specified object

sql>exec DBMS_RESULT_CACHE.invalidate('USER_NAME', 'OBJECT_NAME');

or use ID or CACHE_ID from V$RESULT_CACHE_OBJECTS view

sql>exec DBMS_RESULT_CACHE.invalidate_object(31);

BYPASS – turns on/off bypass mode for result cache. When turned on cached results are no longer used and no new results are saved in the cache. When turned off cache works normaly.

This operation can be used when there is a need to hot patch PL/SQL code in a running system. If a code-patch is applied to a PL/SQL module on which a result cached function directly or transitively depends, then the cached results associated with the result cache function are not automatically flushed (if the instance is not restarted/bounced). This must be manually achieved.

To ensure correctness during the patching process follow these steps:

1. Turn on bypass and flush cache

   BEGIN
      DBMS_RESULT_CACHE.BYPASS(TRUE);
      DBMS_RESULT_CACHE.FLUSH;
   END;
   /

   This must be done on each instance in RAC(sql result cache is not shared between instances)

2. Apply the PL/SQL code patches.
3. Turn off bypass

   BEGIN
      DBMS_RESULT_CACHE.BYPASS(FALSE);
   END;
   /

   This must be done on each instance in RAC(sql result cache is not shared between instances)

Dictionary views

(G)V$RESULT_CACHE_STATISTICS – displays various Result Cache settings and usage statistics

(G)V$RESULT_CACHE_MEMORY – displays all the memory blocks and their status

(G)V$RESULT_CACHE_OBJECTS – displays all the objects (both cached results and dependencies) and their attributes

(G)V$RESULT_CACHE_DEPENDENCY – displays the depends-on relationship between cached results and dependencies

Important facts

• Result cache is disabled for:

– temporary or dictionary tables

– nondeterministic PL/SQL functions

– sequence CURRVAL, NEXTVAL

– CURRNT_DATE, SYSDATE,SYS_GUID etc

• DML/DDL operations on remote databases don’t expire cached results
• Flashback queries can be cached
• SQL with bind variables can be cached and bind variable is saved with bind value

Examples

Let’s prepare data

drop table test_tbl;

create table test_tbl(id number);

begin
  for i in 1..100
  loop
    insert into test_tbl values(i);
  end loop;
  commit;
end;
/

select count(*) from test_tbl;
  COUNT(*)
----------
       100

exec dbms_result_cache.flush
anonymous block completed

set timing on

Lets’ check RESULT_CACHE hint

select /*+ RESULT_CACHE */ count(*) 
from 
  test_tbl, test_tbl, test_tbl, test_tbl;
  COUNT(*)
----------
 100000000 

Elapsed: 00:00:04.540

For next call data is from sql result cache

select id, type, status, name from v$result_cache_objects;
no rows selected

select /*+ RESULT_CACHE */ count(*) 
from 
  test_tbl, test_tbl, test_tbl, test_tbl;
  COUNT(*)
----------
 100000000 

Elapsed: 00:00:00.003

select id, type, status, name from v$result_cache_objects;
 ID TYPE       STATUS    NAME
--- ---------- --------- -------------------------------------
  0 Dependency Published TOMASZ.TEST_TBL                      
  1 Result     Published select /*+ RESULT_CACHE */ count(*)  
                         from  test_tbl, test_tbl, test_tbl, test_tbl

Following example shows how works result_cache(mode force) and hint NO_RESULT_CACHE

alter table test_tbl result_cache (mode force); 
table TEST_TBL altered.
Elapsed: 00:00:00.006

select count(*) 
from 
  test_tbl, test_tbl, test_tbl, test_tbl;
  COUNT(*)
----------
 100000000 

Elapsed: 00:00:04.558

select id, type, status, name from v$result_cache_objects;  
select count(*) 
from 
  test_tbl, test_tbl, test_tbl, test_tbl;
  COUNT(*)
----------
 100000000 

Elapsed: 00:00:00.002

select id, type, status, name from v$result_cache_objects;
ID TYPE       STATUS    NAME             
-- ---------- --------- -----------------
 0 Dependency Published TOMASZ.TEST_TBL  
 2 Result     Published select count(*)  
                        from             
                        test_tbl, test_tbl, test_tbl, test_tbl

 1 Result     Invalid   select /*+ RESULT_CACHE */ count(*)
                        from                       
                        test_tbl, test_tbl, test_tbl, test_tbl

Elapsed: 00:00:00.010

Following statement will not create data in sql result cache

select /*+ NO_RESULT_CACHE */ count(*) 
from 
  test_tbl, test_tbl, test_tbl, test_tbl;
  COUNT(*)
----------
 100000000 

Elapsed: 00:00:04.61

select id, type, status, name from v$result_cache_objects;
ID TYPE       STATUS    NAME             
-- ---------- --------- -----------------
 0 Dependency Published TOMASZ.TEST_TBL  
 2 Result     Published select count(*)  
                        from             
                        test_tbl, test_tbl, test_tbl, test_tbl

 1 Result     Invalid   select /*+ RESULT_CACHE */ count(*)
                        from                       
                        test_tbl, test_tbl, test_tbl, test_tbl

Elapsed: 00:00:00.010

Have a fun 🙂

Tomasz