Oracle 11g新特性:Result Cache
Oracle 11g新特性:Result Cache之一
发布日期:2021-07-16 22:42:43
浏览次数:28
分类:技术文章
本文共 10369 字,大约阅读时间需要 34 分钟。
Oracle 11g新特性:Result Cache之一
在Oracle Database 11g中,Oracle引入了一个令人关注的新特性:结果集缓存(Result Cache)。
顾名思义,这个新特性的含义就是将查询的结果集Cache起来,以便随后相同的查询请求可以直接利用,从而避免了再次查询。今天开始来学习一下这个新特性。 进一步的 Result Cache又可以分为:Server Result Cache 和 Client Result Cache。 前者通过服务器端SGA来缓存结果集,后者通过客户端来缓存结果集。 缓存是提高性能的一个常用手段,可以说在Oracle数据库中,Cache无处不在。 对于Client Result Cache: 在使用OCI应用程序时,可以通过客户端内存来缓存查询的结果集,缓存结果可以在所有session间共享,当查询反复执行时,查询结果可以直接从客户段的缓存中获得,从而极大地提高应用效率。 客户端结果集缓存并不使用服务器端的内存,不会对服务器的内存使用造成影响,这一点和Server Result Cache不同。 同Client Result Cache相关的视图主要有:SQL> select * from dict where table_name like '%CLIENT_RESULT_CACHE%'; TABLE_NAME COMMENTS ------------------------------ --------------------------------------------- CLIENT_RESULT_CACHE_STATS$ Synonym for CRCSTATS_$ GV$CLIENT_RESULT_CACHE_STATS Synonym for GV_$CLIENT_RESULT_CACHE_STATS V$CLIENT_RESULT_CACHE_STATS Synonym for V_$CLIENT_RESULT_CACHE_STATS同Client Result Cache相关的参数有:
SQL> show parameter client_result NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 0如果我们不想启用Client Result Cache的特性,可以设置参数client_result_cache_size为0即可。 对于Server Result Cache: 服务器端结果集缓存使用Shared Pool中的内存来进行结果缓存,这部分内存使用可以通过v$sgastat视图来查询观察:
SQL> select * from v$sgastat 2 where lower(name) like '%result%'; POOL NAME BYTES ------------ -------------------------- ---------- shared pool Result Cache: State Objs 2852 shared pool Result Cache: Memory Mgr 124 shared pool Result Cache: Bloom Fltr 2048 shared pool Result Cache: Cache Mgr 108进一步的和Result Cache相关的视图有:
SQL> select * from dict where table_name like '%RESULT_CACHE%'; TABLE_NAME COMMENTS ------------------------------ --------------------------------------------- CLIENT_RESULT_CACHE_STATS$ Synonym for CRCSTATS_$ GV$CLIENT_RESULT_CACHE_STATS Synonym for GV_$CLIENT_RESULT_CACHE_STATS GV$RESULT_CACHE_DEPENDENCY Synonym for GV_$RESULT_CACHE_DEPENDENCY GV$RESULT_CACHE_MEMORY Synonym for GV_$RESULT_CACHE_MEMORY GV$RESULT_CACHE_OBJECTS Synonym for GV_$RESULT_CACHE_OBJECTS GV$RESULT_CACHE_STATISTICS Synonym for GV_$RESULT_CACHE_STATISTICS V$CLIENT_RESULT_CACHE_STATS Synonym for V_$CLIENT_RESULT_CACHE_STATS V$RESULT_CACHE_DEPENDENCY Synonym for V_$RESULT_CACHE_DEPENDENCY V$RESULT_CACHE_MEMORY Synonym for V_$RESULT_CACHE_MEMORY V$RESULT_CACHE_OBJECTS Synonym for V_$RESULT_CACHE_OBJECTS V$RESULT_CACHE_STATISTICS Synonym for V_$RESULT_CACHE_STATISTICS 11 rows selected.相关的参数主要有:
SQL> show parameter result_cache_max NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_max_result integer 5 result_cache_max_size big integer 992K类似的,result_cache_max_size参数可以被看作Server Result Cache的一个开关,如果设置为0,则Server Result Cache功能会被禁用。而result_cache_max_result则用于定义每个Cache结果集能够使用的Result Cache的百分比。 参数result_cache_mode用于控制Server Result Cache的方式,该参数有3个选项:Manual、AUTO、Force
SQL> show parameter result_cache_mo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_mode string MANUAL关于这个新特性的参考链接: http://www.ningoo.net/2007/08/22/oracle11g_new_feature_server_result_cache2.htm http://yangtingkun.itpub.net/post/468/391015
http://yangtingkun.itpub.net/post/468/391560
==========================
Oracle 11g新特性:Server Result Cache测试
现在我们来看一下Server Result Cache的作用。
首先创建一张测试表:SQL> connect eygle/eygle Connected. SQL> create table eygle as select * from dba_objects; Table created.在以前版本中,我们第一次执行该SQL可以看到consistent gets和physical reads大致相同:
SQL> set autotrace on SQL> select count(*) from eygle; COUNT(*) ---------- 15993 Execution Plan ---------------------------------------------------------- Plan hash value: 3602634261 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 | -------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 28 recursive calls 0 db block gets 282 consistent gets 217 physical reads 0 redo size 420 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed再次执行同样查询时,由于数据Cache在内存中,physical reads会减少到0. 但是consistent gets仍然不变:
SQL> select count(*) from eygle; COUNT(*) ---------- 15993 Execution Plan ---------------------------------------------------------- Plan hash value: 3602634261 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 | -------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 221 consistent gets 0 physical reads 0 redo size 420 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed现在我们来看看在Server Result Cache下,Oracle的行为。 首先在result_cache_mode参数设置为MANUAL时:
SQL> show parameter result_cache_mode NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_mode string MANUAL我们需要在SQL中手工指定Cache,这需要通过加入一个hints来实现,这个hints是result_cache:
SQL> select /*+ result_cache */ count(*) from eygle; COUNT(*) ---------- 15993 Execution Plan ---------------------------------------------------------- Plan hash value: 3602634261 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 | | 1 | RESULT CACHE | 76rwwyazv6t6c39f1d8rrqh8rb | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(EYGLE.EYGLE); attributes=(single-row); name="select /*+ result_cache */ count(*) from eygle" Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 280 consistent gets 0 physical reads 0 redo size 420 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed注意到这个执行计划已经和以往的不同,RESULT CACHE以76rwwyazv6t6c39f1d8rrqh8rb名称创建。 那么在接下来的查询中,这个Result Cache就可以被利用:
SQL> select /*+ result_cache */ count(*) from eygle; COUNT(*) ---------- 15993 Execution Plan ---------------------------------------------------------- Plan hash value: 3602634261 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 | | 1 | RESULT CACHE | 76rwwyazv6t6c39f1d8rrqh8rb | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(EYGLE.EYGLE); attributes=(single-row); name="select /*+ result_cache */ count(*) from eygle" Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 420 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed在这个利用到Result Cache的查询中,consistent gets减少到0,直接访问结果集,不再需要执行SQL查询。 这就是Result Cache的强大之处。 我们可以通过查询v$result_cache_memory视图来看Cache的使用情况:
SQL> select * from V$RESULT_CACHE_MEMORY 2 where FREE='NO'; ID CHUNK OFFSET FRE OBJECT_ID POSITION ---------- ---------- ---------- --- ---------- ---------- 0 0 0 NO 0 0 1 0 1 NO 1 0通过V$RESULT_CACHE_STATISTICS可以查询Result Cache的统计信息:
SQL> select * from V$RESULT_CACHE_STATISTICS; ID NAME VALUE ---------- ------------------------------ ---------- 1 Block Size (Bytes) 1024 2 Block Count Maximum 992 3 Block Count Current 32 4 Result Size Maximum (Blocks) 49 5 Create Count Success 1 6 Create Count Failure 0 7 Find Count 1 8 Invalidation Count 0 9 Delete Count Invalid 0 10 Delete Count Valid 0 10 rows selected.V$RESULT_CACHE_OBJECTS记录了Cache的对象:
SQL> SELECT ID,TYPE,NAME,BLOCK_COUNT,ROW_COUNT FROM V$RESULT_CACHE_OBJECTS; ID TYPE NAME BLOCK_COUNT ROW_COUNT ---------- ---------- ------------------------------ ----------- ---------- 0 Dependency EYGLE.EYGLE 1 0 1 Result select /*+ result_cache */ cou 1 1 nt(*) from eygle一个新的系统包被引入,DBMS_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 = 992K bytes (992 blocks) Maximum Result Size = 49K bytes (49 blocks) [Memory] Total Memory = 100836 bytes [0.059% of the Shared Pool] ... Fixed Memory = 5132 bytes [0.003% of the Shared Pool] ... Dynamic Memory = 95704 bytes [0.056% of the Shared Pool] ....... Overhead = 62936 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. 参考: 查询结果集cache 谷歌
转载地址:https://blog.csdn.net/haiross/article/details/38512971 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
逛到本站,mark一下
[***.202.152.39]2024年04月05日 07时24分24秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
网络安全工程师的初学路径
2021-06-29
离群点检测和新奇检测之离群点检测
2019-04-26
离群点检测和新奇检测之新奇检测
2019-04-26
几种常见的离群点检验方法
2019-04-26
学习前后端技术的建议
2019-04-26
javascript的介绍,变量let和var声明,变量作用域,解构赋值
2019-04-26
统一资源定位符URL
2019-04-26
HTTP keep-alive详解
2019-04-26
字符编码(python编码机制的过去和现在)
2019-04-26
python序列的高阶函数:map,reduce,filter,sorted
2019-04-26
python模块的定义,和模块的作用域
2019-04-26
面向对象,即class类,类的封装,属性的类别划分,
2019-04-26
面向对象的继承和多态,剖析对象的信息(获取相关信息),类属性和实例属性
2019-04-26
JS的语句标识,注释,区分大小写
2019-04-26
JS的变量,使用strict模式
2019-04-26
对象,对象属性和方法
2019-04-26
Ubuntu的安装,搜狗输入法的安装
2019-04-26
itertools处理迭代
2019-04-26