SQL 结果缓存

访问内存比访问硬盘快得多，在接下来几年中，除非硬盘体系结构有重大改进，不然这一情况很可能会持续。缓存这一将数据存储于内存而非硬盘中的过程由此应运而生。缓存是 Oracle 数据库体系结构的一个基本原理，用户从缓存而非数据库所驻留的磁盘中获取数据。

在相对较小的含静态数据的表中，如 STATES、PRODUCT_CODES 等参考表，缓存的优势异乎寻常的明显。但是，假设有一个存储公司客户的大型表 CUSTOMERS。列表相对静态但不完全是，在向列表中添加或从列表中删除客户时，表极少更改。

缓存在这一情况中也有些许用武之地。但如果您要缓存该表，如何在发生变化时确保获得正确的数据？

Oracle 数据库 11 g 可以解决这一问题：使用 QL 结果缓存。假设查询如下。运行它以获取执行统计信息和响应时间：

SQL> set autot on explain stat select state_code, count(*), min(times_purchased), avg(times_purchased) from customers group by state_code ST COUNT(*) MIN(TIMES_PURCHASED) AVG(TIMES_PURCHASED) -- ---------- -------------------- -------------------- NJ 1 15 15 NY 994898 0 15.0052086 CT 5099 0 14.9466562 MO 1 25 25 FL 1 3 3 5 rows selected. Elapsed: 00:00:02.57 Execution Plan ---------------------------------------------------------- Plan hash value: 1577413243 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 30 | 1846 (25)| 00:00:23 | | 1 | HASH GROUP BY | | 5 | 30 | 1846 (25)| 00:00:23 | | 2 | TABLE ACCESS FULL| CUSTOMERS | 1000K| 5859K| 1495 (7)| 00:00:18 | -------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 5136 consistent gets 5128 physical reads 0 redo size 760 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed 几点注意事项：

解释计划说明执行了全表扫描。

共有 5,136 次连续的获取（逻辑 I/O）。

执行时间 2.57 秒。

因为表几乎没变，您可以使用提示来存储要缓存到内存中的查询结果： select /*+ result_cache */ state_code, count(*), min(times_purchased), avg(times_purchased) from customers group by state_code 除提示外，查询与第一个相同。结果（第二次执行该查询）： ST COUNT(*) MIN(TIMES_PURCHASED) AVG(TIMES_PURCHASED) -- ---------- -------------------- -------------------- NJ 1 15 15 NY 994898 0 15.0052086 CT 5099 0 14.9466562 MO 1 25 25 FL 1 3 3 5 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1577413243 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 30 | 1846 (25)| 00:00:23 | | 1 | RESULT CACHE | gk69saf6h3ujx | | | | | | | | 525twvvsnaytd | | | | | | 2 | HASH GROUP BY | | 5 | 30 | 1846 (25)| 00:00:23 | | 3 | TABLE ACCESS FULL| CUSTOMERS | 1000K| 5859K| 1495 (7)| 00:00:18 | -------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=4; dependencies=(ARUP.CUSTOMERS); parameters=(nls); name="select /*+ result_cache */ state_code, count(*), min(times_purchased), avg(times_purchased) from customers group by state_c" Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 760 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed 注意与第一次的不同之处。

响应时间现在为 0.01 秒，而不是先前的将近 3 秒。

连续的获取为 0，这一查询没有执行逻辑 I/O。（事实上，首次运行带有提示的查询时，I/O 将保持不变，因为数据库需要执行 I/O 来构建缓存。后续的调用将从缓存中供应数据，从而消除了 I/O。）

这一解释计划将 RESULT CACHE 视为一个操作。

解释计划的注意事项指明执行的缓存类型以及缓存结果。

在时间上的节约是显著的：从 3 秒到几乎为 0！这是因为第二次查询使用了缓存，结果直接来自数据库内存（结果缓存）而不是执行查询。

SQL 结果缓存是 SGA 中的另一个缓存，与缓冲区缓存或程序全局区一样。当您执行带有 result_cache 提示的查询时，Oracle 执行该操作的过程与其他操作一样，只是结果存储在 SQL 结果缓存中。接下来对同一查询的调用将不访问表，而是从缓存中获取结果。缓存大小由几个初始化参数确定：

result_cache_max_size 结果缓存上限（例如，5M 上限）。如果将它设为 0，将完全关闭结果缓存。 result_cache_max_result 指定任一结果可使用的 result_cache_max_size 百分比 result_cache_mode 如设置为 FORCE，如果缓存可以容纳所有查询，就会缓存它们。默认值为 MANUAL 表示只缓存带有提示的查询。 result_cache_remote_expiration 指定访问远程对象的缓存结果保持有效的时间（以分钟为单位）。默认为 0。 现在，有一个逻辑问题：当表行更改时，将发生什么情况？查询将获取新值还是旧值？好，让我们来看一看。从另一个 SQL*Plus 会话更新表中的某一行： SQL> update customers set times_purchased = 4 2 where state_code = 'FL'; 1 row updated. 但是不要提交。在首次运行查询的原窗口中，再运行一次。使用的是仍是缓存结果，因为更改没有提交。运行查询的会话仍然查看最新版本的数据，缓存仍旧有效。

现在，从您进行更新操作的会话中，发出提交指令，然后运行查询。

ST COUNT(*) MIN(TIMES_PURCHASED) AVG(TIMES_PURCHASED) -- ---------- -------------------- -------------------- NJ 1 15 15 NY 994898 0 15.0052086 CT 5099 0 14.9466562 MO 1 25 25 FL 1 4 4 注意 FL 的数据自动更新为 4。底层表的更改致使缓存无效，因而在下一次查询它时将动态更新。无论您是否使用 SQL 结果缓存，都能保证获得正确的结果。

差异与物化视图

熟悉物化视图 (MV) 的人可能想这一功能与 MV 的功能有什么区别。答案是：有很多区别。表面上他们很相似，都是用某种方法保存结果，从保存的数据集中提供答案，但他们的相似之处也仅限于此了。MV 将数据保存在数据库存储中，而 SQL 结果缓存位于内存中。它们不使用更多的磁盘空间，当数据库实例关闭后，它们会消失，或者 result_cache 中的磁盘空间将耗尽。

MV 也是静态的，当底层表中的数据更改时，MV 并不知道。除非您刷新 MV，否则在您将 query_rewrite_integrity 设为 stale_tolerated 的情况下，用户可能获得的是旧数据，或者用户需要针对底层表重新运行基本查询，而这将花费更多的时间。使用 SQL 结果缓存，您不需要显式刷新缓存；下一次运行查询时，缓存将自动刷新。

MV 提供了一个更为复杂的重写算法。首次对结果进行缓存后，只有重新运行同一查询或查询片段时才会重用缓存的结果（且底层数据未更改）。受益于对 MV 进行查询重写的查询仍可从物化视图上卷数据，联结回表或其他物化视图，并应用其他谓词，这是数据仓库环境中很重要的一个特点。

因此，MV 和 SQL 结果缓存是不可比或不可互换的，它们各具千秋。

您也能在子查询中使用 SQL 结果缓存。请看以下查询：

select prod_subcategory, revenue from ( select /*+ result_cache */ p.prod_category, p.prod_subcategory, sum(s.amount_sold) revenue from products p, sales s where s.prod_id = p.prod_id and s.time_id between to_date('01-jan-1990','dd-mon-yyyy') and to_date('31-dec-2007','dd-mon-yyyy') group by rollup(p.prod_category, p.prod_subcategory) where prod_category = 'software/other' 在上面的查询中，缓存发生在内联视图的子查询中。因此，只要内联查询保持不变，外部查询就可以更改且可使用缓存。

要检查有多少内存用于数据库中的 SQL 结果缓存，您可以使用提供的程序包 dbms_result_cache，如下所示：

SQL> set serveroutput on size 999999 SQL> execute 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 = 2560K bytes (2560 blocks) Maximum Result Size = 128K bytes (128 blocks) [Memory] Total Memory = 126736 bytes [0.041% of the Shared Pool] ... Fixed Memory = 5132 bytes [0.002% of the Shared Pool] ... Dynamic Memory = 121604 bytes [0.040% of the Shared Pool] ....... Overhead = 88836 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 21 blocks ........... Used Memory = 11 blocks ............... Dependencies = 4 blocks (4 count) ............... Results = 7 blocks ................... SQL = 5 blocks (4 count) ................... Invalid = 2 blocks (2 count) PL/SQL procedure successfully completed. 如果您因故想清空缓存（包括结果缓存和功能缓存，如下描述），可以使用： begin dbms_result_cache.flush; 执行以上命令后，通过 result_cache 提示对 CUSTOMERS 运行原始查询后，您将再次看到查询需要 3 秒完成。

当然，第一次运行后，结果将再次缓存，后续执行将从结果缓存中获取值，因此执行速度将更快。如果您只想使一个表的缓存无效，而不是整个缓存，可以使用以下命令：

begin dbms_result_cache.invalidate('ARUP','CUSTOMERS'); 有几个数据词典视图列出了 SQL 结果缓存的统计数据：
SQL 结果缓存使您能够缓存访问大量数据的查询的结果。当底层表更改时，如果您没有干预或另外编写代码，缓存将自动失效。

PL/SQL 功能结果缓存

假设您使用一个 PL/SQL 函数（而不是 SQL 查询）来返回值。这种使用函数返回值来构造代码模块的做法很常见。假设有两个表：CUSTOMERS 存储所有客户的信息以及 state_code。另一个表 TAX_RATE 存储每个州的税率。要获得适用于客户的税率，您需要在查询中联结表。因此，为简化这一过程，您打算编写如下函数，以接受客户 ID 作为参数，并基于 state_code 返回相应的税率：

create or replace function get_tax_rate p_cust_id customers.cust_id%type return sales_tax_rate.tax_rate%type l_ret sales_tax_rate.tax_rate%type; begin select tax_rate into l_ret from sales_tax_rate t, customers c where c.cust_id = p_cust_id and t.state_code = c.state_code; -- simulate some time consuming -- processing by sleeping for 1 sec dbms_lock.sleep (1); return l_ret; exception when NO_DATA_FOUND then return NULL; when others then raise; 执行几次函数，如下所示。记住要启用计时来记录每次执行使用的时间。 SQL> select get_tax_rate(1) from dual; GET_TAX_RATE(1) --------------- 1 row selected. Elapsed: 00:00:01.23 SQL> select get_tax_rate(1) from dual; GET_TAX_RATE(1) --------------- 1 row selected. Elapsed: 00:00:01.17 每次执行使用的时间几乎都相同。（我特意使用了休眠语句来延迟函数内的处理；否则返回速度太快。）如果您仔细分析代码，将注意到每次调用函数时，几乎都会返回相同的值。客户不经常更改所在的州，并且州的税率也很少变化，因此对于同一个客户，每次执行的税率几乎完全相同。当且仅当州税率更改或客户搬离该州时，税率才会变化。因此，缓存这一函数的结果会怎么样呢？

Oracle 数据库 11 g 可让您完成这一任务。您也可以使用 result_cache 子句实现对函数结果的缓存。但当州实际上更改了税率或客户搬离州时，又该如何呢？这一特性让您可以指定对底层表的依赖，因此那些表中的数据更改将触发失效操作，并随后重建函数中的缓存。以下是添加了结果缓存代码的同一函数（粗体）：

create or replace function get_tax_rate p_cust_id customers.cust_id%type return sales_tax_rate.tax_rate%type
result_cache
relies_on (sales_tax_rate, customers) l_ret sales_tax_rate.tax_rate%type; begin select tax_rate into l_ret from sales_tax_rate t, customers c where c.cust_id = p_cust_id and t.state_code = c.state_code; -- simulate some time consuming -- processing by sleeping for 1 sec dbms_lock.sleep (1); return l_ret; exception when NO_DATA_FOUND then return NULL; when others then raise; 更改后，以同一方式创建和执行函数： SQL> select get_tax_rate(1) from dual; GET_TAX_RATE(1) --------------- 1 row selected. Elapsed: 00:00:01.21 需要 1.21 秒，与上次使用非缓存方式一样，但我们来看一下随后的执行：
SQL> select get_tax_rate(1) from dual; GET_TAX_RATE(1) --------------- 1 row selected. Elapsed: 00:00:00.01 使用的时间只有 0.01 秒！到底是怎么回事？函数执行第一次时，耗时 1.21 秒。但这一次的重要区别是它在执行时缓存了结果。后续的调用不执行该函数，只从缓存中获取结果。因此，它没有休眠函数代码中指定的 1 秒钟。

缓存只针对 customer_id 1。如果针对另一个客户执行函数，又会如何？

SQL> select get_tax_rate(&n) from dual; Enter value for n: 5 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(5) from dual GET_TAX_RATE(5) --------------- 1 row selected. Elapsed: 00:00:01.18 SQL> / Enter value for n: 5 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(5) from dual GET_TAX_RATE(5) --------------- 1 row selected. Elapsed: 00:00:00.00 SQL> / Enter value for n: 6 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(6) from dual GET_TAX_RATE(6) --------------- 1 row selected. Elapsed: 00:00:01.17 您可以看到，首次执行每个参数时，它会缓存结果。后续的调用从缓存中检索值。随着您针对各个客户执行该函数，缓存会逐渐增大。

注意函数代码中的“relies on”子句。它将告知函数缓存依赖哪两个表：customers 和 tax_rate。如果那些表中的数据更改，那么缓存就需要刷新。刷新会自动发生，不需要您干预。如果数据未更改，缓存将继续尽可能快地提供缓存的值。

如果因故要绕过缓存，您可以调用提供的程序包 DBMS_RESULT_CACHE 中的过程：

SQL> exec dbms_result_cache.bypass(true); PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> select get_tax_rate(&n) from dual; Enter value for n: 6 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(6) from dual GET_TAX_RATE(6) --------------- 1 row selected. Elapsed: 00:00:01.18 从执行时间上可以看出，没有使用缓存。

缓存与程序包变量

对 SQL 结果缓存和 PL/SQL 函数缓存，您也可以使用程序包变量（可以是标量数据类型或 PL/SQL 集合）实现在内存中缓存值。应用程序可以访问变量，而不是表行或函数。因为基于内存，它的行为像缓存，那么 SQL 结果缓存添加了什么值？

差别众多。对一个客户（假设 cust_id = 5）执行该函数后，从 另一个 会话对同一客户执行该函数：

SQL> select get_tax_rate(&n) from dual Enter value for n: 5 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(5) from dual GET_TAX_RATE(5) --------------- 1 row selected. Elapsed: 00:00:00.00 注意执行时间，它表明结果源自缓存，而不是通过函数的执行。因此，尽管会话中没有缓存该函数，任一调用它的会话仍然可以从缓存中使用它。
缓存针对数据库实例，而不是会话。这一让所有其他会话能使用一个会话中的缓存的能力极其不同于使用程序包变量在内存中保存值的方法，在这一方法中，变量只能在一个会话中可见。

而且，程序包变量无法感知底层表的更改。当数据更改时，您需要手动刷新数据，否则应用程序将获取陈旧数据。在底层表数据更改时，SQL 结果缓存和 PL/SQL 函数缓存会自动刷新缓存，不需用户介入。

客户端查询结果缓存

假设有这样一个情形：客户端需要通过慢速网络链接调用同一数据。尽管数据库可以立即将结果从缓存发送到客户端，但结果必须通过线路传送到客户端，这就增加了整体执行时间。现在有一些专门的中间件框架（如 Oracle Coherence），用于在 Java、PHP 和 Ruby 中缓存数据，如果有一个在客户端级别缓存数据的通用方法，又将如何呢？

为此，Oracle 数据库 11 g 提供了“客户端查询结果缓存”。所有使用 OCI8 驱动程序的数据库客户端堆栈（C、C++、JDBC-OCI 等）都可以使用这一新特性，使客户端能够在本地缓存 SQL 查询的结果，而不是在服务器上。总言之，客户端查询结果缓存可以提供以下好处：

使应用程序开发人员不用构建由所有会话共享的一致的每一流程的 SQL 结果缓存

通过利用更便宜的客户端内存并在本地缓存每个应用程序的工作集，将服务器端查询缓存扩展到客户端内存，

从而消除到服务器的往返过程，确保更好的性能

通过节约服务器资源，改善服务器可伸缩性

提供透明的缓存管理：内存管理、结果集的并发访问等

透明地维护缓存与服务器端更改的一致性

在 RAC 环境中提供一致性

要使用这一特性，您所要做的就是设置一个初始化参数： CLIENT_RESULT_CACHE_SIZE = 1G 该参数定义客户端缓存为 1GB，这是所有客户端的总缓存大小。（这是一个静态参数，因此您必须重启数据库来设置它。）您可以在每个客户端中设置缓存，在客户端位置的 SQLNET.ORA 文件中指定其他参数：
让我们看一看它的使用方式。这是简单的 Java 代码，它使用 OCI8 驱动程序连接到数据库，然后执行 SQL 语句： select /*+ result_cache */ * from customers 。提示致使语句缓存该结果（其他参数已经设置好)。 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class CacheTest { private String jdbcURL = "jdbc:oracle:oci8:@PRONE3"; private Connection conn = null; public CacheTest( ) throws ClassNotFoundException { Class.forName("oracle.jdbc.driver.OracleDriver"); public static void main(String[] args) throws ClassNotFoundException, SQLException { CacheTest check = new CacheTest(); check.dbconnect(); check.doSomething(); public void dbconnect() throws SQLException { System.out.println("Connecting with URL="+jdbcURL+" as arup/arup"); try { conn = DriverManager.getConnection( jdbcURL, "arup" , "arup"); System.out.println("Connected to Database"); } catch (SQLException sqlEx) { System.out.println(" Error connecting to database : " + sqlEx.toString()); public void doSomething() throws SQLException { Statement stmt = null; ResultSet rset = null; try { stmt = conn.createStatement(); System.out.println("Created Statement object"); rset = stmt.executeQuery("select /*+ result_cache */ * from customers"); System.out.println("Retrieved ResultSet object"); if(rset.next()) System.out.println("Result:"+rset.getString(1)); } catch (SQLException sqlEx) { } finally { try { System.out.println("Closing Statment & ResultSet Objects"); if (rset != null) rset.close(); if (stmt != null) stmt.close(); if (conn != null) { System.out.println("Disconnecting..."); conn.close(); System.out.println("Disconnected from Database"); } catch (Exception e) { } 将文件保存为 CacheTest.java，然后编译代码：
$ORACLE_HOME/jdk/bin/javac CacheTest.java 现在，执行编译后的类： $ORACLE_HOME/jdk/bin/java -classpath .:$ORACLE_HOME/jdbc/lib/ojdbc5.jar CacheTest Connecting with URL=jdbc:oracle:oci8:@PRONE3 as arup/arup Connected to Database Created Statement object Retrieved ResultSet object Result :M Closing Statment & ResultSet Objects Disconnecting... Disconnected from Database 多执行几次。几次执行之后，您可以发现客户端已通过动态视图对值进行了缓存，如下所示： select * from client_result_cache_stats$ select * from v$client_result_cache_stats 客户端查询结果缓存在查找很少改变的表时十分有用。（即使它们更改，也会刷新缓存。）这与 SQL 结果缓存不同，其缓存位于服务器上。由于客户端缓存了结果，因此不需要在客户端与服务器间往返以获取数据 — 这不但节省了网络带宽，还节省了服务器 CPU 周期。有关更多信息，请参阅 Oracle 调用接口编程人员指南 。

数据库驻留连接池

在传统的客户端/服务器体系结构中，用户会话和数据库连接之间是一对一的通信。但在基于 Web 的系统中，情况可能有所不同。

基于 Web 的系统在本质上是“无状态”的 — 当您访问页面时，将建立与数据库的连接，页面下载完成后，将切断与数据库的连接。稍后，当用户再次单击页面时，将建立一个新的连接，目的达到后又将切断连接。这一过程使得没有必要维护大量的同步连接。

建立连接的开销很大，因此连接池是这些应用程序的一个重要要求。在这一模式中，当页面需要数据库访问时，会从池中分配一个已经建立的连接。完成工作后，Web 会话会将连接返回池中。

但传统的客户端或中间层连接池的问题是：

每个池只限于单个中间层节点。

池的大量增长将导致预先分配的数据库服务器过多和数据库服务器内存使用过多。

池中的工作负载分配不均。

为避免这些问题，Oracle 数据库 11 g 提供了一个服务器端池，称为数据库驻留连接池 (DRCP)。DRCP 可用于使用 OCI 驱动程序的所有数据库客户端，包括 C、C++ 和 PHP。
Oracle 数据库 11 g 预先安装有默认的连接池，但是处于关闭状态。要启用它，使用： execute dbms_connection_pool.start_pool; 现在，要连接到池中的连接而不是常规会话，您要做的就是在 TNS 条目中添加一行 (SERVER=POOLED)，如下所示： PRONE3_POOL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prolin3.proligence.com)(PORT = 1521)) (CONNECT_DATA =
(SERVER = POOLED) (SID = PRONE3) 现在，更改代码中的 connect 字符串，在这一代码中它连接到数据库。使用上面“客户端结果缓存”部分中的示例代码： private String jdbcURL = "jdbc:oracle:oci8:@PRONE3_POOL"; 就这样了。现在，您的应用程序将连接到池而不是服务器。如果您使用瘦客户端且使用标准的 JDBC 连接字符串，则可以使用 POOLED 子句： prolin3. proligence.com:1521/PRONE3:POOLED 在上面的描述中，您启用了 Oracle 附带的默认池，选项为默认的。您可以使用提供的 DBMS_CONNECTION_POOL 程序包中的 CONFIGURE_POOL 过程：
DRCP 功能非常重要，因为单个池可在一个普通平台上维持数万同步用户。此外，单个池可以在多个客户端和中间层节点间共享，并且在 RAC 和 Data Guard 环境中，DRCP 和 FAN 事件共同提供了快速的连接故障转移。

返回到“Oracle 数据库 11 g ：面向 DBA 和开发人员的重要特性”主页 Arup Nanda ( arup@proligence.com ) 担任专职 Oracle DBA 已愈 12 年，他拥有 Oracle 数据库技术各个领域的经验，2003 年被 Oracle 杂志授予“年度 DBA”称号。Arup 经常在 Oracle 相关的活动中发表演讲并为 Oracle 相关刊物撰写文章，他还是一位 Oracle ACE 总监 。他与其他人合作编写了四本书，其中包括 《RMAN Recipes for Oracle Database 11g:A Problem Solution Approach》 。