时间：2021-07-01 10:21:17 帮助过：14人阅读
SQL> set autotrace traceonly
SQL> set linesize 300
--使用result_cache提示
SQL> select /*+result_cache*/e.employee_id,e.first_name,e.last_name from employees e;
107 rows selected.
--执行计划中发现生成一个name为4c8bpah3vhjmxd77cmggfzah64的缓存对象
Execution Plan
----------------------------------------------------------
Plan hash value: 3375806929
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2033 | 3 (34)| 00:00:01 |
| 1 | RESULT CACHE | 4c8bpah3vhjmxd77cmggfzah64 | | | | |
| 2 | VIEW | index$_join$_001 | 107 | 2033 | 3 (34)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
| 4 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 2033 | 1 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 107 | 2033 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(ROWID=ROWID)
--缓存信息
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(HR.EMPLOYEES); name="select /*+result_cache*/e.employee_id,e.first_name,e.last_name from employees e"
Statistics
----------------------------------------------------------
78 recursive calls
0 db block gets
151 consistent gets
15 physical reads
0 redo size
4091 bytes sent via SQL*Net to client
601 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
--去掉result_cache提示后重新执行SQL，发现执行计划中无缓存对象
SQL> select e.employee_id,e.first_name,e.last_name from employees e;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3375806929
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2033 | 3 (34)| 00:00:01 |
| 1 | VIEW | index$_join$_001 | 107 | 2033 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
| 3 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 2033 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 107 | 2033 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
4091 bytes sent via SQL*Net to client
601 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
--而此时再检查一下视图v$result_cache_objects，发现cache_id为4c8bpah3vhjmxd77cmggfzah64仍然存在。
SQL> col id format 999
SQL> col type format a20
SQL> col status format a20
SQL> col row_count format 999
SQL> col scan_count format 999
SQL> col build_time format a20
SQL> col cache_id format a30
SQL> select name,id,type,status,row_count,scan_count,cache_id from v$result_cache_objects;
NAME
ID
-----------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------- ----
TYPE STATUS ROW_COUNT SCAN_COUNT CACHE_ID
-------------------- -------------------- --------- ---------- ------------------------------
HR.EMPLOYEES
0
Dependency Published 0 0 HR.EMPLOYEES
select /*+result_cache*/e.employee_id,e.first_name,e.last_name from employees e
1
Result Published 107 3 4c8bpah3vhjmxd77cmggfzah64
--再次使用result_cache提示，发现执行计划又会使用上述缓存对象，而且统计信息中的物理读、逻辑读均为0。
SQL> select /*+result_cache*/e.employee_id,e.first_name,e.last_name from employees e;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3375806929
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2033 | 3 (34)| 00:00:01 |
| 1 | RESULT CACHE | 4c8bpah3vhjmxd77cmggfzah64 | | | | |
| 2 | VIEW | index$_join$_001 | 107 | 2033 | 3 (34)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
| 4 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 2033 | 1 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 107 | 2033 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(ROWID=ROWID)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(HR.EMPLOYEES); name="select /*+result_cache*/e.employee_id,e.first_name,e.last_name from employees e"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
4091 bytes sent via SQL*Net to client
601 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
以上即为result cache的一般用法，也是最常用的方法。
除了使用result_cache提示之外，还有两种使用方法：
1）设置表属性，强制缓存此表的结果集
alter table <table_name> result_cache(mode force);
--设置表result_cache属性为force，然后执行无提示的SQL，发现也能使用4c8bpah3vhjmxd77cmggfzah64对象。
SQL> alter table employees result_cache(mode force);
Table altered.
SQL> select e.employee_id,e.first_name,e.last_name from employees e;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3375806929
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2033 | 3 (34)| 00:00:01 |
| 1 | RESULT CACHE | 4c8bpah3vhjmxd77cmggfzah64 | | | | |
| 2 | VIEW | index$_join$_001 | 107 | 2033 | 3 (34)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
| 4 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 2033 | 1 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 107 | 2033 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(ROWID=ROWID)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(HR.EMPLOYEES); name="select e.employee_id,e.first_name,e.last_name from employees e"
Statistics
----------------------------------------------------------
102 recursive calls
0 db block gets
124 consistent gets
0 physical reads
0 redo size
4091 bytes sent via SQL*Net to client
601 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
107 rows processed
--设置表result_cache属性为default(即设置为原来的默认值)，然后再执行无提示的SQL，发现未使用缓存对象。
SQL> alter table employees result_cache(mode default);
Table altered.
SQL> select e.employee_id,e.first_name,e.last_name from employees e;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3375806929
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2033 | 3 (34)| 00:00:01 |
| 1 | VIEW | index$_join$_001 | 107 | 2033 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
| 3 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 2033 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 107 | 2033 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
Statistics
----------------------------------------------------------
75 recursive calls
0 db block gets
124 consistent gets
0 physical reads
0 redo size
4091 bytes sent via SQL*Net to client
601 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
107 rows processed
--看一下多表关联的情况
--先设置其中一个表employees，发现未启用缓存
SQL> alter table employees result_cache(mode force);
Table altered.
SQL> select e.employee_id,e.first_name,e.last_name from employees e,departments d where e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 2332 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 106 | 2332 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
83 recursive calls
0 db block gets
129 consistent gets
6 physical reads
0 redo size
4020 bytes sent via SQL*Net to client
601 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
106 rows processed
--再将剩下的表的result_cache属性设置为force，发现已经启用cache_id为972x78n6vc4b3fq4bpa3ds60pg的缓存对象
SQL> alter table departments result_cache(mode force);
Table altered.
SQL> select e.employee_id,e.first_name,e.last_name from employees e,departments d where e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 2332 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | 972x78n6vc4b3fq4bpa3ds60pg | | | | |
|* 2 | TABLE ACCESS FULL| EMPLOYEES | 106 | 2332 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."DEPARTMENT_ID" IS NOT NULL)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(HR.EMPLOYEES); name="select e.employee_id,e.first_name,e.last_name from employees e,departments d where e.department_id=d.department_id"
Statistics
----------------------------------------------------------
35 recursive calls
0 db block gets
70 consistent gets
0 physical reads
0 redo size
4020 bytes sent via SQL*Net to client
601 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
106 rows processed
2）设置系统参数，强制缓存所有结果集
alter system set result_cache_mode=‘force‘;
看示例：
--首先将两个表的result_cache属性设置为默认值
SQL> alter table employees result_cache(mode default);
Table altered.
SQL> alter table departments result_cache(mode default);
Table altered.
--再设置参数result_cache_mode为force（在另一个窗口中，以dba的角色进行设置），其默认为MANUAL
SQL> show parameter result_cache
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 7872K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL> alter system set result_cache_mode=‘FORCE‘;
System altered.
--执行无提示的SQL，发现继续使用cache_id为972x78n6vc4b3fq4bpa3ds60pg的缓存对象。
SQL> select e.employee_id,e.first_name,e.last_name from employees e,departments d where e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 2332 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | 972x78n6vc4b3fq4bpa3ds60pg | | | | |
|* 2 | TABLE ACCESS FULL| EMPLOYEES | 106 | 2332 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."DEPARTMENT_ID" IS NOT NULL)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(HR.EMPLOYEES); name="select e.employee_id,e.first_name,e.last_name from employees e,departments d where e.department_id=d.department_id"
Statistics
----------------------------------------------------------
111 recursive calls
0 db block gets
180 consistent gets
0 physical reads
0 redo size
4020 bytes sent via SQL*Net to client
601 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
106 rows processed
小结一下：
1）服务端结果集缓存的工作原理大概为：
当一个sql查询执行时，数据库先去缓存中看看是否已存在此结果集。如果存在，数据库直接使用此结果集；如果不存在，则执行此sql，返回此结果集，另外如果条件满足的话，会将返回的结果集进行缓存。当用户重复执行查询时，数据库会将缓存结果集直接返回，减少了响应时间。当结果集依赖的数据库对象发生变化，比如DDL、DML、权限等，此结果集变为无效。
2）启用result cache有三个方法，其优先顺序为:result_cache提示>设置表result_cache属性>设置result_cache_mode参数。
3）一般情况下使用前面两种方法即可满足需要，oracle不建议使用第三种方法。
4）对应于result_cache提示，还有no_result_cache提示，用于禁止使用结果集缓存。
二、参数
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 2336K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory (in bytes)
that can be used by the Result Cache. Values of this parameter greater than 0 are
rounded up to the next multiple of 32 KB. If the value of this parameter is 0, then the
feature is disabled.
对于此参数的默认值，oracle使用了以下算法:
■When using the MEMORY_TARGET initialization parameter to specify the memory allocation, Oracle Database allocates 0.25% of MEMORY_TARGET to the result cache.
■When you set the size of the shared pool using the SGA_TARGET initialization parameter, Oracle Database allocates 0.50% of SGA_TARGET to the result cache.
■If you specify the size of the shared pool using the SHARED_POOL_SIZE initialization parameter, then Oracle Database allocates 1% of the shared pool size to the result cache.
The size of the server result cache grows until reaching the maximum size. Query results larger than the available space in the cache are not cached. The database employs an LRU algorithm to age out cached results, but does not otherwise automatically release memory from the server result cache. You can use the DBMS_RESULT_CACHE.FLUSH procedure to purge memory.
You can change the memory allocated to the result cache by setting the RESULT_CACHE_MAX_SIZE initialization parameter. In an Oracle RAC environment, the result cache itself is specific to each instance and can be sized differently on each instance. However, invalidations work across instances. To disable the server result cache in a cluster, you must explicitly set this parameter to 0 for each instance startup.
RESULT_CACHE_MAX_RESULT specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use.The default is 5%, but you can specify any percentage value between 1 and 100. You can set this parameter at the system or session level.
RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query‘s
execution plan.
Values:
MANUAL
The ResultCache operator is added only when the query is annotated (that is,
hints).
■ FORCE
The ResultCache operator is added to the root of all SELECT statements (provided
that it is valid to do so).
FORCE mode is not recommended because the database and
clients will attempt to cache all queries, which may create significant
performance and latching overhead. Moreover, because queries that
call non-deterministic PL/SQL functions are also cached, enabling the
result cache in such a broad-based manner may cause material
changes to the results.
RESULT_CACHE_REMOTE_EXPIRATION specifies the number of minutes that a result using
a remote object is allowed to remain valid. Setting this parameter to 0 implies that
results using remote objects should not be cached. Setting this parameter to a nonzero
value can produce stale answers (for example, if the remote table used by a result is
modified at the remote database).
三、视图
1、V$RESULT_CACHE_OBJECTS displays all the objects (both cached results and
dependencies) and their attributes.
常用的字段有：
TYPE——Type of the cache object:
■ Result，代表结果集
■ Dependency，结果集所依赖的对象
STATUS——Status of the object:
■ New - Result is still under construction
■ Published - Result is available for use
■ Bypass - Result will be bypassed from use
■ Expired - Result has exceeded expiration time
■ Invalid - Result is no longer available for use
NAME——Name (for example, SQL prefix or PL/SQL function name)
ROW_COUNT——Total number of rows in the cached result
CACHE_ID——CacheId for the result (object name if it‘s a dependency)
这个地方我们需要关注一下STATUS=‘Invalid‘的情况。当所依赖的对象发生变化，比如DDL或DML，甚至是权限发生变化，都会让其状态从Published转变为Invalid。大家一定要注意。
2、V$RESULT_CACHE_DEPENDENCY displays the depends-on relationship between cached
results and dependencies.
RESULT_ID—— Cached result
DEPEND_ID——Dependency object
OBJECT_NO——Dictionary object number of the dependency object
3、V$RESULT_CACHE_MEMORY displays all the memory blocks and their status.
4、V$RESULT_CACHE_STATISTICS displays various Result Cache settings and usage
statistics.
四、系统包
提供DBMS_RESULT_CACHE系统包，用于管理缓存结果集。常用的方法有两个：
1、MEMORY_REPORT过程
This procedure produces the memory usage report for the Result Cache.
SQL> set serveroutput on;
SQL> exec 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 = 7872K bytes (7872 blocks)
Maximum Result Size = 393K bytes (393 blocks)
[Memory]
Total Memory = 384872 bytes [0.100% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 379520 bytes [0.098% of the Shared Pool]
....... Overhead = 117376 bytes
....... Cache Memory = 256K bytes (256 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 256 blocks
............... Dependencies = 32 blocks (32 count)
............... Results = 224 blocks
................... SQL = 213 blocks (204 count)
................... Invalid = 11 blocks (6 count)
PL/SQL procedure successfully completed.
2、flush过程或函数
This function and procedure attempts to remove all the objects from the Result Cache,
and depending on the arguments retains or releases the memory and retains or clears
the statistics.
SQL> exec DBMS_RESULT_CACHE.FLUSH;
PL/SQL procedure successfully completed.
SQL> exec 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 = 7872K bytes (7872 blocks)
Maximum Result Size = 393K bytes (393 blocks)
[Memory]
Total Memory = 5352 bytes [0.001% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
五、使用限制
1、结果集的一致性读要求
如果需要重用数据的一个快照，那这部分数据必须能够具有读一致性。必须满足以下某一个条件才能缓存此快照数据：
1）此快照数据必须是最新提交后的数据；
2）使用flash query功能时，查询时间点必须是一个明确的时间点；
另外，如果当前会话中有一个活动事务参考了某个查询中的对象，那这个查询的结果集也不会被缓存。
2、结果集缓存的其它要求
当sql查询中存在以下对象或函数时，也不能使用结果集缓存：
1）临时表，或者SYS、SYSTEM模式下的表；
2）序列的CURRVAL、NEXTVAL伪列；
3）SQL函数CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, and SYS_TIMESTAMP
3、Query Parameter Requirements for the Result Cache
Cache results can be reused when they are parameterized with variable values when queries are equivalent and the parameter values are the same. Different values or bind variable names may cause cache misses. Results are parameterized if any of the following constructs are used in the query:
■Bind variables
■The SQL functions DBTIMEZONE, SESSIONTIMEZONE, USERENV/SYS_CONTEXT (with constant variables), UID, and USER
■NLS parameters
Result Cache特性之SQL QUERY结果集缓存
标签：result cache result_cache_max_size result_cache_mode