结果集缓存
缓存是一种最常见的在计算机系统用来提高性能的技术.硬件和软件被广泛的使用.oracle数据库也不会例外.例如在缓冲区缓存中缓存数据文件块,在字典缓存中缓存字典信息,在库缓存中缓存游标.在oracle11G中结果集缓存也是可用的.
结果集缓存是如何工作的
oracle数据库引擎提供了三种结果集缓存:
服务器结果集缓存也叫查询结果缓存,是一种服务器端缓存,它用来存储查询的结果集.
pl/sql函数结果集缓存是一种服务器端缓存,它用来存储pl/sql函数返回的结果集.
客户端结果集缓存是一种客户端缓存,用来存储查询结果集.
服务器结果集缓存
服务器结果集缓存是用来避免重复执行查询.简单来说查询第一次执行,它的结果集被存储在共享池中.然后后续执行相同的查询时从结果集缓存中直接提取结果集而不用重新计算.注意这两个查询被认为是相等的.因此能使用相同的结果集.另外如果出现绑定变量,那么它们的值必须相同.这是必须的,因为很明显,绑定变量作为参数输入并传给查询.因此不同的绑定变量值会有不同的结果集.注意结果集缓存是存储在共享池中,对于一个指定的实例所有连接的会话都能共享相同的缓存条目.
下面来举例说明.注意在查询时指定了result_cache提示来启用结果集缓存.第一次执行花了1.04秒.可以看到在执行计划中操作result cache确定了对查询启用了结果集缓存.在执行计划中starts列清楚的说明了所有的操作都至少要被执行一次.执行计划中所有的操作都是必须的因为是第一次执行这个查询.因此结果集缓存还不包含结果集.
SQL> alter session set statistics_level=all; SQL> SELECT /*+ result_cache */ 2 p.prod_category, c.country_id, 3 sum(s.quantity_sold) AS quantity_sold, 4 sum(s.amount_sold) AS amount_sold 5 FROM sh.sales s, sh.customers c, sh.products p 6 WHERE s.cust_id = c.cust_id 7 AND s.prod_id = p.prod_id 8 GROUP BY p.prod_category, c.country_id 9 ORDER BY p.prod_category, c.country_id; 已选择81行。 已用时间: 00: 00: 01.04 SQL> select * from table(dbms_xplan.display_cursor(null,null,'all allstats')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID g9sxqz9qgutu7, child number 0 ------------------------------------- SELECT /*+ result_cache */ p.prod_category, c.country_id, sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS amount_sold FROM sh.sales s, sh.customers c, sh.products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY p.prod_category, c.country_id ORDER BY p.prod_category, c.country_id Plan hash value: 1866882273 -------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 985 (100)| | | | 81 |00:00:00.01 | | | | | 1 | RESULT CACHE | cu8pf4s7jn9d05yda1swvwpd7y | 1 | | | | | | | 81 |00:00:00.01 | | | | | 2 | SORT GROUP BY | | 1 | 68 | 3808 | 985 (7)| 00:00:12 | | | 0 |00:00:00.01 | 73728 | 73728 | | |* 3 | HASH JOIN | | 1 | 968 | 54208 | 984 (7)| 00:00:12 | | | 0 |00:00:00.01 | 940K| 940K| | | 4 | VIEW | index$_join$_003 | 1 | 72 | 1512 | 3 (34)| 00:00:01 | | | 0 |00:00:00.01 | | | | |* 5 | HASH JOIN | | 1 | | | | | | | 0 |00:00:00.01 | 1269K| 1269K| | | 6 | INDEX FAST FULL SCAN| PRODUCTS_PK | 1 | 72 | 1512 | 1 (0)| 00:00:01 | | | 0 |00:00:00.01 | | | | | 7 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 1 | 72 | 1512 | 1 (0)| 00:00:01 | | | 0 |00:00:00.01 | | | | | 8 | VIEW | VW_GBC_9 | 1 | 968 | 33880 | 981 (7)| 00:00:12 | | | 0 |00:00:00.01 | | | | | 9 | HASH GROUP BY | | 0 | 968 | 26136 | 981 (7)| 00:00:12 | | | 0 |00:00:00.01 | 56M| 4744K| | |* 10 | HASH JOIN | | 1 | 918K| 23M| 941 (3)| 00:00:12 | | | 0 |00:00:00.01 | 3056K| 1398K| | | 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 541K| 406 (1)| 00:00:05 | | | 0 |00:00:00.01 | | | | | 12 | PARTITION RANGE ALL| | 1 | 918K| 14M| 530 (3)| 00:00:07 | 1 | 28 | 0 |00:00:00.01 | | | | | 13 | TABLE ACCESS FULL | SALES | 28 | 918K| 14M| 530 (3)| 00:00:07 | 1 | 28 | 0 |00:00:00.01 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
第二次执行只花了0.03秒.在执行计划中starts列显示了所有操作的执行次数.RESULT CACHE被执一次.其它的操作没有被执行.换句话说直接使用了存储在结果集缓存中的结果集.
SQL> SELECT /*+ result_cache */ 2 p.prod_category, c.country_id, 3 sum(s.quantity_sold) AS quantity_sold, 4 sum(s.amount_sold) AS amount_sold 5 FROM sh.sales s, sh.customers c, sh.products p 6 WHERE s.cust_id = c.cust_id 7 AND s.prod_id = p.prod_id 8 GROUP BY p.prod_category, c.country_id 9 ORDER BY p.prod_category, c.country_id; 已选择81行。 已用时间: 00: 00: 00.03 SQL> select * from table(dbms_xplan.display_cursor(null,null,'all allstats')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID g9sxqz9qgutu7, child number 0 ------------------------------------- SELECT /*+ result_cache */ p.prod_category, c.country_id, sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS amount_sold FROM sh.sales s, sh.customers c, sh.products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY p.prod_category, c.country_id ORDER BY p.prod_category, c.country_id Plan hash value: 1866882273 -------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 985 (100)| | | | 162 |00:00:00.01 | | | | | 1 | RESULT CACHE | cu8pf4s7jn9d05yda1swvwpd7y | 1 | | | | | | | 162 |00:00:00.01 | | | | | 2 | SORT GROUP BY | | 0 | 68 | 3808 | 985 (7)| 00:00:12 | | | 0 |00:00:00.01 | 73728 | 73728 | | |* 3 | HASH JOIN | | 0 | 968 | 54208 | 984 (7)| 00:00:12 | | | 0 |00:00:00.01 | 940K| 940K| | | 4 | VIEW | index$_join$_003 | 0 | 72 | 1512 | 3 (34)| 00:00:01 | | | 0 |00:00:00.01 | | | | |* 5 | HASH JOIN | | 0 | | | | | | | 0 |00:00:00.01 | 1269K| 1269K| | | 6 | INDEX FAST FULL SCAN| PRODUCTS_PK | 0 | 72 | 1512 | 1 (0)| 00:00:01 | | | 0 |00:00:00.01 | | | | | 7 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 0 | 72 | 1512 | 1 (0)| 00:00:01 | | | 0 |00:00:00.01 | | | | | 8 | VIEW | VW_GBC_9 | 0 | 968 | 33880 | 981 (7)| 00:00:12 | | | 0 |00:00:00.01 | | | | | 9 | HASH GROUP BY | | 0 | 968 | 26136 | 981 (7)| 00:00:12 | | | 0 |00:00:00.01 | 56M| 4744K| | |* 10 | HASH JOIN | | 0 | 918K| 23M| 941 (3)| 00:00:12 | | | 0 |00:00:00.01 | 3056K| 1398K| | | 11 | TABLE ACCESS FULL | CUSTOMERS | 0 | 55500 | 541K| 406 (1)| 00:00:05 | | | 0 |00:00:00.01 | | | | | 12 | PARTITION RANGE ALL| | 0 | 918K| 14M| 530 (3)| 00:00:07 | 1 | 28 | 0 |00:00:00.01 | | | | | 13 | TABLE ACCESS FULL | SALES | 0 | 918K| 14M| 530 (3)| 00:00:07 | 1 | 28 | 0 |00:00:00.01 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
在执行计划中要注意一个名字缓存ID它与操作result cache相关.如果知道这个缓存ID,可以查询v$resul_cache_objects视图来显示关于缓存数据的信息.下面的查询显示缓存结果集已经被发布(换句话说可以使用).视图信息会显示缓存结果集是何进创建的,创建它花了多长时间,有多少行记录被存储和它被引用了多少次.其它提供关于缓存结果集信息的视图还有v$result_cache_dependency,v$result_cache_memory和v$result_chace_statistics.
SQL> SELECT status, creation_timestamp, build_time, row_count, scan_count 2 FROM v$result_cache_objects 3 WHERE cache_id = 'cu8pf4s7jn9d05yda1swvwpd7y'; STATUS CREATION_TIMESTAMP BUILD_TIME ROW_COUNT SCAN_COUNT --------- ------------------ ---------- ---------- ---------- Published 2013-7-5 9:21:26 94 81 3
为了保证结果的一致性(也就是说结果集是相同的就是看它是来自缓存还是来自数据库计算).每当查询中所引用的对象发生改变,那么依赖于这些表的缓存条目将会无效.事实情况就是这样,即使真正的改变没有发生.例如.甚至一个select * for update语句后面紧跟着一个commit提交也会导致依赖于select表的缓存条目变为无效.
有一些动态初始化参数可以控制服务器结果集缓存:
result_cache_max_size:以byte为单位来指定在共享池中用于结果集缓存的内存总量.如果它被设置为0,这个功能将会被禁用.缺省值是一个比0大的值,它是从共享池中派生出来的.内存分配是动态的,因此初始化参数只能指定它的上限.可以使用下面的查询来显示当前分配的内存大小:
SQL> SELECT name, sum(bytes) FROM v$sgastat WHERE name LIKE 'Result Cache%' GROUP BY rollup(name);
NAME SUM(BYTES)
-------------------------- ----------
Result Cache 161680
Result Cache: Bloom Fltr 2048
Result Cache: Cache Mgr 208
Result Cache: Memory Mgr 200
Result Cache: State Objs 2896
167032
下面的语句显示了resulr_cache_max_size的值为15424K
SQL> show parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 15424K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
result_cache_mode:指定在什么情况下使用结果集缓存.可以将它设置为manual手动这是缺省值或者设置为force.当使用manual时结果集缓存只有当指定result_cache提示时才使用.当使用force时那么会对所有没有使用no_result_cache提示的所有查询使用结果集缓存.因为在大多数情况下我们只会对少量的查询使用结果集缓存,所以让该参数设置为它的缺省值manual当只需要使用时在查询中指定result_cache来启用它.
result_cache_max_result:指定单一个结果集缓存的占用result_cache_max_size服务器结果集缓存大小的百分比.它的缺省值是5.它的值可以是0到100的任何一个数字.超过这个限制的结果集缓存将是无效的.
result_cache_remote_expiration:指定基于远程对象的结果集的有效时间(以分钟为单位).这是必须的,因当远程对象已经发生改变了基于这些远程对象的失效的结果集缓存不能被执行.当经过初始化参数所指定的有效时间后这些结果集才变为无效.这个参数的缺省值是0,意味着基于远程对象的查询缓存功能被禁用.
初始化参数result_cache_max_size和result_cache_max_result只能在系统级别进行修改.其它的result_cache_mode
和result_cache_remote_expiration能在会话级别进行修改.
注意:将result_cache_remote_expiration参数设置为一个比0大的数字会导致过时的结果集缓存存在.因此只有你真正地理解这样做的影响后才能将它设置为比0大.
使用结果集缓存有几个很明显的限制:
查询使用非确定性的函数,序列和临时表是结果集不会被缓存
查询违反了读一致性时结果集将不会被缓存.例如,当通过会话创建结果集时所引用的表正经历大量的事务这样的结果
集不会被缓存.
引用数据字典视图的查询的结果集不会被缓存.
dbms_result_cache包
可以使用dbms_result_cache包来管理结果集缓存,它提供了以下程序来进行管理:
bypass:在会话或系统级别临时禁用或启用结果集缓存.
flush:从结果集缓存中清空所有对象
invalidate:让依赖于指定的数据库对象的所有结果集缓存变为无效
invalidate_object:让单个结果集缓存条目变为无效
memory_report:生成一个内存使用情况报告
status:显示结果集缓存的状态.
例如:
SQL> select dbms_result_cache.status from dual; STATUS -------------------------------------------------------------------------------- ENABLED
pl/sql函数结果集缓存
pl/sql函数结果集缓存类似于服务器结果集缓存,但是它支持pl/sql函数,还和服务器结果集缓存共享相同的内存结构.它的目的是在结果集缓存中存储函数返回的值(仅仅只有函数返回的值,过程返回的值不能被缓存).明显地使用不同输入值的函数是以不同的缓存条目被缓存在结果集缓存中的.在下面的例子中显示了一个启用结果集缓存的函数.为了启用pl/sql函数结果集缓存,要指定result_cache子句.还可以指定relies_on子句来指定函数依赖于哪个表来返回结果.
SQL> CREATE OR REPLACE FUNCTION f 2 RETURN NUMBER 3 RESULT_CACHE RELIES_ON(t) IS 4 l_ret NUMBER; 5 BEGIN 6 SELECT count(t.indi_id) INTO l_ret FROM impl_chenzhou.bs_insured t; 7 RETURN l_ret; 8 END; 9 / Function created
在下面的例子中将会调用函数2480625次f不使用结果集缓存(通过使用bypass过程来临时禁用结果集缓存)共用了4.69秒
SQL> execute dbms_result_cache.bypass(bypass_mode => TRUE, session => TRUE); SQL> select count(f) from impl_chenzhou.bs_insured t; COUNT(F) ---------- 2480625 Elapsed: 00:00:04.69 在下面的例子中将会调用函数2480625次f使用结果集缓存共用了0.32秒 SQL> execute dbms_result_cache.bypass(bypass_mode => FALSE, session => TRUE) SQL> select count(f) from impl_chenzhou.bs_insured t; COUNT(F) ---------- 2480625 Elapsed: 00:00:00.32
注意:如果relies_on子句没有指定或者包含错误信息,当函数依赖的对象发生修改结果集缓存不会变为无效.因此可能会出现过时的结果集.
使用pl/sql函数结果集有一些限制,下面的函数不能使用结果集缓存:
使用out和(或者)in out参数的函数
定义了使用调用者权限的函数
管道化表函数
从匿名块调用函数
使用in参数或者返回值有以下类型的函数:LOB,REF CURSOR,对象和记录
客户端结果缓存集
客户端结果集缓存是用客户端缓存来存查询的结果集.它的目的和工作类似于服务器端结果集缓存.与服务器端的实现进行比较有两个重要的不同.第一它避免了需要在客户端/服务器之间来回地执行sql语句.这是一大优点.第二结果集的失效是基于一种轮询机制,因此一致性不能保证这是一大缺点.
为了实现这种轮询客户端必须定期地执行数据库调用来检查数据库引擎看看它的结果集缓存是否已经变为无效了.为了轮询的开销最小化,每一次客户端由于其它原因执行一个数据库调用时它将检查结果集缓存的有效性.这样,就可以避免掉那些用于对缓存的结果集进行失效操作的数据库调用.使客户端能持续地执行”正常的”数据库调用.
尽管它是客户端缓存但还是要在服务器端来启用它.下面有些参数来控制客户端缓存:
client_result_cache_size:指定每一个客户端进程能使用的结果集缓存的最大内存大小以byte为单位.如果它设置为0,这也是缺省值那么这个功能将禁用.这个初始化参数是静态的只能在实例级别进行修改.修改完后必须对实例进行重启才会生效.
client_result_cache_lag:指定两次数据库调用之间的最大时间间隔以毫秒为单位.也就是说它指定无效的结果集能在客户端缓存中保留多长的时间.缺省值是3000.这个初始化参数是静态的只能在实例级别进行修改.修改完后必须对实例进行重启才会生效.
oci_result_cache_max_size:会覆盖服务器端设置的初始化参数client_result_cache-size.然而要注意是如果在服务器禁用了结果集缓存这个参数将不能激活它.
oci_result_cache_max_rset_size:指定单个结果集缓存最多能使用的内存大小以byte为单位.
oci_result_cache_max_rset_rows:指定单个结果集缓存最多能存储的返回行数.
何时使用
当你遇到由程序反复执行相同的操作所导致的性能问题时,你要么减少执行的频率要么减少操作的响应时间.理想的情况时两者都做.然而有时(例如由于应用程序代码不能修改)你只能实现后者.为了减少响应时间可以使用各种优化技术,如果还不能满足要求那就只能使用高级优化技术了象结果集缓存.基本上要有效使用结果集缓存要满足两个条件.第一相对于修改数据来说同样的数据查询的更频繁.第二要有足够的内存来存储结果集.
在大多数情况下不能对所有的查询都启用结果集缓存.事实上在大多数时候只有特定的查询才能从结果集缓存中获益而对于其它的情况来说,结果集缓存只不过是一种纯粹的额外的开销说不定还会使用缓存过载.还要记住的是服务器端缓存是对所有会话共享的,因此它们的访问是要同步的(它们也会象所有的共享资源一样变成一个串处理点).因此只有在真正查询请求它们的时候才会结果集缓存.也就是说只当真正需要使用它们来提高性能时才在查询中指定result_cache提示.
服务器端结果集缓存无法完全避免执行一个查询的额外开销.这意味着如果一个查询在不使用结果集缓存的情况下对于每一行执行了最少的逻辑读(不是物理读)了,那么使用结果集缓存性能不会提高很多.请记住高速缓存和结果集缓存都是存储在相同的共享内存中的.
pl/sql函数结果集缓存对于经常在sql语句中使用的函数来说特别有用.事实上常遇到这样的情况被处理或被返回的每一行都会调用一次这个函数,同时输入的参数也只有几个不同的值,然而这个函数经常从pl/sql中被频繁地调用它能使用结果集缓存.
因为一致性的问题客户端缓存只应该用在只读表或主要是读的表上.
最后要注意的是可以同进使用服务器和客户端结果集缓存.然而对于客户端执行的查询不能选择只使用服务器结果集缓存而不使用客户端结果集缓存.也就是说两种结果集缓存都会被使用.