Result cache was introduced in oracle 11g. Its job is to keep the result set of a query into a subset of the shared pool.It is usually helpful when we have a select query which is frequently used and data is being modified rarely.
So when we use result_cache for a query for the first time, it will take normal time. But subsequent executions will take very less time.

DEMO:

 

06:45:09 SQL > select count(*) from bigtab where weight in ( select distinct( weight)  from bigtab);

  COUNT(*)
----------
  13899072

Elapsed: 00:00:16.70



explain plan for
select count(*) from bigtab where weight in ( select distinct( weight)  from bigtab);

06:43:53 SQL > select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));06:44:06   2

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 1484132691

---------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Time     |
---------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 | 00:10:07 |
|   1 |  SORT AGGREGATE     |        |     1 |          |
|*  2 |   HASH JOIN SEMI    |        |    10M| 00:10:07 |
|   3 |    TABLE ACCESS FULL| BIGTAB |    10M| 00:02:35 |
|   4 |    TABLE ACCESS FULL| BIGTAB |    10M| 00:02:35 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("WEIGHT"="WEIGHT")

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

 

Now lets use the result_cache hint in the above query and check the performance/execution plan.

As we said before, query with result_cache for the first time, it will take normal time. Next execution will take very less time.

06:47:24 SQL >  select /*+ result_cache */ count(*) from bigtab where weight in ( select distinct( weight)  from bigtab);

  COUNT(*)
----------
  13899072

Elapsed: 00:00:16.47
06:48:16 SQL >  select /*+ result_cache */ count(*) from bigtab where weight in ( select distinct( weight)  from bigtab);

  COUNT(*)
----------
  13899072

Elapsed: 00:00:00.00


06:45:04 SQL > select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));06:45:08   2

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------

Plan hash value: 1484132691

------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |     1 | 00:10:07 |
|   1 |  RESULT CACHE        | 7db70qp96tas5fcuh60w94zdtd |       |          |
|   2 |   SORT AGGREGATE     |                            |     1 |          |
|*  3 |    HASH JOIN SEMI    |                            |    10M| 00:10:07 |
|   4 |     TABLE ACCESS FULL| BIGTAB                     |    10M| 00:02:35 |
|   5 |     TABLE ACCESS FULL| BIGTAB                     |    10M| 00:02:35 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("WEIGHT"="WEIGHT")

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; dependencies=(ORANET.BIGTAB); attributes=(single-row); name="select /*+ result_cache */ count(*) from bigtab where weight in ( select distinct( w
eight)  from bigtab)"

Note
-----
   - dynamic sampling used for this statement (level=2)

With result_cache the query is taking less 1 second.

 

Result_cache memory Report:

 

06:50:17 SQL > set serveroutput on
06:53:54 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  = 200M bytes (200K blocks)
Maximum Result Size = 10M bytes (10K blocks)
[Memory]
Total Memory = 181008 bytes [0.040% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 175656 bytes [0.039% of the Shared Pool]
....... Overhead = 142888 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.

Elapsed: 00:00:00.01

 

We can flush the result_cache as below also.

06:53:58 SQL> exec DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
06:54:33 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  = 200M bytes (200K blocks)
Maximum Result Size = 10M bytes (10K blocks)
[Memory]
Total Memory = 5352 bytes [0.001% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

 

Result_cache can be implement for specific tables also.

 

 

06:58:15 SQL > select table_name,result_cache from dba_tables where table_name='BIGTAB';

TABLE_NAME                     RESULT_
------------------------------ -------
BIGTAB                         DEFAULT

Elapsed: 00:00:00.01
06:59:15 SQL >  alter table bigtab result_cache ( mode force);

Table altered.

Elapsed: 00:00:00.05
06:59:28 SQL > select table_name,result_cache from dba_tables where table_name='BIGTAB';

TABLE_NAME                     RESULT_
------------------------------ -------
BIGTAB                         FORCE

 

NOTE:

1. We can’t use result_cache on tables owned by SYS user.