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
:2
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
:7
else
(select case
when
f_sym_get_grpid(m.mu_id,
m.mu_type,
m.area_code,
m.org_code)
is null then
:8
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