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;

DBMS_RESULT_CACHE.STATUS()
ENABLED

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
    )
/

RESULT_ID	DEPEND_ID	OWNER	OBJECT_NAME	OBJECT_TYPE
1	0	SH	SALES	TABLE

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;

ID	CHUNK	OFFSET	FRE	OBJECT_ID	POSITION
0	0	0	NO	0	0
1	0	1	NO	1	0
2	0	2	NO	2	0
3	0	3	YES	0	0
4	0	4	YES	0	0
5	0	5	YES	0	0
6	0	6	YES	0	0
7	0	7	YES	0	0
8	0	8	YES	0	0
9	0	9	YES	0	0
10	0	10	YES	0	0
11	0	11	YES	0	0
12	0	12	YES	0	0
13	0	13	YES	0	0
14	0	14	YES	0	0
15	0	15	YES	0	0
16	0	16	YES	0	0
17	0	17	YES	0	0
18	0	18	YES	0	0
19	0	19	YES	0	0
20	0	20	YES	0	0
21	0	21	YES	0	0
22	0	22	YES	0	0
23	0	23	YES	0	0
24	0	24	YES	0	0
25	0	25	YES	0	0
26	0	26	YES	0	0
27	0	27	YES	0	0
28	0	28	YES	0	0
29	0	29	YES	0	0
30	0	30	YES	0	0
31	0	31	YES	0	0

V$RESULT_CACHE_OBJECTS

The sample contents of V$RESULT_CACHE_OBJECTS are:

select * from V$RESULT_CACHE_OBJECTS;

ID	TYPE	STATUS	BUCKET_NO	HASH	NAME	NAMES	CREATION_	CREATOR_UID	DEPEND_COUNT	BLOCK_COUNT	SCN	COLUMN_COUNT	PIN_COUNT	SCAN_COUNT	ROW_COUNT	ROW_SIZE_MAX	ROW_SIZE_MIN	ROW_SIZE_AVG	BUILD_TIME	LRU_NUMBER	OBJECT_NO	INVALIDATIONS	SPACE_OVERHEAD	SPACE_UNUSED	CACHE_ID	CACHE_KEY
2	Dependency	Published	1307	3132884251	OE.PRODUCT_DESCRIPTIONS		27/JAN/12	83	0	1	919989	0	0	0	0	0	0	0	0	0	70320	0	0	0	OE.PRODUCT_DESCRIPTIONS	OE.PRODUCT_DESCRIPTIONS
0	Dependency	Published	934	3160269734	SH.SALES		27/JAN/12	85	1	1	919298	0	0	0	0	0	0	0	0	0	70404	0	0	0	SH.SALES	SH.SALES
1	Result	Published	2330	2727995674	select /*+ result_cache */ count(*) from sh.sales	SQL	27/JAN/12	85	1	1	919298	1	0	0	1	7	7	7	19	0	0	0	229	788	1z5tms107m7ws9n3qwqjrbr4tj	db0mctd1n777yb7z80k4k5ms9c

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;

ID	NAME	VALUE
1	Block Size (Bytes)	1024
2	Block Count Maximum	1568
3	Block Count Current	32
4	Result Size Maximum (Blocks)	78
5	Create Count Success	1
6	Create Count Failure	0
7	Find Count	0
8	Invalidation Count	0
9	Delete Count Invalid	0
10	Delete Count Valid	0