SHOUG成员 – ORACLE ACS高级顾问罗敏
第一次听说SQL Query Result Cache这个11g新技术，是在2007年11月的Oracle公司内部11g培训课程中。可能是因为11g新特性太多，而且那次连续几天的培训，被老师轰炸得审美疲劳了，对这个新技术并没有留下太深印象。当时只是感觉Oracle又多了个什么Cache，也没完全弄明白其原理，也不知道与传统Buffer Cache有什么区别，更不知道这个技术适合于什么场景。
待日后有精力再深入研究该技术，特别是结合客户相关实际问题，才猛然发现Oracle这个新技术太牛了！Oracle公司也太富有创新能力了，居然在几十年难得一动的SGA和Shared_Pool内存架构中增加了Result Cache结构，并提供了新的SQL Query Result Cache技术，解决了很多重复查询语句导致资源开销过大的典型问题。
Result Cache到底是什么新技术？如何使用？适合于什么应用场景？… …。希望本章的内容能回答这些问题，更希望您别像我当年一样，仅仅是走马观花地了解一下而已，而是能立马激动起来，并能运用到您的实际应用开发工作中去。呵呵。
Result Cache原理
基本原理
11g出了个SQL Query Result Cache的新特性，简称Result Cache技术吧。其基本原理就是将SQL语句查询结果数据直接存储在内存中，这样后续相同查询语句就直接从该内存中读取了，这将极大地提高该类语句重复查询性能。
为此，Oracle在SGA的Shared Pool内存中专辟了一个Result Cache内存，如下图所示：
以下就是Result Cache的工作原理图：
即：
- 当第一个会话进行第一次查询时，将首先从硬盘读取数据 。
- 第一个会话同时将该数据存储在SQL Query Result Cache区域 。
- 第二个会话进行相同语句查询时，Oracle直接从SQL Query Result Cache区域读取数据 ，将极大地提升查询效率。
可见，Result Cache具有跨语句、跨会话能力，同时当数据发生变更时，Result Cache中相关数据将变为非法（INVALID）状态，Oracle会自动重新从硬盘读取变更数据，并再次存储在Result Cache之中。
Result Cache与Buffer Cache的区别
当2007年11月在参加11g新特性培训中，第一次听说这个技术时，一时没有反应过来：“Oracle不是将查询数据已经存储在Buffer Cache中了吗？怎么又来一个Result Cache呢？”后来，稍加琢磨，反应过来了：Buffer Cache中存储的是需要访问的数据，其作用只是将需要到硬盘访问的数据变为内存访问了，而Result Cache是访问数据的结果数据。因此，Result Cache的作用比Buffer Cache有效多了。
例如：
SELECT department_id, AVG(salary) FROM emp GROUP BY department_id; … … 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 16045 consistent gets 0 physical reads 0 redo size 726 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed
上述语句如果使用Buffer Cache技术，Oracle只是将employees表所有记录存储在Buffer Cache中，后续语句仍然要从Buffer Cache中访问employees表所有记录，并进行按department_id的部门平均工资统计，例如上述语句依然有16045次consistent gets访问。而如果使用了Result Cache技术，Oracle则将该语句如下查询结果存储到Result Cache中：
DEPARTMENT_ID AVG(SALARY) ------------- ----------- 100 8601.33333 30 4150 7000 20 9500 70 10000 90 19333.3333 110 10154 50 3475.55556 40 6500 80 8955.88235 10 4400 60 5760 已选择12行。
后续语句直接从Result Cache中访问这些结果数据，显然结果数据比明细数据少得多。再看该语句的资源消耗情况：
SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM emp GROUP BY department_id; … … 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 726 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed
可见，此时Oracle不仅没有访问硬盘（physical reads = 0），而且也根本没有访问Buffer Cache（consistent gets=0）！
因此，Result Cache技术非常适合于满足如下条件的场景：
- 查询的记录数很多，但返回结果数据较少的应用
- 重复查询频度比较高
- 数据相对静态，变化量不大
例如，数据仓库系统的各种统计运算就是比较典型的应用场景。
Result Cache使用过程
初始化参数的设置
欲使用Result Cache，首先需要考虑初始化参数的设置。以下是几个典型参数的含义和配置建议：
- RESULT_CACHE_MODE
该参数表示是否需要采用Result Cache技术，取值如下：
- MANUAL：表示当在语句中增加相关HINT（RESULT_CACHE）时，才使用Result Cache技术。该值为缺省值。
- FORCE：表示只要有可能，所有查询语句都将使用Result Cache技术。
无论该参数如何设置，Oracle将优先考虑RESULT_CACHE和NO_RESULT_CACHE的 hint。
- RESULT_CACHE_MAX_SIZE
该参数设置Result Cache的最大容量。如果设置为0，则将关闭Result Cache功能。该参数的缺省值，依赖于内存管理模式和相关参数配置。例如：
- 当只设置memory_target参数时，RESULT_CACHE_MAX_SIZE = memory_target*0.25%。
- 当设置sga_target参数时，RESULT_CACHE_MAX_SIZE = sga_target*0.5%。
- 当设置shared_pool_size参数时，RESULT_CACHE_MAX_SIZE = shared_pool_size*1%。
该参数最大不能超过shared_pool_size的75%。
- RESULT_CACHE_MAX_RESULT
该参数为单个SQL查询语句设置可使用的最大Result Cache容量，缺省为RESULT_CACHE_MAX_SIZE的5%。
- RESULT_CACHE_REMOTE_EXPIRATION
该参数表示当SQL语句访问远程数据库对象时，允许远程对象数据发生变化的过期时间。缺省值为0，表示一旦远程对象数据发生变化，相关查询的Result Cache数据变为INVALID。
Result Cache的使用
当上述初始化参数设置好之后，我们就可以考虑Result Cache技术的使用了。通常，我们不建议通过采取RESULT_CACHE_MODE设置为FORCE而强制使用Result Cache的策略。因为，这将导致系统将所有查询操作结果都考虑进行缓存，反而会增加系统不必要的开销。
如果将RESULT_CACHE_MODE设置为MANUAL，则语句中应增加/*+ RESULT_CACHE */的HINT。如下例所示：
SELECT /*+ RESULT_CACHE */ department_id, AVG(salary)
FROM emp
GROUP BY department_id;
执行计划如下：
可见，执行计划中增加了RESULT CACHE操作，所访问的Object Name “8k66uas70mk859zxgaq75kfq9u”是该语句结果集在Result Cache中的编号（Cache_ID）。
以下语句就是强制不使用Result Cache技术，即便RESULT_CACHE_MODE设置为FORCE。
SELECT /*+ NO_RESULT_CACHE */ department_id, AVG(salary)
FROM emp
GROUP BY department_id;
再看如下语句：
SELECT prod_subcategory, revenue
FROM (SELECT /*+ RESULT_CACHE */ p.prod_category,
p.prod_subcategory,
sum(s.amount_sold) revenue
FROM products p, sales s
WHERE s.prod_id = p.prod_id and
s.time_id BETWEEN to_date('01-JAN-2006','dd-MON-yyyy')
and
to_date('31-DEC-2006','dd-MON-yyyy')
GROUP BY ROLLUP(p.prod_category, p.prod_subcategory))
WHERE prod_category = 'Women';
如果在上述in-line视图中使用了Result Cache技术，Oracle将不会再进行视图的合并（Merge）等变更操作，而是直接将视图结果存储在Result Cache中，后续查询包括外围条件发生变化，例如prod_category = ‘Men’，都将使用Result Cache技术。
Result Cache的管理
DBMS_RESULT_CACHE包的使用
通过11g的 DBMS_RESULT_CACHE包，可以对Result Cache进行相关管理工作：
- Result Cache状态
如下语句可查询Result Cache状态：
SQL> SELECT DBMS_RESULT_CACHE.STATUS FROM DUAL;
STATUS
——–
ENABLED
- Result Cache的使用情况
如下语句可查询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 = 3168K bytes (3168 blocks) Maximum Result Size = 158K bytes (158 blocks) [Memory] Total Memory = 107812 bytes [0.044% of the Shared Pool] ... Fixed Memory = 9460 bytes [0.004% of the Shared Pool] ... Dynamic Memory = 98352 bytes [0.040% of the Shared Pool] ....... Overhead = 65584 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 过程已成功完成。
- 清空Result Cache
如下语句可清空Result Cache，包括所有已存在的结果集数据：
SQL> exec DBMS_RESULT_CACHE.FLUSH; PL/SQL 过程已成功完成。 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 = 3168K bytes (3168 blocks) Maximum Result Size = 158K bytes (158 blocks) [Memory] Total Memory = 9460 bytes [0.004% of the Shared Pool] ... Fixed Memory = 9460 bytes [0.004% of the Shared Pool] ... Dynamic Memory = 0 bytes [0.000% of the Shared Pool] PL/SQL 过程已成功完成。
- 将指定表的Result Cache设置为INVALID
如下语句将HR用户下的EMP表的Result Cache设置为INVALID：
SQL> EXEC DBMS_RESULT_CACHE.INVALIDATE(‘HR’,’EMP’);
PL/SQL 过程已成功完成。
相关视图的使用
- (G)V$RESULT_CACHE_STATISTICS
该视图显示Result Cache设置和该内存使用情况的统计信息。例如：
| ID | NAME | VALUE |
| 1 | Block Size (Bytes) | 1024 |
| 2 | Block Count Maximum | 3168 |
| 3 | Block Count Current | 32 |
| 4 | Result Size Maximum (Blocks) | 158 |
| 5 | Create Count Success | 2 |
| 6 | Create Count Failure | 0 |
| 7 | Find Count | 1 |
| 8 | Invalidation Count | 1 |
| 9 | Delete Count Invalid | 0 |
| 10 | Delete Count Valid | 0 |
| 11 | Hash Chain Length | 1 |
- (G)V$RESULT_CACHE_MEMORY
该视图显示Result Cache所有内存块和相关统计信息。
- (G)V$RESULT_CACHE_OBJECTS
该视图显示Result Cache中被缓存的对象，包括结果集数据和依赖的表及相关属性数据。例如，以下是部分字段查询结果：
| ID | TYPE | STATUS | BUCKET_NO | HASH | NAME |
| 0 | Dependency | Published | 371 | 2.947E+09 | HR.EMP |
| 2 | Result | Published | 3135 | 1.132E+09 | SELECT /*+ RESULT_CACHE */ department_id, AVG(salary)
FROM emp GROUP BY department_id |
| 1 | Result | Invalid | 3135 | 1.132E+09 | SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM emp GROUP BY department_id |
- (G)V$RESULT_CACHE_DEPENDENCY
该视图显示结果集数据和依赖表的关联关系，例如：
| RESULT_ID | DEPEND_ID | OBJECT_NO |
| 2 | 0 | 74569 |
查询结果显示结果集2（V$RESULT_CACHE_OBJECTS 表的ID =2 ）与依赖数据0（V$RESULT_CACHE_OBJECTS 表的ID = 0 ）有关联关系，该依赖数据的OBJECT_NO为74569，即HR.EMP表。
Result Cache相关技术点
Result Cache与RAC
RAC支持Result Cache技术。RAC环境中的每个实例都有自己的Result Cache，每个实例的Result Cache不能共享，即保存在Result Cache中的数据只能被本实例的应用进行访问。但是，一旦保存在某个Result Cache中的数据变成INVALID，则整个RAC环境中各Result Cache中的该数据都将变成INVALID。Oracle通过专门的RCBG进程，处理RAC环境下Result Cache之间的数据同步。
Result Cache与并行处理
并行处理也支持Result Cache技术。在并行查询中，整个查询结果集将被保存在Result Cache中，也就是说，整个并行查询语句方可使用Result Cache中的查询结果，单个并行查询子进程无法访问Result Cache。在RAC环境下，并行查询结果保存在查询协调进程（Query Coordinator）所在实例的Result Cache中。
Result Cache的局限
Result Cache技术存在如下一些限制：
- 系统临时表（Temporary Table）和数据字典表不支持Result Cache技术。
- 非确定的（Nodeterministic）PL/SQL函数不支持Result Cache技术。
- 查询语句若出现序列的CURRVAL、NEXTVAL，则不支持Result Cache技术。
- 查询语句若出现current_date, sysdate, sys_guid等函数, 则不支持Result Cache技术。
Result Cache更多技术限制，请参阅11g联机文档《Oracle® Database Performance Tuning Guide11g Release 2 (11.2)》第7章相关内容，以及其它Oracle文档。
Result Cache的其它技术点
- Result Cache支持Flashback查询。
- Result Cache并不会自动释放其内存，Oracle将一直使用该内存到其最大限额值（RESULT_CACHE_MAX_SIZE），然后通过FIFO技术，释放相关内存。另外，通过FLUSH可清空Result Cache内存。
- Result Cache支持绑定（BIND）变量的使用。针对绑定变量语句，Result Cache不仅保存结果集，而且保存相关变量值。这样，只有相同变量值的查询语句，方可使用其在Result Cache中的结果集。
Result Cache更多技术点，请参阅11g联机文档《Oracle® Database Performance Tuning Guide11g Release 2 (11.2)》第7章相关内容，以及其它Oracle文档。
7.5 客户端Result Cache技术
客户端也支持Result Cache
前面介绍的Result Cache技术，主要针对服务器端。实际上，11g在客户端也支持Result Cache技术，即OCI Client Query Cache技术。该技术特点如下：
- 将服务器端的Result Cache扩展到客户端，将充分利用客户端内存，降低服务器端内存开销，提高了整个系统的扩展性。
- 运用客户端的Result Cache技术，将降低网络往返传输开销（round-trips），从而显著提高系统整体性能。
- 当服务器端的数据发生变化时，Oracle将自动刷新客户端的Result Cache数据。
- 客户端的Result Cache技术主要适合于重复查询、频度较高的SQL语句，而且结果数据较少、数据相对静态的应用。例如，大量代码表的查询等。
- 服务器端和客户端的Result Cache技术是相互独立的，可以分别进行设置。
- 通过client_result_cache_stats$、v$client_result_cache_stats视图，可监控客户端的Result Cache的使用。
客户端Result Cache的使用
- 数据库初始化参数
- CLIENT_RESULT_CACHE_SIZE参数
该参数定义了单个客户端进程所使用的Result Cache最大值。如果设置为0，则关闭了客户端Result Cache技术。该参数缺省值即为0。
- CLIENT_RESULT_CACHE_LAG参数
该参数定义客户端与服务端最后一次往返（round-trip）时间阀值，在该时间阀值内，客户端将向服务端查询客户端Result Cache中的数据是否发生变化，该参数缺省值为3000毫秒。即每隔3秒，Oracle将客户端Result Cache中的数据与服务端进行一次同步。
- 客户端相关配置
通过在客户端的sqlnet.ora配置文件中设置如下参数，可以进行客户端Result Cache的配置。
- OCI_RESULT_CACHE_MAX_SIZE
- OCI_RESULT_CACHE_MAX_RSET_SIZE
- OCI_RESULT_CACHE_MAX_RSET_ROWS
这些参数设置了单个客户端进程可使用的Result Cache最大值(Bytes)或最大记录数。客户端参数的设置将优先于服务器端的参数，例如CLIENT_RESULT_CACHE_SIZE参数。
欲使用客户端Result Cache，应用程序应与11.1以上版本客户端库进行连接，并访问11.1以上数据库服务器。
PL/SQL中Result Cache的使用
- 原理
PL/SQL支持Result Cache技术的使用。以下就是原理示意图：
即当PL/SQL函数Calculate第一次执行时，Oracle将按正常方式执行，并将执行结果存储在Result Cache中。而在后续的执行过程中，当该函数涉及的数据和调用参数没有发生变化时，Oracle将避免重复计算，而是直接将Result Cache中保存的该函数结果返回客户。而当该函数涉及的数据和调用参数发生变化时，Oracle将自动重新计算该函数，并将结果数据再次存储在Result Cache中。
在PL/SQL中运用Result Cache技术，特别适合于大批量复杂计算，并且数据相对静态的应用，而且对应用本身透明，更省去了在应用层面进行结果数据存储的开发和管理工作。
- 使用过程
以下就是PL/SQL中Result Cache的使用案例：
CREATE OR REPLACE FUNCTION productName (prod_id NUMBER, lang_id VARCHAR2) RETURN NVARCHAR2 RESULT_CACHE RELIES_ON (product_descriptions) IS result VARCHAR2(50); BEGIN SELECT translated_name INTO result FROM product_descriptions WHERE product_id = prod_id AND language_id = lang_id; RETURN result; END;
即在函数声明部分增加RESULT_CACHE关键字，并且可增加RELIES_ON短语，表示该函数依赖于product_descriptions表。当product_descriptions表的数据发生变更时，该函数的结果集将变为INVALID。
- 相关限制
在如下情况下，PL/SQL无法使用Result Cache技术。
- 当函数定义在一个需要调用者权限的模块中，或者定义在一个匿名块中。
- 该函数是一个管道表（Pipelined Table）函数。
- 该函数有OUT或IN OUT参数。
- 该函数包括BLOB、CLOB、NCLOB、REF CURSOR、collection、object、 record等类型参数。
- 该函数返回类型包括BLOB,、CLOB、NCLOB、REF CURSOR、object、record，以及包含上述不支持返回类型的collection。
适合Result Cache的典型案例
相关背景
- 系统概述
某运营商总部的集中结算系统共有2套互为HA备份的单机系统，节点1主要承担联机交易，节点2主要承担报表、统计等批处理业务。两套系统主要问题都是运行状态不佳。例如以下是我们在现场调研时发现的节点1的运行状况：
可见节点1系统的CPU、I/O利用率都几乎达到100%，并经常导致系统宕机和重启。
- 数据库层面资源消耗情况
以下是节点1数据库的Load Profile：
| Per Second | Per Transaction | |
| Redo size: | 1,023,082.84 | 13,340.70 |
| Logical reads: | 1,838,583.03 | 23,974.58 |
| Block changes: | 5,770.79 | 75.25 |
| Physical reads: | 3,237.45 | 42.22 |
| Physical writes: | 774.41 | 10.10 |
| User calls: | 6,344.54 | 82.73 |
| Parses: | 3,289.28 | 42.89 |
| Hard parses: | 2.72 | 0.04 |
| Sorts: | 1,876.35 | 24.47 |
| Logons: | 42.58 | 0.56 |
| Executes: | 4,098.31 | 53.44 |
| Transactions: | 76.69 |
可见内存和I/O开销都非常大。
以下是Top 5 Timed Events
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
| latch: cache buffers chains | 639,561 | 35,422 | 55 | 28.1 | Concurrency |
| CPU time | 27,473 | 21.8 | |||
| SQL*Net message from dblink | 85,888 | 1,848 | 22 | 1.5 | Network |
| SQL*Net more data from dblink | 3,937,144 | 1,587 | 0 | 1.3 | Network |
| latch free | 16,422 | 1,333 | 81 | 1.1 | Other |
最高等待事件是：latch: cache buffers chains，说明带系统存在比较严重的热块数据。
主要问题分析
- 最高等待事件分析
如上所述，最高等待事件latch: cache buffers chains说明带系统存在比较严重的热块数据。进一步分析如下Segments by Logical Reads数据：
| Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | Logical Reads | %Total |
| STL_USR | COMMDATA | T_FILE_INFO_81 | TABLE | 1,125,308,256 | 33.98 | |
| SETTLE | STATDATA | T_FILE_INFO_18 | TABLE | 952,259,776 | 28.75 | |
| STL_USR | COMMDATA | T_FILE_INFO_13 | TABLE | 218,271,024 | 6.59 | |
| STL_USR | COMMDATA | T_FILE_INFO_30 | TABLE | 145,521,632 | 4.39 | |
| STL_USR | COMMDATA | T_FILE_INFO_50 | TABLE | 143,596,320 | 4.34 |
可见T_FILE_INFO_81和T_FILE_INFO_18是该系统被集中频繁访问的表。
- 典型应用分析
进一步分析，在Top-SQL语句中，访问上述两表的语句占了绝大多数。例如：
select count(*) from t_file_info_18 where province_id = :"SYS_B_0" and operation_type_grade = :"SYS_B_1" and flow_id = :"SYS_B_2" and insert_ip = :"SYS_B_3" and (state = :"SYS_B_4" or state = :"SYS_B_5")
上述语句目前执行计划为全表扫描，该表目前已达到500多万记录，容量1.6GB。
该表虽然已经建立了（PROVINCE_ID，OPERATION_TYPE_GRADE，FLOW_ID，STATE, INSERT_IP）的复合索引，但由于该索引的可选性不高，所以Oracle优化器选择了全表扫描。
- 解决方式建议
最佳解决方式是11g SQL Query Result Cache技术！经与应用和业务部门沟通，T_FILE_INFO_18表数据的查询频度远高于DML操作频度，而且上述语句是全表扫描，访问结果仅仅是求和一条记录。即便基表T_FILE_INFO_18表数据发生变化，Oracle也会自动将Result Cache中相关内容变为INVALID，而再次访问变化数据，并将变化数据的结果集再次存储在Result Cache中，供后续相同查询语句访问。预计Result Cache技术的运用将极大地解决该系统突出的性能问题。
可惜现在该系统还无法使用Result Cache技术，为什么？因为该系统还运行在10g平台，暂时还没有计划升级到11g。想唱戏的连个舞台都没有，呵呵。
本章参考资料及进一步读物
本章参考资料及进一步读物：
| 序号 | 资料类别 | 资料名称 | 资料概述 |
| 1. | Oracle 11g R2联机文档 | 《Oracle® Database Performance Tuning Guide》 | 请大家重点看第7章第7.6节“Managing the Server and Client Result Caches” |
| 2. | Oracle 11g R2联机文档 | 《Oracle® Database Administrator’s Guide》 | 请大家重点看第6章 “Specifying the Result Cache Maximum Size”小节 |
| 3. | Oracle大学教材 | 《Oracle® Database 11g New Features》第11章 | 这是Oracle大学教材。欲看到图文并茂的该文档，只能报名参加该课程的培训了。 |
| 4. | My Oracle Support | 《SQL Query Result Cache. Includes: [Video] (Doc ID 1108133.1)》 | 系统介绍SQL Query Result Cache的文档，还有视频哦，听听标准的美式英语吧。 |
| 5. | My Oracle Support | 《11g New Feature : SQL Query Result Cache (Doc ID 453567.1)》 | 一篇介绍SQL Query Result Cache的简洁的文档，以及一个例子。 |
| 6. | My Oracle Support | 《11g New Feature PL/SQL Function Result Cache (Doc ID 430887.1)》 | 一篇介绍PL/SQL Function Result Cache的简洁的文档，还有一个详细的例子。 |
Leave a Reply