PLSQL_性能优化系列20_Oracle Result Cash结果缓存
20150528 Created By BaoXinjian
一、摘要
SQL 查询结果高速缓存可在数据库内存中对查询结果集和查询碎片启用显式高速缓存。
存储在共享池（Share Pool）中的专用内存缓冲区可用于存储和检索高速缓存的结果。
对查询访问的数据库对象中的数据进行修改后，存储在该高速缓存中的查询结果将失效。
虽然SQL 查询高速缓存可用于任何查询，但最适用于需要访问大量行却仅返回其中一少部分的语句。 数据仓库应用程序大多属于这种情况。
1. 注意点：
(1). RAC 配置中的每个节点都有一个专用的结果高速缓存。
一个实例的高速缓存结果不能供另一个实例使用。
但是，失效会对多个实例产生影响。
要处理RAC 实例之间与SQL 查询结果高速缓存相关的所有同步操作，需对每个实例使用专门的RCBG 进程。
(2). 通过并行查询，可对整个结果进行高速缓存（在RAC 中，是在查询协调程序实例上执行高速缓存的），但单个并行查询进程无法使用高速缓存。
2. 简言之：
高速缓存查询或查询块的结果以供将来重用。
可跨语句和会话使用高速缓存，除非该高速缓存已过时。
3. 优点：
可扩展性
降低内存使用量
4. 适用的语句：
访问多行
返回少数行
二、设置SQL查询结果高速缓存
查询优化程序根据初始化参数文件中RESULT_CACHE_MODE 参数的设置管理结果高速缓存机制。
可以使用此参数确定优化程序是否将查询结果自动发送到结果高速缓存中。
可以在系统和会话级别设置RESULT_CACHE_MODE 参数。
参数值可以是AUTO、MANUAL 和FORCE：
(1) 设置为AUTO 时，优化程序将根据重复的执行操作确定将哪些结果存储在高速缓存中。
(2) 设置为MANUAL（默认值）时，必须使用RESULT_CACHE 提示指定在高速缓存中存储特定结果。
(3) 设置为FORCE 时，所有结果都将存储在高速缓存中。
注：对于AUTO 和FORCE 设置，如果语句中包含[NO_]RESULT_CACHE 提示，则该提示优先于参数设置。
三、管理SQL查询结果高速缓存
可以改变初始化参数文件中的多种参数设置，以管理数据库的SQL 查询结果高速缓存。
默认情况下，数据库会为SGA 中共享池(Share Pool)内的结果高速缓存分配内存。
分配给结果高速缓存的内存大小取决于SGA的内存大小以及内存管理系统。
可以通过设置RESULT_CACHE_MAX_SIZE参数来更改分配给结果高速缓存的内存。
如果将结果高速缓存的值设为0，则会禁用此结果高速缓存。
此参数的值将四舍五入到不超过指定值的32 KB的最大倍数。如果四舍五入得到的值是0，则会禁用该功能。
使用RESULT_CACHE_MAX_RESULT参数可以指定任一结果可使用的最大高速缓存量。
默认值为5%，但可指定1 到100 之间的任一百分比值。可在系统和会话级别上实施此参数。
使用RESULT_CACHE_REMOTE_EXPIRATION参数可以指定依赖于远程数据库对象的结果保持有效的时间（以分钟为单位）。
默认值为0，表示不会高速缓存使用远程对象的结果。
将此参数设置为非零值可能会生成过时的信息：例如，当结果使用的远程表在远程数据库上发生了更改时。
使用以下初始化参数进行管理：
1. RESULT_CACHE_MAX_SIZE
– 此参数设置分配给结果高速缓存的内存。
– 如果将其值设为0，则会禁用结果高速缓存。
– 默认值取决于其它内存设置（memory_target的0.25% 或sga_target 的0.5% 或shared_pool_size 的1%）
– 不能大于共享池的75%
2. RESULT_CACHE_MAX_RESULE
– 设置单个结果的最大高速缓存
– 默认值为5%
3. RESULT_CACHE_REMOTE_EXPIRATION
– 根据远程数据库对象设置高速缓存结果的过期时间
– 默认值为0
四、通过Hint测试Result Cashe
Step1. 创建测试数据表gavin.test_resultcache
create table gavin.test_resultcache as select * from dba_objects;
Step2.1 第一次运行select count(*) from gavin.test_resultcache;
我们第一次执行该SQL可以看到consistent gets和physical reads大致相同
SQL> set autotrace on;
SQL> select count(*) from gavin.test_resultcache;
COUNT(*)
----------
73258
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=293 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST_RESULTCACHE' (TABLE) (Cost=293 Card=72217)
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
1118 consistent gets
1044 physical reads
0 redo size
352 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Step2.2 第二次运行select count(*) from gavin.test_resultcache;
再次执行同样查询时，由于数据Cache在内存中，physical reads会减少到0,但是consistent gets仍然不变
SQL> select count(*) from gavin.test_resultcache;
COUNT(*)
----------
73258
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=293 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST_RESULTCACHE' (TABLE) (Cost=293 Card=72217)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Step2.3 第三次运行select count(*) from gavin.test_resultcache;
加入/*+ result_cache*/将查询结果放入高速缓存中
SQL> select /*+ result_cache */ count(*) from gavin.test_resultcache;
COUNT(*)
----------
73258
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=293 Card=1)
1 0 RESULT CACHE OF '8asjtwtjdzshb8jmtfy6s1rzv9'
2 1 SORT (AGGREGATE)
3 2 TABLE ACCESS (FULL) OF 'TEST_RESULTCACHE' (TABLE) (Cost=293 Card=72217)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1116 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Step2.4 第四次运行select count(*) from gavin.test_resultcache;
在这个利用到Result Cache的查询中，consistent gets减少到0，直接访问结果集，不再需要执行SQL查询。
这就是Result Cache的强大之处。
SQL> select /*+ result_cache */ count(*) from gavin.test_resultcache;
COUNT(*)
----------
73258
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=293 Card=1)
1 0 RESULT CACHE OF '8asjtwtjdzshb8jmtfy6s1rzv9'
2 1 SORT (AGGREGATE)
3 2 TABLE ACCESS (FULL) OF 'TEST_RESULTCACHE' (TABLE) (Cost=293 Card=72217)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Step3. 通过视图查看result cashe的使用和管理情况
1. 通过查询v$result_cache_memory视图来看Cache的使用情况
2. 通过查询v$result_cache_statistics视图来看Result Cache的统计信息
3. 通过查询v$result_cache_objects视图来记录了Cache的对象
Step4. 通过dbms包查看result cashe的使用情况
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 = 1M bytes (1K blocks) Maximum Result Size = 51K bytes (51 blocks) [Memory] Total Memory = 107836 bytes [0.068% of the Shared Pool] ... Fixed Memory = 9440 bytes [0.006% of the Shared Pool] ... Dynamic Memory = 98396 bytes [0.062% of the Shared Pool] ....... Overhead = 65628 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 30 blocks ........... Used Memory = 2 blocks ............... Dependencies = 1 blocks (1 count) ............... Results = 1 blocks ................... SQL = 1 blocks (1 count) PL/SQL procedure successfully completed.
Thanks and Regards
参考：Eygle - http://www.eygle.com/archives/2007/09/11g_server_result_cache.html
参考：Linux - http://www.linuxidc.com/Linux/2012-12/76119.htm
技术交流，技术讨论，欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
