本文标题:Oracle 11g New Feature:server result cache【本站如从外站转载,会注明出处】
本文链接:http://www.validba.net/2009/07/oracle-11g-new-featureserver-result-cache/
11g新增了一个cache:result cache.跟db cache不同的是,result cache是shared pool的一个组件;只是缓冲sql query的结果.也就是说针对select 查询起作用,同一select查询的结果被缓冲了,下次再次运行这个查询的时候,会直接从result cache中取出数据.返回客户端,sql query不会引起任何的物理读和逻辑读.新参数RESULT_CACHE_MAX_SIZE定义该缓冲区的大小,同时该参数也支持动态修改。默认为32K,如果修改为小于32K的值,那么oracle将disable server 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 672K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
result_cache_mode控制sql query的缓冲方式,manual和force,如果为manual,那么需要在sql中使用result_cahe hint ,才可以缓冲结果集.如果为force,那么oracle将缓冲所有的sql query结果集.
SQL> alter session set result_cache_mode=manual; Session altered. SQL> select * from a1; Execution Plan ---------------------------------------------------------- Plan hash value: 2676145672 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 14 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| A1 | 2 | 14 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------- SQL> select /*+ result_cache */ * from a1; Execution Plan ---------------------------------------------------------- Plan hash value: 2676145672 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 14 | 3 (0)| 00:00:01 | | 1 | RESULT CACHE | 8n41vxqqg25am98bas0msumb6d | | | | | | 2 | TABLE ACCESS FULL| A1 | 2 | 14 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- SQL> alter session set result_cache_mode=force; SQL> select * from a1; Execution Plan ---------------------------------------------------------- Plan hash value: 2676145672 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 14 | 3 (0)| 00:00:01 | | 1 | RESULT CACHE | 8n41vxqqg25am98bas0msumb6d | | | | | | 2 | TABLE ACCESS FULL| A1 | 2 | 14 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------
manual和force管理result_cache方式的不同.这样也提供了我们使用的灵活性.如果对于一个更新频繁的OLTP系统,数据变化快, 相同的sql query可能得到不同的dataset,那么可能会引起result cache频繁的同步数据, 这个时候使用result cache会导致shared pool的争用.那么对于result cache进一步的应用我们拭目以待.
