Result Cache相关的参数：
1. result_cache_max_size:
Maximum size of the result cache (5M for 5 MB, for example). If you set this to 0, result caching will be completely turned off.
2. result_cache_max_result:
Specifies the percentage of result_cache_max_size that any single result can use
3. result_cache_mode:
If set to FORCE, all the queries are cached if they fit in the cache. The default is MANUAL, which indicates that only queries with the hint will be cached.
4. result_cache_remote_expiration:
Specifies the number of minutes that a cached result that accesses a remote object will remain valid. The default is 0.

==准备测试环境
SQL> create table test1 as
2 select OWNER, OBJECT_NAME,SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE
3 from dba_objects;

表已创建。

SQL> insert into test1 select * from test1;

已创建68295行。

SQL> commit;

提交完成。
......
......
SQL> select count(*) from test1;

COUNT(*)
----------
4370880

==按顺序执行下面4句SQL，返回结果一样，但执行时间上，第3句不到0.1秒，因为数据是从内存里面读取；其余每句大概10秒，数据需要先从磁盘到内存，再返回给用户。
SQL> set timi on

SQL> select object_type,count(*) from test1
2 group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEXTYPE 704
WINDOW GROUP 256
......
EVALUATION CONTEXT 704

已选择41行。

已用时间: 00: 00: 09.97

SQL> select /*+ result_cache */ object_type,count(*) from test1
2 group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEXTYPE 704
WINDOW GROUP 256
......
EVALUATION CONTEXT 704

已选择41行。

已用时间: 00: 00: 10.83

SQL> select /*+ result_cache */ object_type,count(*) from test1
2 group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEXTYPE 704
WINDOW GROUP 256
......
EVALUATION CONTEXT 704

已选择41行。

已用时间: 00: 00: 00.07

SQL> select /*+ result_cache */ object_type,count(*) from test1
2 group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEXTYPE 704
WINDOW GROUP 256
......
EVALUATION CONTEXT 704

已选择41行。

已用时间: 00: 00: 10.39

==再看一下执行计划和统计信息的差别
SQL> set autotrace on explain stat
SQL> select object_type,count(*) from test1
2 group by object_type;

OBJECT_TYPE COUNT(*)
------------------- ----------
INDEXTYPE 704
WINDOW GROUP 256
......
EVALUATION CONTEXT 704

已选择41行。

已用时间: 00: 00: 09.89

执行计划
----------------------------------------------------------
Plan hash value: 1431305903

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5005K| 52M| 8767 (4)| 00:01:46 |
| 1 | HASH GROUP BY | | 5005K| 52M| 8767 (4)| 00:01:46 |
| 2 | TABLE ACCESS FULL| TEST1 | 5005K| 52M| 8540 (1)| 00:01:43 |
----------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
31039 consistent gets
22333 physical reads
0 redo size
1448 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
41 rows processed

SQL> select /*+ result_cache */ object_type,count(*) from test1
2 group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEXTYPE 704
WINDOW GROUP 256
......
EVALUATION CONTEXT 704

已选择41行。

已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 1431305903

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5005K| 52M| 8767 (4)| 00:01:46 |
| 1 | RESULT CACHE | 46kpzm1ppmb4taj8zg5uzj3xs0 | | | | |
| 2 | HASH GROUP BY | | 5005K| 52M| 8767 (4)| 00:01:46 |
| 3 | TABLE ACCESS FULL| TEST1 | 5005K| 52M| 8540 (1)| 00:01:43 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(SCOTT.TEST1); parameters=(nls); name="select /*+ result_cache */ object_type,count(*) from test1
group by object_type"

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1448 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
41 rows processed

==对比执行计划和统计信息，可以看出除了时间大幅缩短以外，用Result Cache以后，consistent gets和physical reads降为0，“RESULT CACHE”变成执行计划中的一个步骤。

==如果重新查询以前数据发生改变，则Result Cache不起作用，以保证数据正确性。

下面测试可以看出，增加一条记录后，重新查询的结果INDEXTYPE为705 （原先704），查询时间7.24秒，跟没有用Result Cache相当。
SQL> insert into test1 values('SCOTT','AAA','',123,123,'INDEXTYPE');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select /*+ result_cache */ object_type,count(*) from test1
2 group by object_type;

OBJECT_TYPE COUNT(*)
------------------- ----------
INDEXTYPE 705
WINDOW GROUP 256
......
EVALUATION CONTEXT 704

已选择41行。

已用时间: 00: 00: 07.24

==查看Result Cache使用情况。
SQL> set serveroutput on size 999999
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 = 1600K bytes (1600 blocks)
Maximum Result Size = 80K bytes (80 blocks)
[Memory]
Total Memory = 103536 bytes [0.047% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.045% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 2 blocks
................... SQL = 1 blocks (1 count)
................... Invalid = 1 blocks (1 count)

PL/SQL 过程已成功完成。

==查看Result Cache相关参数
SQL> show parameter result_cache

NAME TYPE VALUE
------------------------------------ ----------- -------
result_cache_max_result integer 5
result_cache_max_size big integer 1600K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0

==清除全部Result Cache
SQL> begin
2 dbms_result_cache.flush;
3 end;
4 /

PL/SQL 过程已成功完成。

==只清除某一张表的Result Cache
SQL> begin
2 dbms_result_cache.invalidate('SCOTT','TEST1');
3 END;
4 /

PL/SQL 过程已成功完成。

==Result Cache相关视图
V$RESULT_CACHE_STATISTICS:
Shows various settings, especially memory consumption
V$RESULT_CACHE_MEMORY:
Shows various pieces of the memory that make up the SQL Result Cache
V$RESULT_CACHE_OBJECTS:
Shows the objects that make up the SQL Result Cache
V$RESULT_CACHE_DEPENDENCY:
Shows the dependencies between the various objects that make up the SQL Result Cache