本文共 7792 字,大约阅读时间需要 25 分钟。
查询结果集cache--oracle 11g release 2
Cache是oracle体系里边一个耳熟能详的机制,在历来的版本演变过程中,oracle都是不遗余力的扩展这个机制。现在我们已经很熟悉了buffer cache,keep cache,library cache,shared pool,PGA/UGA等。除了数据cache和游标共享之外,oracle现在已经支持普通编程模式上的cache;比如子查询cache,全局临时表和联合数组。在11g的版本中,oracle更是扩大了这方面的功能,在服务端和客户端增加了结果集cache。
11g版本中增加的三种结果集cache新特性:
- 查询结果集cache
- PL/SQL函数结果集cache
- 客户端OCI结果集cache
本文只阐述查询结果集cache机制,PL/SQL函数结果集cache特性将是另外一篇文章的主题。
概述
查询结果集cache通常用来存储SQL语句的查询结果,以便重用。通过这个机制,oracle可以避免潜在的时间消耗和不必要的重复操作(比如,排序/聚合,物理I/O,联合查询等),被cache的结果集存储在内存专属区域,在实例范围内可用(而并非仅仅限于一个会话)。不同于关联数组,全局的临时表等解决方案,查询结果集cache对于应用程序来说完全透明,并由oracle自动来维护其一致性。
本文将详细的介绍并测试查询结果集cache机制。
数据库配置
下面将列出与查询结果集cache机制相关的数据库配置参数:
SQL> SELECT name, value, isdefault 2 FROM v$parameter 3 WHERE name LIKE 'result_cache%';
NAME VALUE ISDEFAULT---------------------------------- ------------------ ---------result_cache_mode MANUAL TRUEresult_cache_max_size 1081344 TRUEresult_cache_max_result 5 TRUEresult_cache_remote_expiration 0 TRUE
4 rows selected.
简短的介绍一下这些参数:
-
result_cache_mode: 结果集cache的模式,一共有三种方式,hint,alter session和alter system,默认我们需要通过RESULT_CACHE hint 来显示的实现cache。
-
result_cache_max_size:这个参数限定结果集的最大值,以bytes为单位。缓存从共享池中分配,但又分别管理(举个例子,刷新共享池并不会清空结果集缓存)
-
result_cache_max_result:这个参数限定了一个结果集所使用的总缓存的最大百分比。
-
result_cache_remote_expiration:这个参数限定了依赖于远程对象的结果集的有效时间,默认值为0,即不cache这样的结果集。
缓存的大小可以动态的调整,下面将加倍cache的值,以便后面的例子能够实现更好的结果。 SQL> ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY; System altered. SQL> SELECT name, value 2 FROM v$parameter 3 WHERE name = 'result_cache_max_size'; NAME VALUE---------------------------------------- -------------------------result_cache_max_size 20971521 row selected. 设定这些参数都非常的简单。 下面将通过几个例子来演示本文的主题。 手动cache结果集
oracle实例中cache的默认方式是手工的(manual),即除非使用RESULT_CACHE hint方式,才能使oracle cache查询的结果集。下面的例子,将演示一个手工缓存一个聚合查询的结果集:
SQL> SELECT value 2 FROM v$parameter 3 WHERE name = 'result_cache_mode';
VALUE----------------MANUAL1 row selected.
下面运行查询语句并cache结果集,使用autotrace配置来查看查询语句统计信息和执行计划。
SQL> set autotrace traceonlySQL> set timing onSQL> SELECT /*+ RESULT_CACHE */ 2 p.prod_name 3 , SUM(s.amount_sold) AS total_revenue 4 , SUM(s.quantity_sold) AS total_sales 5 FROM sales s 6 , products p 7 WHERE s.prod_id = p.prod_id 8 GROUP BY 9 p.prod_name; 71 rows selected.Elapsed: 00:00:05.00 使用了RESULT_CACHE hint,oracle cache了结果集,查询返回71行数据共花费了5秒。下表显示本次查询的统计信息和执行计划(注:这里只是理论上的而非实际的执行计划)。 Execution Plan----------------------------------------------------------Plan hash value: 504757596----------------------------------------------------------------------- ... -----------------| Id | Operation | Name | Rows | ... | Pstart| Pstop |----------------------------------------------------------------------- ... -----------------| 0 | SELECT STATEMENT | | 71 | ... | | || 1 | RESULT CACHE | 091zc7mvn8ums36mbd2gqac4h0 | | ... | | || 2 | HASH GROUP BY | | 71 | ... | | ||* 3 | HASH JOIN | | 72 | ... | | || 4 | VIEW | VW_GBC_5 | 72 | ... | | || 5 | HASH GROUP BY | | 72 | ... | | || 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 || 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 || 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | |----------------------------------------------------------------------- ... -----------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("ITEM_1"="P"."PROD_ID")Result Cache Information (identified by operation id):------------------------------------------------------ 1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ p.prod_name, SUM(s.amount_sold) AS total_revenue, SUM(s.quantity_sold) AS total_" 高亮的部分包含了一些新的信息: 首先:ID=1时的操作是“RESULT CACHE”,这是本次查询的最后一步,指示oracle cache了结果集。 其次:除了RESULT CACHE操作外,还有一个系统产生的名称,通常作为一个查找key,用在在系统内部匹配查询语句和cache的结果集。 最后:有一段新的关于cache的报告,这部分内容包含了结果集所依赖的对象及主要的sql语句片段。 下面是autotrace显示的查询统计信息: Statistics---------------------------------------------------------- 14871 recursive calls 0 db block gets 4890 consistent gets 1745 physical reads 0 redo size 3526 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 136 sorts (memory) 0 sorts (disk) 71 rows processed 查询产生了许多的I/O和sql递归调用,下面再一次运行查询,并对比统计信息。 SQL> SELECT /*+ RESULT_CACHE */ 2 p.prod_name 3 , SUM(s.amount_sold) AS total_revenue 4 , SUM(s.quantity_sold) AS total_sales 5 FROM sales s 6 , products p 7 WHERE s.prod_id = p.prod_id 8 GROUP BY 9 p.prod_name; 71 rows selected.Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 504757596----------------------------------------------------------------------- ... -----------------| Id | Operation | Name | Rows | ... | Pstart| Pstop |----------------------------------------------------------------------- ... -----------------| 0 | SELECT STATEMENT | | 71 | ... | | || 1 | RESULT CACHE | 091zc7mvn8ums36mbd2gqac4h0 | | ... | | || 2 | HASH GROUP BY | | 71 | ... | | ||* 3 | HASH JOIN | | 72 | ... | | || 4 | VIEW | VW_GBC_5 | 72 | ... | | || 5 | HASH GROUP BY | | 72 | ... | | || 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 || 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 || 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | |----------------------------------------------------------------------- ... -----------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("ITEM_1"="P"."PROD_ID")Result Cache Information (identified by operation id):------------------------------------------------------ 1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ p.prod_name, SUM(s.amount_sold) AS total_revenue, SUM(s.quantity_sold) AS total_"Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 3526 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) 71 rows processed 统计报告显示,相比第一次查询,本次没有发生I/O,排序,sql递归调用,oracle检测到cache的结果集满足查询需求,所以本次查询仅花费0.1秒。 有趣的是,本次查询的执行计划没有发生变化(因为sql语句没有被重复解析),但是这里却很容易被误导,事实上,执行计划中的操作并没有被执行,但是执行计划中的result cache操作应该设定一个标志,表明重用了cache的结果集。
参考:
PGA 结果集 Oracle 谷歌
转载地址:https://blog.csdn.net/haiross/article/details/38496005 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!