Result Cache is a feature introduced in Oracle 11g which caches the results of a query in the shared pool. It is ideal for a query that is used frequently and rarely changes. Once the initial query has been cached, Oracle retrieves the result of the query from the cache, improving its performance. When a query executes, the database looks in the cache memory to determine whether the result exists in the cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.

Benefits of Using the Server Result Cache:

1. "The benefits of using the server result cache depend on the application. OLAP applications can benefit significantly from its use. Good candidates for caching are queries that access a high number of rows but return a small number, such as those in a data warehouse. For example, you can use advanced query rewrite with equivalences to create materialized views that materialize queries in the result cache instead of using tables."

2. As queries using result cache bypass buffer cache and the result is in the cache, there is less CPU consumption.

3. There is no physical I/O, as the result is available again in the cache.

Other SQL Query Result Cache Considerations

Result cache is disabled for queries containing:

1 Temporary or Dictionary tables
2 Non-deterministic PL/SQL functions
3 Sequence CURRVAL and NEXTVAL
4 SQL functions CURRENT_DATE,SYSDATE,SYS_GUID, and so on

DDL/DML on remote database does not expire cached results
Flashback queries can be cached
Result Cache does not automatically release memory

1 It grows until maximum size is reached
2 DBMS_RESULT_CACHE.FLUSH purges memory

Bind variables

1 Cached result is parameterized with variable values
2 Cached results can only be found for the same variable values

Cached result will not be build if:

1 Query is build on a non-current version of data(read consistency enforcement)
2 Current session has outstanding transaction on tables in query

Requirements for the Result Cache

Also enabling result cache does not guarantee that result of the query will be in the cache. There are certain requirements before result cache can be used:

Read Consistency Requirement
Query Parameter Requirements
Restrictions for Result Cache
Results cannot be cached when the following objects or functions are in a query:
- 1 Temporary tables
- 2 External tables
- 3 Tables in the SYS or SYSTEM schemas
- 4 Sequence CURRVAL and NEXTVAL pseudo columns
- 5 SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, and SYS_TIMESTAMP
  
  A if Result Cache Can Not Be Enabled,first use index hints:
  
  [oracle@ORACLERAC2 ~]$ cd $ORACLE_HOME/dbs
  [oracle@ORACLERAC2 dbs]$ strings spfilePROD1.ora | grep result_cache
  *.result_cache_mode='MANUAL'
  [oracle@ORACLERAC2 dbs]$

SQL> show parameter result_cache_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 4608K
SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
-----------------------------------------------
ENABLED
SQL> alter system set result_cache_max_size=15m;

System altered.
SQL> sho parameter result_cache_mode

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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 15M

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE 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 = 15M bytes (15K blocks)
Maximum Result Size = 768K bytes (768 blocks)
[Memory]
Total Memory = 340280 bytes [0.159% of the Shared Pool]
... Fixed Memory = 12144 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 328136 bytes [0.153% of the Shared Pool]
....... Overhead = 131528 bytes
....... Cache Memory = 192K bytes (192 blocks)
........... Unused Memory = 27 blocks
........... Used Memory = 165 blocks
............... Dependencies = 29 blocks (29 count)
............... Results = 136 blocks
................... SQL     = 117 blocks (117 count)
................... Invalid = 19 blocks (19 count)

PL/SQL procedure successfully completed.

No hints,No result cache:
SQL> SELECT department_id, AVG(salary)
2 FROM     hr.employees
3 GROUP BY department_id;
12 rows selected.
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 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          6 consistent gets
          0 physical reads
          0 redo size
        853 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed

hints and result cache:
SQL> SELECT   /*+ result_cache*/ department_id, AVG(salary)
2 FROM     hr.employees
3 GROUP BY department_id;
12 rows selected.
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       | 7uc8hravqsvzc3u63m12dkt8pf |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4 (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT   /*+ result_cache*/ department_id, AVG(salary)
FROM     hr.employees
GROUP BY department_id"
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          0 consistent gets
          0 physical reads
          0 redo size
        853 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed

SQL> select name,status from v$result_cache_objects;
……
SELECT   /*+ RESULT_CACHE */ department_id, AVG(salary)                                                                          Published
FROM     hr.employees
GROUP BY department_id
……

B    All results are stored in the result cache. If a query result is not in the cache, then the database executes the query and stores the result in the cache. Subsequent executions of the same statement, including the result cache hint, retrieve data from the cache.Sessions uses these results if possible. To exclude query results from the cache, you must use the /*+ NO_RESULT_CACHE */ query hint.
SQL>
SQL> alter system set result_cache_mode= FORCE;
System altered.
SQL> sho parameter result_cache_mode
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      FORCE
SQL>
SQL> SELECT    department_id, AVG(salary)
2 FROM     hr.employees
3 GROUP BY department_id;
12 rows selected.
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       | 7uc8hravqsvzc3u63m12dkt8pf |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4 (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT    department_id, AVG(salary)
FROM     hr.employees
GROUP BY department_id"
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          0 consistent gets
          0 physical reads
          0 redo size
        853 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed

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

12 rows selected.
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 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          6 consistent gets
          0 physical reads
          0 redo size
        853 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed

C You can use table annotations to control result caching. Table annotations are in effect only for the whole query, not for query segments. The primary benefit of these annotations is avoiding the necessity of adding result cache hints to queries at the application level.

A table annotation has a lower precedence than a SQL hint. Thus, you can override table and session settings by using hints at the query level. Permitted values for the RESULT_CACHE table annotation are as follows:

DEFAULT

If at least one table in a query is set to DEFAULT, then result caching is not enabled at the table level for this query, unless the RESULT_CACHE_MODE initialization parameter is set to FORCE or the RESULT_CACHE hint is specified. This is the default value.
FORCE

If all the tables of a query are marked as FORCE, then the query result is considered for caching. The table annotation FORCE takes precedence over the RESULT_CACHE_MODE parameter value of MANUAL set at the session level.

SQL> alter table hr.employees RESULT_CACHE (MODE DEFAULT);
Table altered.
SQL> SELECT   department_id, AVG(salary)
2 FROM     hr.employees
3 GROUP BY department_id;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 119216990
--------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          6 consistent gets
          0 physical reads
          0 redo size
        853 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed
SQL> alter table hr.employees RESULT_CACHE (MODE FORCE);
Table altered.
SQL> SELECT   department_id, AVG(salary)
2 FROM     hr.employees
3 GROUP BY department_id;
12 rows selected.
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       | 7uc8hravqsvzc3u63m12dkt8pf |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4 (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT   department_id, AVG(salary)
FROM     hr.employees
GROUP BY department_id"
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          0 consistent gets
          0 physical reads
          0 redo size
        853 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed
SQL> SELECT /*+ NO_RESULT_CACHE */ department_id, AVG(salary)
2 FROM     hr.employees
3 GROUP BY department_id;
12 rows selected.
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 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          6 consistent gets
          0 physical reads
          0 redo size
        853 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed

Result Cache Can Not Be Enabled

Benefits of Using the Server Result Cache:

Other SQL Query Result Cache Considerations

Requirements for the Result Cache