Oracle 11g：SQL查询结果集缓存

【IT专家网独家】随着过去三十年Oracle关系型数据库管理系统的发展，它提供给数据库管理员几个选项将经常被访问的数据保存在内存中以减少或消除从数据库的I/O子系统读取不必要的数据。下面是使数据库管理员可以在她的工具中找到的一些特性的快速概览，这些特性帮助维护对查询数据库内存中相关数据的良好控制。

　　持久缓存数据库块。 CACHE 存储选项(还提供了+CACHE优化器提示)提供了一个为数据表保存所有数据库块的方法，只要这个表被表扫描进数据库缓存区中。一旦通过这个方法进行了缓存区，相关的缓存就绝不会从缓存区中过期，直到数据库实例终止。这个策略有一些缺陷，因为它依赖于数据库管理员对哪个表最能受益于缓存的了解，它还倾向于作为一个用于编码表和参照数据的万用解决方法而被过度使用。

　　影响数据库块的保存持久性。Oracle还通过创建或改变一个表或索引以便它保存在这个缓存池中，从而提供了影响缓存保持在数据库缓存区中的KEEP缓存池中的能力。Oracle只是简单的将缓存尽可能长时间的保存在KEEP池中;本质上来说，它们放置在缓存中更接近最常使用(MRU)的一端。但是，没有什么是永远的，当大型查询需要大量缓存来完成时，就可能使KEEP缓存池中的缓存最终过期。

　　永久地保存结果集。物化视图(MV)提供了一个保存经常查询的数据的方法：通过创建一个具有基本表的视图来保存一个特定记录集一段较长时间的能力。如果配置适当，Oracle将“重写”一个现有查询以便它查询物化视图而不是直接查询基本的数据库表。此外，可以建立一个物化视图以便对它的基本基础数据的改变可以自动地对依赖的物化视图进行更新。但是，物化视图的最明显优势也是它的潜在危害：因为它需要定期地进行更新以保证它的数据是最新的，数据的实际刷新可能花费额外的时间，甚至有可能发生在很不恰当的时刻。

　　我们真正需要的是持久性比这些特性短一些的东西(仍然很出色!)：一段只存储一个查询结果的内存，它可以与任何其它需要相似数据的存储共享。例如，一个“编码表”捕捉U.S.各州和土地面积，它可能只是一个具有几行和几列的表，并且几乎都不改变，所以它应该很少需要被更新。因此，当这个结果集不再有效时，我希望它可以自己更新而不需要我进行任何干预。那么经常被几个用户频繁执行、但不经常利用物化视图的查询重写功能的查询怎么样呢?物化视图在创建、配置和刷新方面不是那么简单，所以这个特性需要比物化视图更易于建立，而且必须能够只花费几分钟的执行耗费来刷新它本身。

　　SQL查询结果集缓存

　　Oracle Database 11g提供了结果集缓存来提供这个功能。一个SQL查询结果集将取决于几个新的初始化参数的设置，被缓存在共享全局区(SGA)的一个数据库实例共享池的子段中。

　　RESULT_CACHE_MODE。这个新参数接受三个值之一，它可以被设置为数据库(ALTER SYSTEM)或单独会话(ALTER SESSION)级别：

• 　　当设置为MANUAL(默认)时，如果查询本身指定了+RESULT_CACHE优化器提示，那么一个SQL查询结果将只被认为是可能被缓存。
• 　　但是如果这个参数设置为FORCE，那么查询的结果将总是被缓存，除非这个查询指定了+NO_RESULT_CACHE优化器提示。
• 　　最后，如果这个参数设置为了AUTO，那么Oracle 11g使用一个未发布的内部算法来自动地根据结果集从未来语句执行受益频繁度来决定查询结果集是否应该被缓存。只有当这个查询指定了+NO_RESULT_CACHE优化器提示时它才会被忽略。

　　控制结果集缓存内存的利用。Oracle 11g还提供了几个方法来限制使得分配给SQL查询结果集缓存的内存数量是合适的：

　　RESULT_CACHE_MAX_SIZE。为所有的本地结果缓存预留适当的SGA内存量，数据库管理员可以为RESULT_CACHE_MAX_SIZE初始参数指定一个数值。Oracle 11g自动地将这个提供的数值四舍五入到最接近的32K界限。

　　如果没有提供数值，那么Oracle 11g使用下面的算法来为结果缓存分配内存：

　　如果为新的Oracle 11g MEMORY_TARGET 参数指定了数值(例如分配给数据库实例的SGA和PGA的总内存)，那么Oracle将设置RESULT_CACHE_MAX_SIZE为MEMORY_TARGET的0.25%。

　　如果没有为MEMORY_TARGET设置数值，但是设置了SGA_TARGET的数值，那么Oracle 11g将RESULT_CACHE_MAX_SIZE设置为SGA_TARGET的0.5%。

　　最后，如果没有为MEMORY_TARGET设置数值，也没有为SGA_TARGET设置数值，那么Oracle根据的SHARED_POOL_SIZE设置将RESULT_CACHE_MAX_SIZE设置为分配给共享池的1.0%。

　　注意，无论使用哪个算法，Oracle 11g都不会将RESULT_CACHE_MAX_SIZE设置为超过SHARED_POOL_SIZE的75%。此外，要注意如果数据库管理员想使SQL结果缓存特性完全失效，那么她仅仅需要设置这个内存分配空间规模为0来告诉Oracle 11g不为结果缓存保留任何内存空间。

　　RESULT_CACHE_MAX_RESULT。 这个参数告诉Oracle 11g每个单个查询应该允许多少结果缓存。它的默认值是整个结果缓存的5%，这通常应该是足够的，但是它也可以设置为0%到100%。

RESULT_CACHE_REMOTE_EXPIRATION。 如果一个查询依赖于一个远程数据库，那么这个参数决定一个结果集应该保留的分钟数。默认数值为0分钟，这是作为对远程数据库表的任何改变不能在本地数据库检测到的一个提醒，因此陈旧的结果集可能会不适当地保持一段较长时间。这个参数可以设置为全局(ALTER SYSTEM)或对每个会话(ALTER SESSION)。

　　创建SQL查询结果缓存：一个简要的示例

　　在Listing1.1中，是关于怎样在MANUAL模式中使用SQL查询结果缓存特性的一个实际例子：

• 　　首先使用DBMS_RESULT_CACHE.PURGE(看下一节的详细描述)净化结果缓存，激活MANUAL结果缓存，然后将结果缓存的规模设置得相对较小，只有1MB。
• 　　然后用一个SQL查询从销售历史(SH)架构的PROMOTIONS表内容中捕捉一个关于总的和平均的提升成本的摘要级别表示。这个结果记录设置从源表超过500条的记录中捕捉不超过10条的记录，所以它对于SQL查询结果缓存是个较好的候选方式。
• 　　然后对原始的查询使用一个EXPLAIN PLAN，包括+RESULT_CACHE提示以便可以决定这个刚刚创建的结果缓存是否应该被以后的查询利用。它还创建了一个报表，详细地显示了结果缓存的内存是怎样被使用的。这是这个输出的一个示例：

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 = 1M bytes (1K blocks)
　　Maximum Result Size = 10K bytes (10 blocks)
　　[Memory]
　　Total Memory = 103528 bytes [0.073% of the Shared Pool]
　　... Fixed Memory = 5132 bytes [0.004% of the Shared Pool]
　　....... Cache Mgr = 108 bytes
　　....... Memory Mgr = 124 bytes
　　....... Bloom Fltr = 2K bytes
　　....... State Objs = 2852 bytes
　　... Dynamic Memory = 98396 bytes [0.069% of the Shared Pool]
　　....... Overhead = 65628 bytes
　　........... Hash Table = 32K bytes (4K buckets)
　　........... Chunk Ptrs = 12K bytes (3K slots)
　　........... Chunk Maps = 12K bytes
　　........... Miscellaneous = 8284 bytes
　　....... Cache Memory = 32K bytes (32 blocks)
　　........... Unused Memory = 30 blocks
　　........... Used Memory = 2 blocks
　　............... Dependencies = 1 blocks (1 count)
　　............... Results = 1 blocks

　　设置结果缓存模式为FORCE是怎样影响SQL查询结果缓存的当前内容的呢?Listing 1.2中的代码做了描述：

• 　　首先激活结果缓存的FORCE模式，然后将结果缓存的规模设置为相对较大的20MB，并允许单独结果缓存的最大规模为这个数值的一半(10MB)。
• 　　接下来，使用一个简单的SQL查询从可支付帐户测试数据中的AP.VENDORS表捕捉所有零售商的名称。因为这个查询不包括+NO_RESULT_CACHE优化器指示，所以这个结果集将会立即被缓存。
• 　　然后用一个SQL查询捕捉可支付帐户(AP)测试数据的一个更加复杂的摘要级别表示。因为结果记录集合并了+NO_RESULT_CACHE优化器指示，所以这个结果集不会被缓存。
• 　　最后一步是对这两个查询使用一个EXPLAIN PLAN来看对任何将来类似产生的结果集的影响。它还重新创建了结果缓存内存的详细报表来看看是否有什么改变了：

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 = 20M bytes (20K blocks)
　　Maximum Result Size = 10M bytes (10K blocks)
　　[Memory]
　　Total Memory = 103528 bytes [0.073% of the Shared Pool]
　　... Fixed Memory = 5132 bytes [0.004% of the Shared Pool]
　　....... Cache Mgr = 108 bytes
　　....... Memory Mgr = 124 bytes
　　....... Bloom Fltr = 2K bytes
　　....... State Objs = 2852 bytes
　　... Dynamic Memory = 98396 bytes [0.069% of the Shared Pool]
　　....... Overhead = 65628 bytes
　　........... Hash Table = 32K bytes (4K buckets)
　　........... Chunk Ptrs = 12K bytes (3K slots)
　　........... Chunk Maps = 12K bytes
　　........... Miscellaneous = 8284 bytes
　　....... Cache Memory = 32K bytes (32 blocks)
　　........... Unused Memory = 24 blocks
　　........... Used Memory = 8 blocks
　　............... Dependencies = 2 blocks (2 count)
　　............... Results = 6 blocks
　　................... SQL = 6 blocks (2 count)
　　EXPLAIN PLAN FOR
　　SELECT /*SQRC_1.2*/
　　vendor_id
　　,name
　　FROM ap.vendors
　　;
　　SELECT *
　　FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
　　----------------------------------------------------------------------------------------------------------------------------
　　Plan hash value: 2620802014
　　-------------------------------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　-------------------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 164 | 3772 | 3 (0)| 00:00:01 |
　　| 1 | RESULT CACHE | 89gqh0j9248q8d0w79w0fcwhw2 | | | | |
　　| 2 | TABLE ACCESS FULL| VENDORS | 164 | 3772 | 3 (0)| 00:00:01 |
　　-------------------------------------------------------------------------------------------------
　　Result Cache Information (identified by operation id):
　　------------------------------------------------------
　　1 - column-count=2; dependencies=(AP.VENDORS); name="SELECT /*SQRC_1.2*/
　　vendor_id
　　,name
　　FROM ap.vendors
　　SQL> EXPLAIN PLAN FOR
　　SELECT /*+NO_RESULT_CACHE SQRC_1.3*/
　　I.customer_id
　　,C.cust_last_name || ', ' || C.cust_first_name AS customer_fullname
　　,SUM(ID.extended_amt) total_detail
　　FROM
　　ap.vendors V
　　,ap.invoices I
　　,ap.invoice_items ID
　　,oe.customers C
　　,oe.product_information P
　　WHERE ID.invoice_id = I.invoice_id
　　AND I.vendor_id = V.vendor_id
　　AND I.customer_id = C.customer_id
　　AND ID.product_id = P.product_id
　　AND I.active_ind = 'Y'
　　GROUP BY
　　I.customer_id
　　,C.cust_last_name || ', ' || C.cust_first_name
　　ORDER BY
　　I.customer_id
　　,C.cust_last_name || ', ' || C.cust_first_name
　　;
　　SELECT *
　　FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
　　2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
　　Explained.
　　SQL> 2
　　PLAN_TABLE_OUTPUT
　　----------------------------------------------------------------------------------------------------------------------------
　　Plan hash value: 500053926
　　----------------------------------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　----------------------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 956 | 34416 | 11 (19)| 00:00:01 |
　　| 1 | SORT GROUP BY | | 956 | 34416 | 11 (19)| 00:00:01 |
　　|* 2 | HASH JOIN | | 956 | 34416 | 10 (10)| 00:00:01 |
　　| 3 | NESTED LOOPS | | | | | |
　　| 4 | NESTED LOOPS | | 25 | 700 | 6 (0)| 00:00:01 |
　　| 5 | TABLE ACCESS FULL | CUSTOMERS | 319 | 6061 | 5 (0)| 00:00:01 |
　　|* 6 | INDEX RANGE SCAN | INVOICES_CUST_IDX | 25 | | 0 (0)| 00:00:01 |
　　|* 7 | TABLE ACCESS BY INDEX ROWID| INVOICES | 1 | 9 | 1 (0)| 00:00:01 |
　　| 8 | TABLE ACCESS FULL | INVOICE_ITEMS | 975 | 7800 | 3 (0)| 00:00:01 |
　　----------------------------------------------------------------------------------------------------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　2 - access("ID"."INVOICE_ID"="I"."INVOICE_ID")
　　6 - access("I"."CUSTOMER_ID"="C"."CUSTOMER_ID")
　　filter("I"."CUSTOMER_ID">0)
　　7 - filter("I"."ACTIVE_IND"='Y')

使用DBMS_RESULT_CACHE控制SQL查询结果集缓存

　　Oracle 11g还提供了DBMS_RESULT_CACHE包来查询SQL结果缓存内容的状态和适当地控制SQL结果缓存内容。这是关于它的功能的一个简要摘要：

　　表 1-1. DBMS_RESULT_CACHE功能和存储过程

表 1-1. DBMS_RESULT_CACHE功能和存储过程

功能 / 存储过程

描述

STATUS

返回结果缓存的当前状态。值包括：

ENABLED：结果缓存是激活的。

DISABLED：结果缓存是不可用的。

BYPASSED：结果缓存暂时不可用。

SYNC： 结果缓存是可用的，但是目前正与其他RAC节点重新同步。

MEMORY_REPORT

列出结果缓存内存利用的一个概要（默认）或详细的报表。

FLUSH

推出整个结果缓存的内容。

INVALIDATE

使结果缓存中一个特定对象的缓存结果无效。

INVALIDATE_OBJECT

根据缓存ID使一特定结果缓存无效。

　　Listing 1.3 显示了一些关于怎样使用这些打包的存储过程和函数的额外例子。

　　结果缓存元数据

　　四个动态视图提供了关于现有结果缓存内容、内存利用和结果缓存所依赖的数据库对象的信息：

表1-2. SQL结果缓存元数据

视图

描述

V$RESULT_CACHE_STATISTICS

罗列出各种缓存设置和内存使用统计数据。

V$RESULT_CACHE_MEMORY

列出所有的内存块和相应的统计信息。

V$RESULT_CACHE_OBJECTS

列出所有的对象（缓存的结果和依赖的对象）和它们的属性。

V$RESULT_CACHE_DEPENDENCY

列出缓存的结果和依赖对象间的依赖详情。

　　Listing 1.4是用于这篇文章单个示例(V$)的几个查询;扩展这些查询成为用于Real Application Clusters数据库的全局资源视图(GV$)是比较简单的工作。在Listing 1.5中，重新生成了对V$RESULT_CACHE_OBJECTS视图查询的结果，以此来描述它为缓存的结果集包含了什么元数据。

　　接下来的步骤

　　Oracle 11g新的结果缓存功能为数据库管理员提供了几个简单但很好的工具来捕捉、保存、监控和管理SQL查询结果缓存，加速了应用程序对相应持久数据的访问速度。在这个系列文章中的最后一篇中，我将介绍怎样在PL/SQL函数中利用结果集缓存，还有怎样在应用程序服务器级别来缓存结果集以便加快客户端应用程序对频繁访问数据的访问。

 

参考：

查询结果集cache 谷歌