Oracle Acs资深顾问罗敏 老罗技术核心感悟: 11g性能优化新技术: SQL Query Result Cache

 

作者为: 

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_cache1

 

 

 

 

 

 

 

 

 

以下就是Result Cache的工作原理图:

result_cache2

 

 

 

即:

  1. 当第一个会话进行第一次查询时,将首先从硬盘读取数据 。
  2. 第一个会话同时将该数据存储在SQL Query Result Cache区域 。
  3. 第二个会话进行相同语句查询时,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_cache3

 

可见,执行计划中增加了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技术的使用。以下就是原理示意图:

 

result_cache4

即当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的运行状况:

 

result_cache5

 

可见节点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的简洁的文档,还有一个详细的例子。

 

 

 




Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *