- 1.1 概述
- 1.2 设置SQL查询结果高速缓存
- 1.3 管理SQL查询结果高速缓存
- 1.4 使用Result_Cache 提示
- 1.5 使用DBMS_RESULT_CACHE 程序包
- 1.6 查看SQL结果高速缓存字典信息
- 1.7 SQL 查询结果高速缓存：注意事项
- 1.8 OCI 客户机查询高速缓存
- 1.9 使用客户端查询高速缓存
- 1.10 PL/SQL 函数高速缓存
- 1.12 PL/SQL 函数高速缓存：注意事项
- 2.1 Query Result Cache 示例
- 2.2 PL/SQL Function Result Cache 示例(用于存储PL/SQL函数的结果集)
- 2.3 DBMS_RESULT_CACHE 包示例
一．Result Set Caching 说明
Oracle 官网的说明，参考：
7.6 Managing the Server and ClientResult Caches
http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#BGBCABED
1.1 概述
SQL 查询结果高速缓存可在数据库内存中对查询结果集和查询碎片启用显式高速缓存。存储在共享池（Share Pool）中的专用内存缓冲区可用于存储和检索高速缓存的结果。对查询访问的数据库对象中的数据进行修改后，存储在该高速缓存中的查询结果将失效。
虽然SQL 查询高速缓存可用于任何查询，但最适用于需要访问大量行却仅返回其中一少部分的语句。数据仓库应用程序大多属于这种情况。
注：
(1) RAC 配置中的每个节点都有一个专用的结果高速缓存。一个实例的高速缓存结果不能供另一个实例使用。但是，失效会对多个实例产生影响。要处理RAC 实例之间与SQL 查询结果高速缓存相关的所有同步操作，需对每个实例使用专门的RCBG 进程。
(2) 通过并行查询，可对整个结果进行高速缓存（在RAC 中，是在查询协调程序实例上执行高速缓存的），但单个并行查询进程无法使用高速缓存。
简言之：
• 高速缓存查询或查询块的结果以供将来重用。
• 可跨语句和会话使用高速缓存，除非该高速缓存已过时。
• 优点：
– 可扩展性
– 降低内存使用量
• 适用的语句：
– 访问多行
– 返回少数行
1.2 设置SQL查询结果高速缓存
查询优化程序根据初始化参数文件中RESULT_CACHE_MODE 参数的设置管理结果高速缓存机制。
可以使用此参数确定优化程序是否将查询结果自动发送到结果高速缓存中。可以在系统和会话级别设置RESULT_CACHE_MODE 参数。
参数值可以是AUTO、MANUAL 和FORCE：
(1) 设置为AUTO 时，优化程序将根据重复的执行操作确定将哪些结果存储在高速缓存中。
(2) 设置为MANUAL（默认值）时，必须使用RESULT_CACHE 提示指定在高速缓存中存储特定结果。
(3) 设置为FORCE 时，所有结果都将存储在高速缓存中。
注：对于AUTO 和FORCE 设置，如果语句中包含[NO_]RESULT_CACHE 提示，则该提示优先于参数设置。
1.3 管理SQL查询结果高速缓存
可以改变初始化参数文件中的多种参数设置，以管理数据库的SQL 查询结果高速缓存。
默认情况下，数据库会为SGA 中共享池(Share Pool)内的结果高速缓存分配内存。分配给结果高速缓存的内存大小取决于SGA的内存大小以及内存管理系统。可以通过设置RESULT_CACHE_MAX_SIZE参数来更改分配给结果高速缓存的内存。如果将结果高速缓存的值设为0，则会禁用此结果高速缓存。此参数的值将四舍五入到不超过指定值的32 KB的最大倍数。如果四舍五入得到的值是0，则会禁用该功能。
使用RESULT_CACHE_MAX_RESULT参数可以指定任一结果可使用的最大高速缓存量。默认值为5%，但可指定1 到100 之间的任一百分比值。可在系统和会话级别上实施此参数。
使用RESULT_CACHE_REMOTE_EXPIRATION参数可以指定依赖于远程数据库对象的结果保持有效的时间（以分钟为单位）。默认值为0，表示不会高速缓存使用远程对象的结果。
将此参数设置为非零值可能会生成过时的信息：例如，当结果使用的远程表在远程数据库上发生了更改时。
使用以下初始化参数进行管理：
1.RESULT_CACHE_MAX_SIZE
– 此参数设置分配给结果高速缓存的内存。
– 如果将其值设为0，则会禁用结果高速缓存。
– 默认值取决于其它内存设置（memory_target的0.25% 或sga_target 的0.5% 或shared_pool_size 的1%）
– 不能大于共享池的75%
2.RESULT_CACHE_MAX_RESULT
– 设置单个结果的最大高速缓存
– 默认值为5%
3.RESULT_CACHE_REMOTE_EXPIRATION
– 根据远程数据库对象设置高速缓存结果的过期时间
– 默认值为0
1.4 使用Result_Cache 提示
如果要使用查询结果高速缓存并将RESULT_CACHE_MODE初始化参数设置为MANUAL，则必须在查询中显式指定RESULT_CACHE 提示。这会在查询的执行计划中引入ResultCache运算符。执行查询时，ResultCache 运算符将查找结果高速缓存，以检查该查询结果是否存在于高速缓存中。如果存在，则直接从高速缓存检索该结果。如果高速缓存中不存在该查询结果，则执行查询。结果将以输出形式返回，也存储在结果高速缓存中。
如果将RESULT_CACHE_MODE 初始化参数设置为AUTO 或FORCE，并且不希望将查询结果存储在结果高速缓存中，则必须在查询中使用NO_RESULT_CACHE 提示。例如，如果在初始化参数文件中RESULT_CACHE_MODE的值为FORCE，并且不希望对EMPLOYEES表使用结果高速缓存，则需要使用NO_RESULT_CACHE 提示。
注：应优先于参数设置使用[NO_]RESULT_CACHE提示。
1.5 使用DBMS_RESULT_CACHE 程序包
DBMS_RESULT_CACHE程序包提供了统计信息、信息和运算符，可以管理查询结果高速缓存的内存分配。可以使用DBMS_RESULT_CACHE程序包执行多种操作，如查看高速缓存的状态（OPEN 或CLOSED）、检索有关高速缓存内存使用量的统计信息、刷新高速缓存。
例如，要查看内存分配统计信息，请使用以下SQL 过程：
SQL> set serveroutput on
SQL> executedbms_result_cache.memory_report
DBMS_RESULT_CACHE 程序包用于：
（1） 管理查询结果高速缓存的内存分配
（2） 查看高速缓存的状态：
SELECTDBMS_RESULT_CACHE.STATUS FROM DUAL;
（3）检索有关高速缓存内存使用量的统计信息：
EXECUTEDBMS_RESULT_CACHE.MEMORY_REPORT;
（4） 删除所有现有结果并清空高速缓存：
EXECUTEDBMS_RESULT_CACHE.FLUSH;
（5）使依赖于指定对象的高速缓存结果失效：
EXECDBMS_RESULT_CACHE.INVALIDATE('JFV','MYTAB');
1.6 查看SQL结果高速缓存字典信息
(G)V$RESULT_CACHE_STATISTICS : 列出各种高速缓存设置和内存使用量统计信息
(G)V$RESULT_CACHE_MEMORY : 列出所有内存块和相应的统计信息
(G)V$RESULT_CACHE_OBJECTS: 列出所有对象（高速缓存结果和依赖性）及其属性
(G)V$RESULT_CACHE_DEPENDENCY: 列出高速缓存结果之间的依赖性详细信息及依赖性
1.7 SQL 查询结果高速缓存：注意事项
对于用户编写的基于函数的索引中使用的任何函数，必须使用DETERMINISTIC 关键字声明该函数对任何指定的输入参数值集始终返回相同的输出值。
(1) 只有在高速缓存处于未使用状态下才能运行清除操作，要进行刷新，需要禁用（关闭）高速缓存。
(2) 对于绑定变量，将使用变量值对高速缓存结果进行参数化。只能找到相同变量值的高速缓存结果。也就是说，不同的值或绑定变量名称将导致高速缓存未命中。
注意事项如下：
1.对包含以下内容的查询禁用结果高速缓存：
– 临时表或字典表
– 不确定的PL/SQL 函数
– CURRVAL 和NEXTVAL 序列
– current_date、sysdate 和sys_guid 等SQL 函数
2.远程数据库上的DML/DDL 不会使高速缓存结果过期。
3.可高速缓存闪回查询。
4.结果高速缓存不会自动释放内存。
– 它将不断增长，直到达到最大大小。
– DBMS_RESULT_CACHE.FLUSH 会清除内存。
5.绑定变量
– 将使用变量值对高速缓存结果进行参数化。
– 只能找到相同变量值的高速缓存结果。
6.对于以下情况，不会生成高速缓存结果：
– 查询是基于数据的非当前版本构建的（强制实施读取一致性）
– 当前会话在查询的表中存在未完成的事务处理
1.8 OCI 客户机查询高速缓存
在Oracle Database11g 中，可以使用Oracle 调用接口(OCI) 客户机查询高速缓存对客户机内存中的查询结果集启用高速缓存。
高速缓存结果集数据以透明方式与服务器端上的所有更改保持一致。由于高速缓存中保存有相应信息，因此利用此功能的应用程序会提高查询的性能。此外，使用高速缓存的查询可避免在发送查询和获取结果时在客户机和服务器之间进行往返。处理查询所用的服务器CPU 会减少，从而提高了服务器的可扩展性。
在使用客户端查询高速缓存之前，请确定应用程序是否会受益于此功能。当您的应用程序生成重复的结果集、小型结果集、静态结果集或频繁执行的查询时，客户端高速缓存非常有用。
客户机和服务器结果高速缓存是独立的，可独立启用或禁用它们。
注：可以使用client_result_cache_stats$视图或v$client_result_cache_stats 视图来监视客户机查询高速缓存。
使用客户端缓存优点如下：
(1) 将服务器端查询高速缓存扩展到客户端内存
(2) 通过消除客户机与服务器之间的往返可以提高性能
(3) 利用客户端内存
(4) 通过节省服务器CPU 资源提高服务器的可扩展性
(5) 服务器上的结果集发生更改时会自动刷新结果高速缓存
(6) 尤其适用于查找表
1.9 使用客户端查询高速缓存
可通过以下方式使用客户端查询高速缓存：
• 设置初始化参数
– CLIENT_RESULT_CACHE_SIZE
– CLIENT_RESULT_CACHE_LAG
• 使用客户机配置文件
– OCI_RESULT_CACHE_MAX_SIZE
– OCI_RESULT_CACHE_MAX_RSET_SIZE
– OCI_RESULT_CACHE_MAX_RSET_ROWS
根据以下项使用客户机结果高速缓存：
– 表结果高速缓存模式
– SQL 语句中的RESULTCACHE 提示
CLIENT_RESULT_CACHE_SIZE ：非零值表示启用客户机结果高速缓存。这是客户机每个进程的结果集高速缓存的最大大小（以字节表示）。所有OCI 客户机进程都可达到此最大值，可以使用OCI_RESULT_CACHE_MAX_SIZE参数覆盖此值。
CLIENT_RESULT_CACHE_LAG ：自客户机与服务器之间的最后一次往返后的最长时间（以毫秒为单位），在此之前OCI 客户机查询执行一次往返以获得与在客户机上高速缓存的查询相关的所有数据库更改。
客户机配置文件是可选的，并且可覆盖服务器初始化参数文件中的高速缓存参数集。参数值可以包含于sqlnet.ora 文件中。
OCI_RESULT_CACHE_MAX_RSET_SIZE/ROWS表示每个进程的查询高速缓存中所有结果集的最大大小（以字节/行表示）。OCI 应用程序可使用应用程序提示来强制执行结果高速缓存存储。这将覆盖ALTERTABLE/ALTER VIEW 的部署时间设置。
该应用程序提示可以是：
(1) SQL 提示/*+ result_cache */, and /*+no_result_cache */
(2) OCIStmtExecute() 模式。这些内容将覆盖SQL 提示。
注：要使用此功能，该应用程序必须与版本11.1 或更高版本的客户机库重新链接，并连接到版本11.1 或更高版本的服务器。
1.10 PL/SQL 函数高速缓存
从Oracle Database11g 开始，可以使用PL/SQL 跨段函数结果高速缓存机制。此高速缓存机制提供了一种语言受到支持且由系统管理的方法，可将PL/SQL 函数的结果存储在共享全局区域(SGA) 中，运行应用程序的每个会话均可使用这种方法。该高速缓存机制不仅高效而且易于使用，可以减轻设计和开发自己的高速缓存和高速缓存管理策略的工作。
Oracle Database11g可以标记PL/SQL 函数，指示应高速缓存其结果，这样在下一次访问调用相同参数值时，可以进行查找，而无需重新计算。此函数结果高速缓存可节省大量空间和时间。这是通过将输入参数用作查找关键字以透明方式完成的。高速缓存是针对整个实例的，因此所有调用该函数的不同会话都可从中受益。如果更改了给定参数集的结果，则可以通过构造使高速缓存条目失效，以便在下一次访问时正确地对其进行重新计算。如果函数返回的值是根据从方案级表中选择的数据计算出来的，则此功能尤其有用。
对于此类用法，失效构造十分简单，并且是声明性的。可在PL/SQL 函数的源文本中包括语法，以请求高速缓存函数结果并确保其正确性，并请求当表列表中出现相应DML 时清除此高速缓存。如果调用高速缓存结果的函数时发现高速缓存中包含结果值，则不执行函数主体，而是立即返回高速缓存的值。
1.11 使用PL/SQL函数高速缓存
PL/SQL 函数高速缓存使用：
(1) 在程序包的函数声明部分或函数定义中包括RESULT_CACHE选项。
(2) 可以选择包括RELIES_ON 子句，以指定函数结果依赖的任何表或视图。
如：
CREATE OR REPLACE FUNCTION productName
(prod_id NUMBER, lang_id VARCHAR2)
RETURN NVARCHAR2
RESULT_CACHE RELIES_ON (product_descriptions)
IS
result VARCHAR2(50);
BEGIN
SELECT translated_name INTO result
FROM product_descriptions
WHERE product_id = prod_id AND language_id= lang_id;
RETURN result;
END;
使用说明：
（1）如果函数的执行导致了未处理的异常错误，该异常结果不会存储在高速缓存中。
（2）在以下情况下执行高速缓存结果的函数主体：
- 此数据库实例上的会话第一次使用这些参数值调用该函数。这些参数值的高速缓存结果无效。
- 当在函数定义的RELIES_ON 子句中指定的任何数据库对象发生更改时，高速缓存的结果就会失效。
- 这些参数值的高速缓存结果已过时。如果系统需要更多内存，它可能会放弃最早的高速缓存值。
- 该函数绕过高速缓存。
（3）该函数不应有任何负作用。
（4）该函数不应依赖特定于会话的设置。
（5）该函数不应依赖特定于会话的应用程序上下文。
1.12 PL/SQL 函数高速缓存：注意事项
PL/SQL 函数高速缓存不适用于以下情况：
(1) 函数是在具有调用者权限的模块中或匿名块中定义的。
(2) 函数是用管道传输的表函数。
(3) 该函数包含OUT 或IN OUT 参数。
(4) 该函数包含下列类型的IN 参数：BLOB、CLOB、NCLOB、REF CURSOR、集合、对象或记录。
(5) 该函数的返回类型为：BLOB、CLOB、NCLOB、REFCURSOR、对象、记录或包含上述不受支持的某个返回类型的集合。
二．Result Cache 示例
2.1 Query Result Cache 示例
SQL> conn dave/dave;
Connected.
SQL> create table dave as select * fromall_objects;
Table created.
SQL> set timing on
SQL> select count(1) from dave;
COUNT(1)
----------
71937
Elapsed: 00:00:00.32
SQL> select /*+result_cache */ count(1)from dave;
COUNT(1)
----------
71937
Elapsed: 00:00:00.24
SQL> select /*+result_cache*/ count(1) from dave;
COUNT(1)
----------
71937
Elapsed: 00:00:00.02
--第二次查询的时间明显减少，这里数据量不大，如果数据量大，这里效果更明显。
SQL> select count(1) from dave;
COUNT(1)
----------
71937
Elapsed: 00:00:00.20
SQL>
--这里控制的参数result_cache_mode我们使用的是默认值： manual：
SQL> set lin 160
SQL> show parameter result_cache_mode
NAME TYPE VALUE
----------------------------------------------- ------------------------------
result_cache_mode string MANUAL
--改成FORCE：
SQL> ALTER SESSION SETRESULT_CACHE_MODE=FORCE;
Session altered.
Elapsed: 00:00:00.01
SQL> select count(1) from dave;
COUNT(1)
----------
71937
Elapsed: 00:00:00.03
--这里默认就使用了result cache。
--使用hint 禁用resultcache：
SQL> select/*+no_result_cache*/ count(1) from dave;
COUNT(1)
----------
71937
Elapsed: 00:00:00.16
--查看执行计划：
SQL> explain plan for select count(1)from dave;
Explained.
Elapsed: 00:00:00.45
SQL> set lin 130
set pagesize 200
select * from table(dbms_xplan.display);
SQL> SQL>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1718719579
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 287 (1)| 00:00:04 |
| 1 | RESULT CACHE |1tdd2tzbg0xtb6v90uujzmrug1 | | | |
| 2| SORT AGGREGATE | | 1 | | |
| 3| TABLE ACCESS FULL| DAVE | 82433 | 287 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified byoperation id):
------------------------------------------------------
1- column-count=1; dependencies=(DAVE.DAVE); attributes=(single-row);name="select count(1) from dave"
Note
-----
-dynamic sampling used for this statement (level=2)
19 rows selected.
Elapsed: 00:00:00.36
SQL>
2.2 PL/SQL Function Result Cache 示例(用于存储PL/SQL函数的结果集)
2.2.1 RESULT_CACHE 选项 示例
启用函数的Result Cache，需要在函数中执行RESULT_CACHE选项。还有一个可选的选项：RELIES_ON.
--创建表并插入数据：
CREATE TABLE res_cache_test_tab (
id NUMBER,
value NUMBER
);
BEGIN
FORi IN 1 .. 10 LOOP
INSERT INTO res_cache_test_tab VALUES (i, i*10);
ENDLOOP;
COMMIT;
END;
/
--创建函数,注意RESULT_CACHE选项:
CREATE OR REPLACE FUNCTIONget_value(p_in IN NUMBER)
RETURNNUMBER
RESULT_CACHE
AS
l_valueres_cache_test_tab.value%TYPE;
BEGIN
SELECTvalue
INTO l_value
FROM res_cache_test_tab
WHERE id = p_in;
-- Pausefor 1 second.
DBMS_LOCK.sleep(1);
RETURNl_value;
END get_value;
/
--使用过程验证2次查询的时间效率:
CREATE OR REPLACE PROCEDURE run_test AS
l_start NUMBER;
l_loops NUMBER := 10;
l_value res_cache_test_tab.value%TYPE;
BEGIN
l_start := DBMS_UTILITY.get_time;
FORi IN 1 .. l_loops LOOP
l_value := get_value(i);
ENDLOOP;
DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start)|| ' hsecs');
l_start := DBMS_UTILITY.get_time;
FORi IN 1 .. l_loops LOOP
l_value := get_value(i);
ENDLOOP;
DBMS_OUTPUT.put_line('SecondLoop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END run_test;
/
--执行过程:
SET SERVEROUTPUT ON
SQL> EXEC run_test;
First Loop: 1018 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
SQL>
--再次执行时，时间就一致了，因为都使用了result cache：
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
2.2.2 RELIES_ON 选项 示例
RELIES_ON 选项是个可选选项，用来控制数据的一致性。其用来指定函数依赖的表或者视图，当关联的表和视图失效后，result cache 中的值也会失效。
在Oracle 11g之前，需要显示的指定这个选项。而在Oracle 11.2 中默认就会跟踪依赖的对象。
--这里我们测试修改了数据，Result Cache失效，到时查询时间变长：
UPDATE res_cache_test_tab
SET value = value * 10;
COMMIT;
SQL> EXEC run_test;
First Loop: 1006 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
SQL>
2.3 DBMS_RESULT_CACHE 包示例
2.3.1 STATUS
显示当前result cache 状态：
SQL> SELECT DBMS_RESULT_CACHE.statusFROM dual;
STATUS
-----------------------------------------------------
ENABLED
1 row selected.
SQL>
2.3.2 MEMORY_REPORT
显示result cache 内存的使用情况
SQL> EXECDBMS_RESULT_CACHE.memory_report(detailed => true);
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 = 2016K bytes (2016 blocks)
Maximum Result Size = 100K bytes (100blocks)
[Memory]
Total Memory = 162184 bytes [0.072% of theShared Pool]
... Fixed Memory = 5352 bytes [0.002% ofthe Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 156832 bytes [0.069%of the Shared Pool]
....... Overhead = 124064 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 21664 bytes
....... Cache Memory = 32K bytes (32blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 32 blocks
............... Dependencies = 3 blocks (3count)
............... Results = 29 blocks
................... SQL = 11 blocks (11 count)
................... PLSQL = 10 blocks (10 count)
................... Invalid = 8 blocks (8count)
PL/SQL procedure successfully completed.
2.3.3 INVALIDATE
让指定对象关联的所有result cache 失效。 过程中指定用户名和对象名，或者指定OBJECT_ID.
SQL> EXEC DBMS_RESULT_CACHE.invalidate('DAVE','GET_VALUE');
PL/SQL procedure successfully completed.
2.3.4 INVALIDATE_OBJECT
从V$RESULT_CACHE_OBJECTS视图中查询ID 或者CACHE_ID，指定ID后让指定对象关联的result cache 失效。
SQL> exec DBMS_RESULT_CACHE.invalidate_object(31);
PL/SQL procedure successfully completed.
SQL>
2.3.5 FLUSH
FLUSH 用户移除resultcache中的所有对象，从而释放内存。
SQL> execDBMS_RESULT_CACHE.flush;
PL/SQL procedure successfully completed.
SQL>
释放之后V$RESULT_CACHE_OBJECTS 就会显示为空。
---------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
Skype: tianlesoftware
QQ: tianlesoftware@gmail.com
Email: tianlesoftware@gmail.com
Blog: http://blog.csdn.net/tianlesoftware
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware