Oracle 11g提供了DBMS_RESULT_CACHE包来查询SQL结果缓存内容的状态和适当地控制SQL结果缓存内容。
DBMS_RESULT_CACHE功能和存储过程
功能/存储过程 描述
STATUS： 返回结果缓存的当前状态。值包括：
ENABLED： 结果缓存是激活的。
DISABLED：结果缓存是不可用的。
BYPASSED：结果缓存暂时不可用。
SYNC： 结果缓存是可用的，但是目前正与其他RAC节点重新同步。
MEMORY_REPORT： 列出结果缓存内存利用的一个概要（默认）或详细的报表。
FLUSH： 推出整个结果缓存的内容。
INVALIDATE： 使结果缓存中一个特定对象的缓存结果无效。
INVALIDATE_OBJECT： 根据缓存ID使一特定结果缓存无效。
下面通过具体的实验给予介绍：
一 STATUS： 返回结果缓存的当前状态。
yang@rac1>select dbms_result_cache.status() from dual;
DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------
ENABLED
二 MEMORY_REPORT： 列出结果缓存内存利用的一个概要（默认）或详细的报表。视图v$result_cache_statistics 是MEMORY_REPORT相同的描述
yang@rac1>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 = 15744K bytes (15744 blocks)
Maximum Result Size = 787K bytes (787 blocks)
[Memory]
Total Memory = 12704 bytes [0.001% of the Shared Pool]
... Fixed Memory = 12704 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
yang@rac1>select * from v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ ---------------------------------------------------------------------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 15744
3 Block Count Current 0
4 Result Size Maximum (Blocks) 787
5 Create Count Success 0
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 0
12 Global Hit Count 0
13 Global Miss Count 0
13 rows selected.
yang@rac1>set autotrace on
yang@rac1>select /*+ result_cache */ count(*) from yangobj;
COUNT(*)
----------
74484
Execution Plan
----------------------------------------------------------
Plan hash value: 362321706
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 297 (1)| 00:00:04 |
| 1 | RESULT CACHE | 7uz1ww4x7gs2a6ba4qjauzt4bq | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| YANGOBJ | 61204 | 297 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(YANG.YANGOBJ); attributes=(single-row); name="select /*+ result_cache */ count(*) from yangobj"
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
4 db block gets
1126 consistent gets
0 physical reads
548 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
yang@rac1>/
COUNT(*)
----------
74484
Execution Plan
----------------------------------------------------------
Plan hash value: 362321706
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 297 (1)| 00:00:04 |
| 1 | RESULT CACHE | 7uz1ww4x7gs2a6ba4qjauzt4bq | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| YANGOBJ | 61204 | 297 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(YANG.YANGOBJ); attributes=(single-row); name="select /*+ result_cache */ count(*) from yangobj"
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从视图v$result_cache_objects中获取CACHE_ID。
yang@rac1>select name,status,cache_id from v$result_cache_objects;
NAME STATUS CACHE_ID
------------------------------ --------- -----------------------------------
YANG.YANGOBJ Published YANG.YANGOBJ
select /*+ result_cache */ cou Published 7uz1ww4x7gs2a6ba4qjauzt4bq
nt(*) from yangobj
三 INVALIDATE_OBJECT： 根据缓存ID--CACHE_ID使一特定结果缓存无效。
yang@rac1>exec DBMS_RESULT_CACHE.INVALIDATE_OBJECT('7uz1ww4x7gs2a6ba4qjauzt4bq');
PL/SQL procedure successfully completed.
yang@rac1>select name,status,cache_id from v$result_cache_objects;
NAME STATUS CACHE_ID
------------------------------ --------- -----------------------------------
YANG.YANGOBJ Published YANG.YANGOBJ
select /*+ result_cache */ cou Invalid 7uz1ww4x7gs2a6ba4qjauzt4bq
nt(*) from yangobj
四 FLUSH： 清理整个结果缓存的内容。
yang@rac1>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 = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 107812 bytes [0.049% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 98352 bytes [0.045% of the Shared Pool]
....... verhead = 65584 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 26 blocks
........... Used Memory = 6 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 3 blocks
................... SQL = 2 blocks (2 count)
................... Invalid = 1 blocks (1 count)
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.35
yang@rac1>exec dbms_result_cache.flush();
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.02
yang@rac1>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 = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 9460 bytes [0.004% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.02
结果缓存已被清除。。