This feature was first introduced in Oracle 11g and was meant to increase performance of repetitive queries returning the same data.
The Result Cache feature is interesting if your application always look for static data, or data that is rarely updated, for these reasons, it is firstly destinated to Data Warehouses databases (OLAP) as many users will look for the same data via reporting tools.
This is particularly useful when you query a large amount of data to return only a few rows, for example, the last month sales aggregated by week.
Oracle will have to look for a large amount of data to retrieve all sales and make the aggregate, but the result will be only 4 rows.
For the first query execution, the runtime will be standard but for the subsequent queries, it will be very fast.
This cache is part of the shared pool.

Managing the Result Cache

The Result Cache is managed by 3 initialization parameters:

By default, the size of the result cache depends of you initialization parameters.
If MEMORY_TARGET parameter is set, then the result cache is 0.25% of this value.
If SGA_TARGET is set, then the result cache value is 0.5% of this value.
If you manage the shared pool manualy by setting the SHARED_POOL_SIZE parameter, then the result cache size is 1% of this value assigned to the shared pool.

How to use the result cache ?

The result cache can be used in two ways.

Using the result cache in queries

To use the result cache in queries, you need to add the RESULT_CACHE hint in the query.

SELECT /*+ RESULT_CACHE */ SUM(emp.emp_id), dep.dep_name
  FROM employees emp,
       departments dep
 where emp.dep_id = dep.dep_id
 GROUP BY dep.dep_name
 ORDER BY dep.dep_name;

Using the result cache in functions

To use it in functions, you must mention it in the create statement.

CREATE OR REPLACE FUNCTION format_customer_name (
                             p_cust_id IN customers.cust_id%TYPE
                             ) RETURN VARCHAR2
                               RESULT_CACHE
                               RELIES_ON (customers) IS
     v_name VARCHAR2(4000);

BEGIN

   counter.increment();

   SELECT cust_first_name || ' ' || cust_last_name
   INTO   v_name
   FROM   customers
   WHERE  cust_id = p_cust_id;

   RETURN v_name;

END format_customer_name;
/

Performance improvement

here is a small test to show the benefits of using the result cache.

SQL> create user demo identified by demo;

User created.

SQL> alter user demo quota unlimited on users;

User altered.

SQL> create table demo.test as select * from dba_objects union all select * from dba_objects union all select * from dba_objects;

Table created.

SQL> set lines 300
SQL> set autotrace traceonly exp stat
SQL> select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type;

44 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    44 |   396 |  1273   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | d45yr4zbr96uv494hpz99gkkn1 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    44 |   396 |  1273   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST                       |   272K|  2397K|  1268   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type"


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
       4587  consistent gets
       4582  physical reads
          0  redo size
       1756  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         44  rows processed

The first execution is a normal execution and Oracle caches the rows into the shared pool, as you can see just under the plan, oracle gets statistics about the data to cache; the number of columns and the dependent objects.
Remember that if you alter the dependent object, the data cached is invalidated.
To see what is actually cached into the result cache, we can query this view

SQL> SELECT id, type, creation_timestamp, block_count,
       column_count, pin_count, row_count
  FROM V$RESULT_CACHE_OBJECTS
 WHERE cache_id = 'd45yr4zbr96uv494hpz99gkkn1';

        ID TYPE       CREATION_ BLOCK_COUNT COLUMN_COUNT  PIN_COUNT  ROW_COUNT
---------- ---------- --------- ----------- ------------ ---------- ----------
         4 Result     22-APR-14           1            2          0         44

The cache_id used in the query is from the column name of the plan. As you can see the rows from my query are cached.
Now let’s see what happens if I execute the same query a second time.

SQL> select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type;

44 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    44 |   396 |  1273   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | d45yr4zbr96uv494hpz99gkkn1 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    44 |   396 |  1273   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST                       |   272K|  2397K|  1268   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1756  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         44  rows processed

SQL>

When we look at the plan statistics, there is a huge improvement, nothing has been done, the data is just retrieved from the cache without any overhead.
Now we will alter the dependant object to see how Oracle handles it.

SQL> delete from demo.test where object_type='TABLE';

7164 rows deleted.

SQL> set autotrace traceonly exp stat
SQL>  select /*+ RESULT_CACHE  */ object_type, count(*) from demo.test group by object_type;

43 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    44 |   396 |  1062   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | d45yr4zbr96uv494hpz99gkkn1 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    44 |   396 |  1062   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST                       |   272K|  2397K|  1056   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE  */ object_type, count(*) from demo.test group by object_type"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3825  consistent gets
          0  physical reads
          0  redo size
       1745  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         43  rows processed

I issued a delete statement on the table, immediately oracle invalidate the data in the result cache and will no longer use it until the transaction is committed or rolled back, even if I issue the same statement again.
Now I issue a commit and run the statement again.

SQL> commit;

Commit complete.

SQL> select /*+ RESULT_CACHE  */ object_type, count(*) from demo.test group by object_type;

43 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    44 |   396 |  1062   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | d45yr4zbr96uv494hpz99gkkn1 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    44 |   396 |  1062   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST                       |   272K|  2397K|  1056   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE  */ object_type, count(*) from demo.test group by object_type"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3825  consistent gets
          0  physical reads
          0  redo size
       1745  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         43  rows processed

For the first execution, oracle cache the altered data. Now we issue the statement again.

SQL> select /*+ RESULT_CACHE  */ object_type, count(*) from demo.test group by object_type;

43 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    44 |   396 |  1062   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | d45yr4zbr96uv494hpz99gkkn1 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    44 |   396 |  1062   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST                       |   272K|  2397K|  1056   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE  */ object_type, count(*) from demo.test group by object_type"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1745  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         43  rows processed

Oracle cached the new data in the result cache.

SQL> SELECT id, type, creation_timestamp, block_count,
       column_count, pin_count, row_count, invalidations
  FROM V$RESULT_CACHE_OBJECTS
 WHERE cache_id = 'd45yr4zbr96uv494hpz99gkkn1';  

        ID TYPE       CREATION_ BLOCK_COUNT COLUMN_COUNT  PIN_COUNT  ROW_COUNT INVALIDATIONS
---------- ---------- --------- ----------- ------------ ---------- ---------- -------------
         2 Result     22-APR-14           1            2          0         43             0
         1 Result     22-APR-14           1            2          0         44             0

I have now two sets in the result cache, one before and one after the delete.

As you can see, you can expect to huge performance benefits by using this feature, but to be efficient, you have to select the queries you choose to cache with caution because it can rapidly be memory consuming and can result in latch contention.