L>
注意到这个执行计划已经和以往的不同，RESULTCACHE以1hnnwscv2aj3631n497zczt04j名称创建。那么在接下来的查询中，这个Result Cache就可以被利用：
09:58:49 scott@felix SQL>select /*+result_cache */ count(*) from felix;
COUNT(*)
----------
75613
Execution Plan
----------------------------------------------------------
Plan hash value: 2587295606
------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1| 301 (1)| 00:00:04 |
| 1 | RESULT CACHE | 1hnnwscv2aj3631n497zczt04j | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| FELIX | 63221 | 301 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operationid):
------------------------------------------------------
1 - column-count=1;dependencies=(SCOTT.FELIX); attributes=(single-row); name="select /*+result_cache */ count(*) from felix"
Note
-----
- dynamicsampling 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 toclient
523 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
10:01:08 scott@felix SQL>
在这个利用到Result Cache的查询中，consistent gets减少到0，直接访问结果集，不再需要执行SQL查询。这就是Result Cache的强大之处。
在以上测试中，当result_cache_mode设置为MANUAL时，只有使用hints的情况下，Oracle才会利用缓存结果集；而如果将result_cache_mode设置为AUTO，Oracle如果发现缓冲结果集已经存在，那么就会自动使用。但是如果缓冲结果集不存在，Oracle并不会自动进行缓冲，只有使用HINT的情况下，Oracle才会将执行的结果集缓存。
可以通过查询v$result_cache_memory视图来看Cache的使用情况：
10:05:07 scott@felix SQL>select * fromV$RESULT_CACHE_MEMORY where free='NO';
ID CHUNK OFFSET FREE OBJECT_ID POSITION
---------- ---------- ---------- ------ --------------------
0 0 0 NO 0 0
1 0 1 NO 1 0
10:05:12 scott@felix SQL>
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_MEMORY displays all the memory blocks and their status.
| Column |
Datatype |
Description |
| ID |
NUMBER |
Unique block identifier (that is, the block number) |
| CHUNK |
NUMBER |
Chunk to which the block belongs (the upper 27 bits of the ID) |
| OFFSET |
NUMBER |
Offset of the block within its chunk (the lower 5 bits of the ID) |
| FREE |
VARCHAR2(3) |
Indicates whether the block is free (YES) or not (NO) |
| OBJECT_ID |
NUMBER |
Cache object to which the memory block belongs; NULL if the memory block is not allocated to a cache object (FREE = YES) |
| POSITION |
NUMBER |
Position of the block in the cached object; NULL if the memory block is not allocated to a cache object (FREE = YES) |
通过V$RESULT_CACHE_STATISTICS可以查询Result Cache的统计信息：
10:15:27 scott@felix SQL>select * fromV$RESULT_CACHE_STATISTICS;
IDNAME VALUE
-------------------------------------------------- ------------------------
1 Block Size (Bytes) 1024
2Block Count Maximum 1024
3Block Count Current 32
4Result Size Maximum (Blocks) 51
5Create Count Success 1
6Create Count Failure 0
7Find Count 1
8Invalidation Count 0
9Delete Count Invalid 0
10Delete Count Valid 0
11Hash Chain Length 1
12Find Copy Count 1
12 rows selected.
10:15:34 scott@felix SQL>
V$RESULT_CACHE_OBJECTS记录了Cache的对象：