环境介绍：

ORACLE11GR2 64BIT

SOLARIS 10 64BIT

SUBJECT:RESULT_CACHE (SQL RESULT CACHE)

在oracle11g中，为了极大的缓解在select count(*) XXXX时能够小的消耗系统资源以及更快的返回统计记录于是增加了RESULT_CACHE的功能，使用方法就是通过hints固定：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter result_cache;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 1984K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0

以上的parameter分别指定了分配给result cache的内存大小、RC的控制方式以及远程RC的过期设置，还可以通过执行DBMS_RESULT_CACHE来获得更详细的信息：

SQL> set serveroutput 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 = 1984K bytes (1984 blocks)
Maximum Result Size = 99K bytes (99 blocks)
[Memory]
Total Memory = 153264 bytes [0.094% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.007% of the Shared Pool]
... Dynamic Memory = 142568 bytes [0.087% of the Shared Pool]
....... Overhead = 109800 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 26 blocks
........... Used Memory = 6 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 5 blocks
................... SQL = 1 blocks (1 count)
................... Invalid = 4 blocks (4 count)

PL/SQL ????????
-----------------------------------------------------------------

性能测试：

• 正常访问：

SQL> select count(*) from bigtable;

COUNT(*)
----------
1835014

????: 00: 00: 10.09

SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835014

????: 00: 00: 00.01

• DML下的测试

SQL> insert into bigtable select * from user_objects;

???7??

????: 00: 00: 00.36SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 09.69SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 10.18SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 09.94SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 09.28SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 10.16SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 08.73

SQL> commit;

?????

????: 00: 00: 00.01
SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 13.02SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 23.26SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 15.83SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 08.10SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 00.01SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 00.01

不难发现，如果通过RESULT_CACHE访问的目标表被某些session做DML操作，并且没有commit时，RESULT_CACHE和NO_RESULT_CACHE占用的时间没大的差异。

针对以上的测试结果，RESULT_CACHE主要应用在OLAP系统里或者是一些DML操作极少的表内比较合理。