Result Cache & DBMS_RESULT_CACHE in ORACLE 11G

Server result cache is new feature in ORACLE 11G which enables database to cache the sql query and plsql function results in memory.

Result cache is a new component in Share pool which is part of SGA.

Result Cache section is marked in Yellow in above diagram.

The following are the result cache parameters

SQL> show parameter result_cache

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------------

client_result_cache_lag              big integer 3000

client_result_cache_size             big integer 0

result_cache_max_result              integer     5

result_cache_max_size                big integer 3168K

result_cache_mode                    string      MANUAL

result_cache_remote_expiration       integer     0

select name, type, value, isses_modifiable, issys_modifiable

 from v$parameter

where name like '%result%'

1.    Use result_cache_max_size parameter to set the size of result cache.

2.    Result_cache_mode parameter determines whether and under what circumstance query result caching will apply.

This parameter is having 3 possible values.

MANUAL -> the result cache operator will be added (results cached) only if you use the new  result_cache hint in the SQL query

AUTO -> the cost optimizer will determine when it should cache the query results, based on factors such as the frequency of execution, the cost of execution, and how frequently the database objects that are part of the query are changing

FORCE -> database caches the results of all SQL statements, as long as it’s valid to cache the result.

Use the ALTER SYSTEM command to enable result caching in a database

alter system set result_cache_mode = FORCE

alter system set result_cache_mode = manual

We can use result_cache, no_result_cache hints in SQL queries, subqueries, and inline views.

If we set the RESULT_CACHE_MODE parameter to FORCE, the result cache is used by default, but we can bypass it using the NO_RESULT_CACHE hint.

3.    Result_cache_max_result: By default, any single query result is limited to a maximum of 5 percent of the result cache (the size of which is set with the result_cache_max_size parameter). However, you can modify this default value by using the result_cache_max_result parameter, which specifies the maximum proportion of the result cache any single query result can take up, in percentage terms. You can set any percentage value between 0 and 100 for the result_cache_max_result parameter.

4.    result_cache_remote_expiration: use this dynamic initialization parameter to specify the time (in minutes) for which query results involving tables in remote databases stay valid. The default value of this parameter is 0, meaning the database won’t cache any results involving remote objects.

Result Cache with explain plan:

sql_11g> explain plan for select /*+ result_cache */ job, avg(sal)

  2   from emp

  3  group by job;

Explained.

sql_11g> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1697595674

--------------------------------------------------------------------------------

| Id  | Operation           | Name                       | Rows  | Bytes | Cost

(%CPU)| Time     |

--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                            |     5 |    60 |     3

  (34)| 00:00:01 |

|   1 |  RESULT CACHE       | bsj5p6dwcs3rh4nt989hcgzhy0 |       |       |

      |          |

|   2 |   HASH GROUP BY     |                            |     5 |    60 |     3

  (34)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| EMP                        |    14 |   168 |     2

   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Result Cache Information (identified by operation id):

------------------------------------------------------

   1 - column-count=2; dependencies=(XXPO.EMP); parameters=(nls); name="select /

*+ result_cache */ job, avg(sal)

 from emp

PLAN_TABLE_OUTPUT

------------------------

group by job"

15 rows selected.

Query the table v$result_cache_objects for the cache id above to view the details about cached SQL.

select * from

v$result_cache_objects

where cache_id ='bsj5p6dwcs3rh4nt989hcgzhy0'

DBMS_RESULT_CACHE package:

DBMS_RESULT_CACHE package provides various procedures to administer the result cache including managing and monitoring the cache.

SQL Query Result Cache: This cache helps to store the results of frequently executed SQL queries. Data warehouse applications are the most common beneficiaries of this cache.

To view the status of result cache

SELECT DBMS_RESULT_CACHE.status  FROM dual

SQL> SELECT DBMS_RESULT_CACHE.status  FROM dual;

STATUS

--------------------------------------------------

ENABLED

To View the Result Cache Memory Report

exec dbms_result_cache.memory_report;

SQL> execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);

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  = 3168K bytes (3168 blocks)

Maximum Result Size = 158K bytes (158 blocks)

[Memory]

Total Memory = 9460 bytes [0.002% of the Shared Pool]

... Fixed Memory = 9460 bytes [0.002% of the Shared Pool]

....... Memory Mgr = 128 bytes

....... Bloom Fltr = 2K bytes

....... Cache Mgr  = 4432 bytes

....... State Objs = 2852 bytes

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

PL/SQL procedure successfully completed.

Now, let’s run a SQL statement with result_cache hint

SQL> SELECT name, value

  2      FROM   v$parameter

  3      WHERE  name = 'result_cache_max_size';

NAME                    VALUE

-----------------------------------------

result_cache_max_size  2097152

SQL> SELECT value

  2      FROM   v$parameter

  3      WHERE  name = 'result_cache_mode';

VALUE

----------------------------------------------------

MANUAL

SQL>

SQL> select /*+ result_cache */ job, avg(sal)

  2       from emp

  3      group by job;

JOB         AVG(SAL)

--------- ----------

CLERK         1037.5

SALESMAN        1400

PRESIDENT       5000

MANAGER   2758.33333

ANALYST         3000

Now, if we query v$result_cache_objects table

select *

 from v$result_cache_objects

select * from

V$RESULT_CACHE_STATISTICS

Create Count Success shows value as 1 which means we have 1 cached sql.

The INVALIDATE procedure invalidates all result-set objects for a specific object, specified using an OWNER and NAME (OBJECT_NAME) or OBJECT_ID from the %_OBJECTS views

EXEC DBMS_RESULT_CACHE.invalidate('MAHENDRA','EMP')

The INVALIDATE_OBJECT procedure invalidates a specific result-set object in the result cache, specified using an ID or CACHE_ID from the V$RESULT_CACHE_OBJECTS view.

SQL> exec DBMS_RESULT_CACHE.invalidate_object(31);

The BYPASS procedure determines if the result cache is bypassed or not

SQL> exec DBMS_RESULT_CACHE.bypass(true);

SQL> exec DBMS_RESULT_CACHE.bypass(false);

The FLUSH procedure and function remove all objects from the result cache and optionally release all memory and clear existing cache statistics.

SQL> exec DBMS_RESULT_CACHE.flush;

It flushes the contents from v$result_cache_objects, V$result_cache_statistics etc tables.

Examples:

First flush the result cache and shared pool.

exec dbms_result_cache.flush;

alter system flush shared_pool;

SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.26

SQL> select * from

  2   (select * from (select mgr,deptno, max(e.sal) maxsalary

  3   from emp e

  4   group by e.mgr, deptno)

  5   order by maxsalary desc)

  6   where rownum <=3;

       MGR     DEPTNO  MAXSALARY

---------- ---------- ----------

                   10       5000

      7566         20       3000

      7839         20       2975

Elapsed: 00:00:00.02

Now issue following command from other session to force caching result sets at system level

alter system set result_cache_mode=force;

Come back to original session and re-execute the sql statement. First execution takes time and from second execution onwards it result returns in less time as the results are stored in result cache.

SQL> /

       MGR     DEPTNO  MAXSALARY

---------- ---------- ----------

                   10       5000

      7566         20       3000

      7839         20       2975

Elapsed: 00:00:00.05

SQL> /

       MGR     DEPTNO  MAXSALARY

---------- ---------- ----------

                   10       5000

      7566         20       3000

      7839         20       2975

Elapsed: 00:00:00.01

SQL>

Note: If the number of cached results keeps growing, the result cache could be eventually filled up. The result cache will grow, but only until it reaches the maximum size set by the result_cache_max_size parameter. You must manually purge results from the cache to prevent the cache from filling up. First disable the cache before you purge it, because you can’t purge while the cache is in active use.

• exec dbms_result_cache.flush: Removes all results and clears the cache memory

• exec dbms_result_cache.invalidate (ABC', 'TESTTAB'): Invalidates cache results for a

Specified object

• select dbms_result_cache.status from dual: Shows status of the result cache

• exec dbms_result_cache.memory_report: Shows result cache memory usage

• V$RESULT_CACHE_STATISTICS: Shows cache settings and memory usage statistics

• V$RESULT_CACHE_OBJECTS: Lists all cached objects and their attributes

• V$RESULT_CACHE_DEPENDENCY: Shows the dependency information between the

cached results and dependencies

• V$RESULT_CACHE_MEMORY: Shows all memory blocks and their statistics

The V$RESULT_CACHE_OBJECTS view shows both cached results and all dependencies.

The STATUS column can take the following values:

• new: The cached result is still being built.

• published: The cached result is available for use by other queries.

• bypass: Other queries will bypass the cached result.

• expired: The cached result has crossed the expiration time limit.

• invalid: The cached result is unavailable for use by other queries.

The following limitations apply with regard to the operation of the query result cache:

• When dealing with bind variables, a query can reuse a cached result only for identical variable values. Different values for the bind variables or different bind variable names lead to a cache miss.

• Oracle won’t cache results if the query uses a noncurrent version of the data because of the enforcement of the read consistency principle.

• Oracle won’t cache results if there are pending transactions on the tables that are part of the query.

• Oracle won’t cache flashback queries.

• All queries containing the following are ineligible for query result caching:

• Nondeterministic PL/SQL functions

• Currval and nextval

• SQL functions such as sysdata and current_date

• Temporary tables

• Dictionary tables

PL/SQL Function Result Cache -> Click Here