Result Cache 总结

Result Cache是shared pool中的一部分,用于存储查询的结果以提高数据访问速度.它包括SQL和PL/SQL function两部分query result cache.

相关参数:
SQL> show parameter result

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

说明:
1). result_cache_max_size
      设置result cache的内存大小.如果设为0则disable result cache.
2). result_cache_max_result
      该参数指定单个result cache可以使用result cache的最大百分比.
3). result_cache_mode
      它有两个选项:MANUAL, FORCE.
       MANUAL: 在查询语句中必须显式地指明要用result cache.通过hint来实现/* + result_cache */
       FORCE: 所以结果都缓存.通过hint /*+ no_result_cache */来指定不缓存数据.
       例如:
       SELECT /*+ result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
       SELECT /*+ no_result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;

4). result_cache_remote_expiration
      指定访问远程数据库对象的结果有效时间(min).

关于result cache分配大小的说明:
1). 如果只指定了MEMORY_TARGET,则result cache 的大小为memory_target 的0.25%.
2). 如果指定了SGA_TARGET,则result cache 的大小为sga_target 的0.5%.
1). 如果指定了SHARED_POOL_SIZE,则result cache 的大小为shared pool size 的1%.

result cache的统计信息statistics:
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  = 1312K bytes (1312 blocks)
Maximum Result Size = 65K bytes (65 blocks)
[Memory]
Total Memory = 103528 bytes [0.043% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.041% of the Shared Pool]
....... verhead = 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     = 2 blocks (2 count)

PL/SQL procedure successfully completed.

刷新result cache, 删除所以缓存的结果:
SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

监视result cache statistics:
SQL> column name format a20
SQL> select name, value from v$result_cache_statistics;

NAME                      VALUE
-------------------- ----------
Block Size (Bytes)         1024
Block Count Maximum        1312
Block Count Current           0
Result Size Maximum          65
(Blocks)
Create Count Success          0
Create Count Failure          0
Find Count                    0
Invalidation Count            0
Delete Count Invalid          0
Delete Count Valid            0

10 rows selected.

以上查询结果中Create Count Failure, Delete Count Valid的值应该小,而find count的值应该大好.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9466564/viewspace-612443/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9466564/viewspace-612443/