11G OCM Use Result Cache

References

Oracle® Database Administrator's Guide 11g Release 1 (11.1)

• Configuring Memory Manually
  • Specifying the Result Cache Maximum Size

Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)

• DBMS_RESULT_CACHE
  • MEMORY_REPORT
  • STATUS

Oracle® Database Reference 11g Release 1 (11.1)

• Initialization Parameters
  • RESULT_CACHE_MAX_RESULT
  • RESULT_CACHE_MAX_SIZE
  • RESULT_CACHE_MODE
• Dynamic Performance Views
  • V$RESULT_CACHE_DEPENDENCY
  • V$RESULT_CACHE_MEMORY
  • V$RESULT_CACHE_OBJECTS
  • V$RESULT_CACHE_STATISTICS

Oracle® Database SQL Language Reference 11g Release 1 (11.1)

• RESULT_CACHE hint

Overview

On the server side, the use of the Result Cache seems fairly simple.

Checking the Status of Result Cache

The primary system parameter is RESULT_CACHE_MAX_SIZE:

SQL> show parameter RESULT_CACHE_MAX_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_max_size big integer 1568K

Specifying the Result Cache Maximum Size says that:

If RESULT_CACHE_MAX_SIZE is 0 upon instance startup, the result cache is disabled. To reenable it you must set RESULT_CACHE_MAX_SIZE to a nonzero value (or remove this parameter from the text initialization parameter file to get the default maximum size) and then restart the database.

Note that after starting the database with the result cache disabled, if you use an ALTER SYSTEM statement to set RESULT_CACHE_MAX_SIZE to a nonzero value but do not restart the database, querying the value of the RESULT_CACHE_MAX_SIZE parameter returns a nonzero value even though the result cache is still disabled. The value of RESULT_CACHE_MAX_SIZE is therefore not the most reliable way to determine if the result cache is enabled. You can use the following query instead:

Using the DBMS_RESULT_CACHE.STATUS function, the status of the Result Cache is confimed:

SQL> SELECT dbms_result_cache.status() FROM dual;

Manual Result Cache

The default setting of the RESULT_CACHE_MODE parameter is:

SQL> show parameter result_cache_mode NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_mode string MANUAL

According to RESULT_CACHE_MODE,

• MANUAL
  • The ResultCache operator is added only when the query is annotated (that is, hints).
• FORCE
  • The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).

Testing Result Cache

To test the result cache, the following SQL is used with the RESULT_CACHE hint:

select /*+ result_cache */ count(*) from PRODUCT_DESCRIPTIONS;

The Explain Plan is:

Plan hash value: 2366705070 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 (0)| 00:00:01 | | 1 | RESULT CACHE | 2q2ak1nj7cts87457jrf971uc9 | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | INDEX FAST FULL SCAN| PRD_DESC_PK | 8640 | 10 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(OE.PRODUCT_DESCRIPTIONS); attributes=(single-row); name="select /*+ result_cache */ count(*) from PRODUCT_DESCRIPTIONS"

The plan shows the result cache being used.

Memory Report

The memory usage report is obtained, via the MEMORY_REPORT procedure, as follows:

SQL> set serveroutput on size unlimited SQL> exec 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 = 1568K bytes (1568 blocks) Maximum Result Size = 78K bytes (78 blocks) [Memory] Total Memory = 103528 bytes [0.055% of the Shared Pool] ... Fixed Memory = 5132 bytes [0.003% of the Shared Pool] ....... Cache Mgr = 108 bytes ....... Memory Mgr = 124 bytes ....... Bloom Fltr = 2K bytes ....... State Objs = 2852 bytes ... Dynamic Memory = 98396 bytes [0.052% of the Shared Pool] ....... Overhead = 65628 bytes ........... Hash Table = 32K bytes (4K buckets) ........... Chunk Ptrs = 12K bytes (3K slots) ........... Chunk Maps = 12K bytes ........... Miscellaneous = 8284 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 29 blocks ........... Used Memory = 3 blocks ............... Dependencies = 2 blocks (2 count) ............... Results = 1 blocks ................... SQL = 1 blocks (1 count) PL/SQL procedure successfully completed.

Dynamic Performance View

There are four (4) associated dynamic performance views:

• V$RESULT_CACHE_DEPENDENCY
• V$RESULT_CACHE_MEMORY
• V$RESULT_CACHE_OBJECTS
• V$RESULT_CACHE_STATISTICS

V$RESULT_CACHE_DEPENDENCY

The sample contents of V$RESULT_CACHE_DEPENDENCY are:

SELECT d.result_id, d.depend_id, o.owner, o.object_name, o.object_type FROM v$result_cache_dependency d INNER JOIN dba_objects o ON ( d.object_no = o.object_id ) /

So where is OE.PRODUCT_DESCRIPTIONS?

V$RESULT_CACHE_MEMORY

The sample contents of V$RESULT_CACHE_MEMORY are:

SQL> select * from V$RESULT_CACHE_MEMORY;

V$RESULT_CACHE_OBJECTS

The sample contents of V$RESULT_CACHE_OBJECTS are:

select * from V$RESULT_CACHE_OBJECTS;

So where is the SQL using OE.PRODUCT_DESCRIPTIONS?

V$RESULT_CACHE_STATISTICS

The sample contents of V$RESULT_CACHE_STATISTICS are:

select * from v$result_cache_statistics;