Oracle 11g Result Cache 特性案例分享

最新推荐文章于 2021-03-24 09:23:52 发布

cuiwangxie1183

最新推荐文章于 2021-03-24 09:23:52 发布

阅读量102

点赞数

文章标签：数据库内存管理

Oracle 11g Result Cache 特性案例分享

结果集缓存（Result Cache）是Oracle 11g新引入的功能，除了可以在服务器端缓存结果集（Server Result Cache）之外，还可以在客户端缓存结果集（Client Result Cache）。本文重点介绍Server Result Cache。

1. 基本原理

基本原理就是将 SQL 语句查询结果数据直接存储在内存中，这样后续相同查询语句就直接从该内存中读取了，这将极大地提高该类语句重复查询性能。

1. 当第一个会话进行第一次查询时，将首先从硬盘读取数据1。

2. 第一个会话同时将该数据存储在SQL Query Result Cache 区域2。

3. 第二个会话进行相同语句查询时，Oracle 直接从 SQL Query Result Cache区域读取数据3，将极大地提升查询效率。

可见，Result Cache 具有跨语句、跨会话能力，同时当数据发生变更时，Result Cache 中相关数据将变为非法（INVALID）状态，Oracle 会自动重新，从硬盘读取变更数据，并再次存储在 Result Cache之中。

Result Cache 与 Buffer Cache 的区别

Buffer Cache 中存储的是需要访问的数据，其作用只是将需要到硬盘访问的数据变为内存访问了，而 Result Cache 是访问数据的结果数据。因此，Result Cache的作用比 Buffer Cache有效多了。从下图我们看出 Result Cache占据的是Shared Pool空间。

2. 使用场景

1. 查询的记录数很多，但返回结果数据较少的应用

2. 重复查询频度比较高

3. 数据相对静态，变化量不大

例如，数据仓库系统的各种统计运算就是比较典型的应用场景。

3. Result Cache 使用过程

3.1 参数设置

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。

3.2 Result Cache的使用

通常，不建议通过采取 RESULT_CACHE_MODE 设置为 FORCE 而强制使用 Result Cache 的策略。因为，系统将所有查询操作结果都考虑进行缓存，反而会增加系统不必要的开销。

语句中增加 /*+ RESULT_CACHE */的HINT。如下例所示

可见，执行计划中增加了 RESULT CACHE 操作，所访问的 Object Name

“9ygccamyvv39j1h19vkm7suh5y”是该语句结果集在 Result Cache 中的编号

（Cache_ID）。

3.3 对比表缓存

缓存表前

缓存表后

对比前后的统计输出，没有发现多少性能提升。

3.4 Result Cache 的管理

DBMS_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 = 225472K bytes (225472 blocks)

Maximum Result Size = 11273K bytes (11273 blocks)

[Memory]

Total Memory = 1470408 bytes [0.014% of the Shared Pool]

... Fixed Memory = 166376 bytes [0.002% of the Shared Pool]

... Dynamic Memory = 1304032 bytes [0.012% of the Shared Pool]

....... Overhead = 157152 bytes

....... Cache Memory = 1120K bytes (1120 blocks)

........... Unused Memory = 66 blocks

........... Used Memory = 1054 blocks

............... Dependencies = 7 blocks (7 count)

............... Results = 1047 blocks

................... SQL = 530 blocks (8 count)

................... Invalid = 517 blocks (6 count)

PL/SQL procedure successfully completed.

清空 Result Cache

如下语句可清空 Result Cache，包括所有已存在的结果集数据：

SQL> exec DBMS_RESULt_CACHE.FLUSH;

PL/SQL procedure successfully completed.

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 = 24672K bytes (24672 blocks)

Maximum Result Size = 1233K bytes (1233 blocks)

[Memory]

Total Memory = 5352 bytes [0.000% of the Shared Pool]

... Fixed Memory = 5352 bytes [0.000% of the Shared Pool]

... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

将指定表的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设置和该内存使用情况的统计信息。

(G)V$RESULt_CACHE_MEMORY

该视图显示 Result Cache所有内存块和相关统计信息。

(G)V$RESULt_CACHE_OBJECTS

该视图显示 Result Cache 中被缓存的对象，包括结果集数据和依赖的表及相关属性数据。

(G)V$RESULt_CACHE_DEPENDENCY

该视图显示结果集数据和依赖表的关联关系

3.5 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 的局限

系统临时表（Temporary Table）和数据字典表不支持 Result Cache 技术。

非确定的（Nodeterministic）PL/SQL函数不支持Result Cache 技术。

查询语句若出现序列的 CURRVAL、NEXTVAL，则不支持 Result Cache 技术。

查询语句若出现 current_date, sysdate, sys_guid 等函数, 则不支持Result Cache技术。

其他

Result Cache支持Flashback 查询。

Result Cache 并不会自动释放其内存，Oracle 将一直使用该内存到其最大限额值（RESULt_CACHE_MAX_SIZE），然后通过 FIFO 技术，释放相关内存。另外，通过 DBMS_RESULt_CACHE.FLUSH 可清空 Result Cache 内存。

Result Cache 支持绑定（BIND）变量的使用。针对绑定变量语句，Result Cache 不仅保存结果集，而且保存相关变量值。这样，只有相同变量值的查询语句，方可使用其在Result Cache中的结果集。

4. 某客户端数据库案例

生产上一条频繁执行的sql执行时长达13s以上

select b.recommend,

b.class_recommend,

b.app_pakagename,

b.app_keystore,

b.icon_up_date,

b.new_flag,

b.icon_note,

b.app_name,

b.provider,

b.packagestate,

b.newestapkver,

b.version,

b.file_id,

b.icon_id,

b.appadpt_id,

b.version_note,

b.valid_apkver,

b.newestapkver,

'0' as appvf_id,

b.file_size,

b.appnotice,

b.app_notice_time,

b.check_md5,

b.ver_type,

b.appclass_id,

b.appclass_name,

b.app_level

from (select rownum no, a.*

from (select t.*,

f.file_id as icon_id,

f.create_date as icon_up_date,

(case

when sysdate between f.new_stime and f.new_etime then

'1'

else

'0'

end) as new_flag

from (select distinct p.recommend as recommend,

p.class_recommend as class_recommend,

p.ver_id,

p.app_code as appadpt_id,

p.ver_name as app_name,

p.provider as provider,

p.app_pakagename as app_pakagename,

p.app_keystore as app_keystore,

'' as icon_note,

decode(p.stat, 's0p', '1', 's0s', '5') as packagestate,

p.file_id as file_id,

p.ver_code as version,

p.description as version_note,

(select regexp_replace(f_vm_getstr(ver_code),

';',

'|')

from t_info_app

where ver_name = p.ver_name

and stat = 's0p'

and app_model_type =

p.app_model_type) as valid_apkver,

p.ver_code as newestapkver,

p.check_md5 as check_md5,

p.file_size as file_size,

p.app_displaylevel as app_level,

(select i.app_notice

from t_actinst a,

t_actinfo i

where a.stat = 's0a'

and i.app_id = p.ver_id

and a.actinfo_id = i.actinfo_id

and a.start_time >= trunc(sysdate)

and a.end_time < trunc(sysdate + 1)

and rownum = 1) as appnotice,

p.file_type as ver_type,

(select to_char(max(f.create_date),

'yyyymmddhh24miss')

from t_app_notice f

where f.app_id = p.ver_id

and f.stat = 's0a') as app_notice_time,

p.cid as appclass_id,

(select class_name

from t_info_appclass

where class_id = p.cid) as appclass_name

from v_info_app p

where (p.ver_code, p.ver_id) in

(select max(app.ver_code), max(app.ver_id)

from v_info_app app

where ((app.grp_ids != '0' and

f_vm_iscontain(app.grp_ids || ';',

';' || (case

when :1 is null then

when (select distinct mu_code

from t_mu

where mu_code = :3

and mu_type = :4

and (area_code = :5 or

area_code = :6)) is null then

else

(select case

when f_sym_get_grpid(m.mu_id,

m.mu_type,

m.area_code,

m.org_code) is null then

else

f_sym_get_grpid(m.mu_id,

m.mu_type,

m.area_code,

m.org_code) || ';' || :9

end

from t_mu m

where 1 = 1

and m.mu_code = :10

and m.mu_type = :11

and (m.area_code = :12 or

m.area_code = :13))

end) || ';') = 1) or

app.grp_ids = '0')

and app.ver_id in

(select objsub_id

from t_adp

where obj_type = '01'

and adp_type = :14

and objmain_id = '4')

and app.chl_code = :15

and (app.file_type = 0 or app.file_type = 1 or

p.file_type = 4 or p.file_type = 5)

and (app.stat = 's0p' or app.stat = 's0s')

and app.ver_update is not null

and app.match_cid = :16

and (app.app_model_type = :17 or

app.app_model_type = :18)

and app.app_code not in

(select app_code

from t_adp_ver_app

where (mobel_type = :19 or

mobel_type = :20)

and prod_type = :21

and ver_code = :22

and stat = 's0a'

and ver_name =

(select ver_name

From t_client

where prod_type = :23

and cid = :24))

group by app.cid, app.ver_name)

and (p.file_type = 0 or p.file_type = 1 or

p.file_type = 4 or p.file_type = 5)) t

inner join (select a.objsub_id,

tf.file_id,

tf.create_date,

tf.file_type,

tf.new_stime,

tf.new_etime

from t_adp a, t_file tf

where a.obj_type = 21

and a.objmain_id = tf.file_id(+)

and tf.file_type = '2') f

on t.ver_id = f.objsub_id) a

where 1 = 1

order by recommend desc) b

where b.no > to_number(:25)

涉及的表

t_info_app

t_actinst

t_actinfo

t_app_notice

t_info_appclass

t_adp

t_group

t_term

t_file

t_channel

t_adp_ver_app

t_client

t_mu

v_info_app查询较慢，改写缓存v_info_app结果集

create or replace view v_info_app as

with t21 as

(

select /*+ result_cache */a.objmain_id,

f.file_id,

f.file_src_name,

f.file_name,

f.file_type,

f.file_size,

f.file_path,

f.app_keystore,

f.check_md5,

f.new_stime,

f.new_etime,

i.*

from t_adp a,

t_info_app i,

t_file f

where a.obj_type = '21'

and a.objsub_id = i.ver_id(+)

and f.file_id = a.objmain_id

t11 as

(select /*+ result_cache */a.objmain_id,

decode(regexp_replace(to_char(wmsys.wm_concat(distinct i.ver_id)), ',', ';'),'','0',regexp_replace(to_char(wmsys.wm_concat(distinct i.ver_id)), ',', ';')) as oldver_ids,

decode(regexp_replace(to_char(wmsys.wm_concat(distinct i.ver_code)), ',', ';'),'','0',regexp_replace(to_char(wmsys.wm_concat(distinct i.ver_code)), ',', ';')) as upfor

from t_adp a,

t_info_app i

where a.obj_type = '11'

and a.objsub_id = i.ver_id(+)

group by a.objmain_id),

t15 as

(select /*+ result_cache */a.objmain_id,

c.chl_id,

c.chl_parentid,

c.chl_code,

c.chl_name

from t_adp a,

t_channel c

where a.obj_type = '15'

and a.objsub_id = c.chl_id(+)),

t13 as

(select /*+ result_cache */a.objmain_id,

(select i.ver_name from t_info_app i where i.ver_id = a.objmain_id) as new_ver_name,

decode(regexp_replace(to_char(wmsys.wm_concat(distinct g.grp_name)), ',', ';'),'','0',

regexp_replace(to_char(wmsys.wm_concat(distinct g.grp_name)), ',', ';')) as grps,

decode((regexp_replace(to_char(wmsys.wm_concat(distinct g.grp_id)), ',', ';')),'','0',

regexp_replace(to_char(wmsys.wm_concat(distinct g.grp_id)), ',', ';') ) as grp_ids

from t_adp a,

t_group g

where a.obj_type = '13'

and a.objsub_id = g.grp_id(+)

group by a.objmain_id),

t16 as

(select /*+ result_cache */a.objmain_id,

decode(regexp_replace(to_char(wmsys.wm_concat(distinct t.term_code)), ',', ';'),'','0',regexp_replace(to_char(wmsys.wm_concat(distinct t.term_code)), ',', ';')) as term_codes,

decode(regexp_replace(to_char(wmsys.wm_concat(distinct t.term_id)), ',', ';'),'','0',regexp_replace(to_char(wmsys.wm_concat(distinct t.term_id)), ',', ';')) as term_ids

from t_adp a,

t_term t

where a.obj_type = '16'

and a.objsub_id = t.term_id(+)

group by a.objmain_id)

select /*+ result_cache */t21.ver_id ,

t21.cid,

t11.oldver_ids,

t21.ver_name,

t21.ver_update,

t21.recommend,

t21.class_recommend,

t21.app_code,

t21.app_pakagename,

t21.description,

t21.memo,

t11.upfor,

t21.file_id,

t21.file_src_name,

t21.file_name,

t21.file_type,

t21.file_size,

t21.file_path,

t21.ver_code,

t21.app_keystore,

t21.check_md5,

t21.provider,

t21.new_stime,

t21.new_etime,

t21.app_displaylevel,

t21.app_model_type,

t21.offline_stat,

t21.match_cid,

t15.chl_id,

t15.chl_parentid,

t15.chl_code,

t15.chl_name,

t13.grp_ids,

t13.grps,

t16.term_ids,

t16.term_codes,

t21.create_date,

t21.public_date,

t21.stat

from t21,

t11,

t15,

t13,

t16

where 1 = 1

and t21.ver_id = t15.objmain_id(+)

and t15.objmain_id = t11.objmain_id(+)

and t15.objmain_id = t13.objmain_id(+)

and t15.objmain_id = t16.objmain_id(+);

优化前后对比：

节点1CPU

节点2CPU

CPU使用率由原来的均值30%左右降低到10%

AWR统计

Sql 执行时间

Sql的执行时间由原来的平均13s降低到不到1s

Sql执行计划对比

缓存前

缓存后

Consistent gets 下降为原来的1/15。

总体看下来本次缓存结果集效果是很理想的。当然数据库级别的优化调整只是起到一种缓解作用，解决根本问题还是着力从业务角度拆解优化该语句。

--BY JWL

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

转载于:http://blog.itpub.net/30109892/viewspace-1984760/

cuiwangxie1183

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 11g Result Cache 特性案例分享

Oracle 11g ResultCache 特性案例分享结果集缓存（Result Cache）是Oracle 11g新引入的功能，除了可以在服务器端缓存结果集（Server Result Cache）...
复制链接

扫一扫