11G的result cache是一个很吸引人的特性，可以大幅减少逻辑读取，特别对于一些经常执行的语句，而结果不是经常变化的，效果不错，我的测试遇到一个小问题。

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 Linux: 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 4000K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

SQL> show parameter memory%target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------
memory_max_target                    big integer 1552M
memory_target                        big integer 1552M

--可以看出我的机器目前result_cache_max_size=4000K,memory_max_target,memory_target=1552.

我增加MEMORY_MAX_TARGET,MEMORY_TARGET到2800M。

$ cat inittest.ora
SPFILE='+DATA/test/spfiletest.ora'
MEMORY_MAX_TARGET=2800M
MEMORY_TARGET=2800M

--注:我的/etc/fstab定义
tmpfs                   /dev/shm                tmpfs   defaults,size=3G        0 0

大于3G,会出现
11:20:50 SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

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 7M
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

SQL> show parameter memory%target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------
memory_max_target                    big integer 2800M
memory_target                        big integer 2800M

--可以发现result_cache_max_size增加到7M,应该这个也是根据内存大小动态调整的。如果机器内存很大，配置memory_*target很大，
result_cache_max_size也应该随着变大。(现在内存16G的服务器应该很多)

1.开始测试:
SQL> alter system set result_cache_max_size=24m;
System altered.
SQL> alter system set result_cache_max_result=100;
System altered.
--result_cache_max_size表示result_cache占用共享池的内存大小，result_cache_max_result指每个会话占用的百分比，缺省是5%，
我测试方便设置占用100.

SQL> create table t as select rownum id, rpad('*', 100, '*') name from dual connect by level <= 300000;

SQL> set autot traceonly --主要目的不是要看执行计划，而是避免滚动时间太长了。
SQL> select /*+ result_cache*/ * from t;

300000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |   276K|    17M|  1032   (1)| 00:00:13 |
|   1 |  RESULT CACHE      | 355yk153mq10cgh9jctsjdpr94 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T                          |   276K|    17M|  1032   (1)| 00:00:13 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(SCOTT.T); name="select /*+ result_cache*/ * from t"

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


Statistics
----------------------------------------------------------
         68  recursive calls
          0  db block gets
       6196  consistent gets
       4616  physical reads
          0  redo size
    3262015  bytes sent via SQL*Net to client
      17009  bytes received via SQL*Net from client
       1501  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     300000  rows processed

--第2次执行
SQL> select /*+ result_cache*/ * from t;

300000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |   276K|    17M|  1032   (1)| 00:00:13 |
|   1 |  RESULT CACHE      | 355yk153mq10cgh9jctsjdpr94 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T                          |   276K|    17M|  1032   (1)| 00:00:13 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(SCOTT.T); name="select /*+ result_cache*/ * from t"

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6124  consistent gets
          0  physical reads
          0  redo size
    3262015  bytes sent via SQL*Net to client
      17009  bytes received via SQL*Net from client
       1501  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     300000  rows processed
--第2次执行,可以发现我的机器result_cache_max_size=24m,还是太小，存在一定的逻辑读，而且应该没有result cache。

2.打开另外的会话执行：
SQL> set timing on
SQL> select count(*) from v$result_cache_memory;
  COUNT(*)
----------
     24576

Elapsed: 00:00:23.55

24秒！仅仅24XXX，需要24秒才能完成，result_cache_max_size=24m，如果更大当result cache填满时，需要的时间不是更长！

再次执行select count(*) from v$result_cache_memory;
在会话1执行select /*+ result_cache*/ * from t;你会发现系统挂起！
如果那个管理人员访问了v$result_cache_memory视图，比如我经常使用toad，不小心点击了v$result_cache_memory视图，前面如果使用
result cache的语句不是全部挂起了吗。

3.接着测试：
在会话1执行：
SQL> select /*+ result_cache*/ count(*) from dept;

在会话2执行：
SQL> select count(*) from v$result_cache_memory;
  COUNT(*)
----------
     24576

Elapsed: 00:00:00.03
--居然发现这次变快了。

查看视图结构:
SQL> desc v$result_cache_memory;
 Name          Null?    Type
 ------------ -------- -----------
 ID                    NUMBER
 CHUNK                 NUMBER
 OFFSET                NUMBER
 FREE                  VARCHAR2(3)
 OBJECT_ID             NUMBER
 POSITION              NUMBER

SQL> select * from v$result_cache_memory where free='NO';
        ID      CHUNK     OFFSET FRE  OBJECT_ID   POSITION
---------- ---------- ---------- --- ---------- ----------
         0          0          0 NO           0          0
         2          0          2 NO           2          0
         4          0          4 NO           4          0
     24448        764          0 NO       24448          0
     24449        764          1 NO       24449          0

--做一个推测,应该是执行select /*+ result_cache*/ count(*) from dept;后释放了原来的占用的result cache memory。

再次执行会话1:
SQL> select /*+ result_cache*/ * from t;

会话2:
SQL> select count(*) from v$result_cache_memory where free='NO';
  COUNT(*)
----------
     24576
--从这里结果可以得出result cache memory有被填满。

总结：
可以发现如果设置result_cache_max_size很大,而且填满，访问视图v$result_cache_memory将是出现可怕的情况，许多与result cache有关的sql语句会挂起！如果你将数据库result_cache_mode=force,情况可能更糟！关于什么原因导致挂起，以后再写！