A result cache + RESULT_CACHE_MAX_SIZE + ...

A result cache is an area of memory, either in the SGA or client application memory.
Server Result Cache
Initialization Parameters (we should know):
- RESULT_CACHE_MAX_SIZE
This parameter sets the memory allocated to the server result cache.
A result cache is in the shared pool. The RESULT_CACHE_MAX_SIZE initialization parameter is parameter for the maximum size of the result cache component of the SGA, if RESULT_CACHE_MAX_SIZE is 0 upon instance startup, the result cache is diabled.

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 3136 K

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> alter system set result_cache_max_size=0;

System altered.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS

A result cache status is "BYPASS".
If STOP/START instance. Then

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 0

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
DISABLED

A result cache status is "DISABLED".
If the result cache is disabled and 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.

SQL> alter system set result_cache_max_size=3M;

System altered.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
DISABLED

SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM hr.employees GROUP BY department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

still... "DISABLED", so STOP/START instance again.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM hr.employees GROUP BY department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 3w0d0687vphfr3gvhgbcpfg89s | | | | |
| 2 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

A result cache status is "ENABLED", then check:

SQL> SELECT ID, TYPE, CREATION_TIMESTAMP, BLOCK_COUNT, COLUMN_COUNT, PIN_COUNT, ROW_COUNT FROM V$RESULT_CACHE_OBJECTS WHERE CACHE_ID = '3w0d0687vphfr3gvhgbcpfg89s';

ID TYPE CREATION_ BLOCK_COUNT COLUMN_COUNT PIN_COUNT ROW_COUNT
---------- ---------- --------- ----------- ------------ ---------- ----------
1 Result 16-JUN-10 1 2 0 12

That showed some situation of the result cache with RESULT_CACHE_MAX_SIZE initialization parameter.

- RESULT_CACHE_MAX_RESULT
This parameter sets the maximum amount of server result cache memory that can be used for for a single result. The default is 5%, but you can specify any percentage value between 1 and 100. You can set this parameter at the system or session level.

SQL> show parameter RESULT_CACHE_MAX_RESULT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_result integer 5

- RESULT_CACHE_REMOTE_EXPIRATION
This parameter specifies the expiration time for a result in the server result cache that depends on remote database objects. The default value is 0 minutes, which implies that results using remote objects should not be cached.

We can manage memory for the server result cache by using DBMS_RESULT_CACHE package.
Example: Check result cache report and flush

SQL> set serverout on
SQL> exec dbms_result_cache.memory_report;
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 = 3M bytes (3K blocks)
Maximum Result Size = 153K bytes (153 blocks)
[Memory]
Total Memory = 158360 bytes [0.033% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 147664 bytes [0.030% of the Shared Pool]
....... Overhead = 114896 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)

PL/SQL procedure successfully completed.

SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL> exec dbms_result_cache.memory_report;
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 = 3M bytes (3K blocks)
Maximum Result Size = 153K bytes (153 blocks)
[Memory]
Total Memory = 10696 bytes [0.002% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.