| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 106K (1)| 00:24:46 | | |
| 1 | RESULT CACHE | 0mr1089p1wxv3919raqyvtwtsv | | | | | | |
| 2 | SORT AGGREGATE | | 1 | 14 | | | | |
| 3 | PARTITION RANGE ALL | | 2173K| 29M| 106K (1)| 00:24:46 | 1 | 2 |
| 4 | PARTITION LIST ITERATOR| | 2173K| 29M| 106K (1)| 00:24:46 | KEY | KEY |
|* 5 | TABLE ACCESS FULL | GG_DISTRIBUTION | 2173K| 29M| 106K (1)| 00:24:46 | 1 | 48 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("W"."CREATE_DATE">TO_DATE(' 2013-01-01 00:00:00', 'syyyy-GG-dd hh24:mi:ss') AND "W"."DATA_AREA"
LIKE '03%')
Result Cache Information (identified by operation id):
-----------------------------------------------------
1 - column-count=1; dependencies=(LCAM_TEST.GG_DISTRIBUTION); attributes=(single-row); parameters=(nls); name="SELECT COUNT(1)
FROM GG_DISTRIBUTION W
WHERE W.DATA_AREA LIKE '03' || '%'
AND W.CREATE_DATE > TO_DATE('2013-01-01', 'yyyy-"
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
339 bytes sent via SQL*Net to client
337 bytes received via SQL*Net from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以根据RESULT CACHE=0mr1089p1wxv3919raqyvtwtsv找到其缓存的信息。
SQL> select s.STATUS,
s.CREATION_TIMESTAMP,
s.BUILD_TIME,
s.ROW_COUNT,
s.SCAN_COUNT
from v$result_cache_objects s
where cache_id = '0mr1089p1wxv3919raqyvtwtsv';
STATUS CREATION_TIMES BUILD_TIME ROW_COUNT SCAN_COUNT
--------- -------------- ---------- ---------- ----------
Published 20-6月 -14 700 1 26
结果缓存的限制：
当查询语句使用非确定性函数、序列号和临时表的时候不能被缓存。
查询语句可能会造成数据不一致的时候。
引用到数据字典视图的查询语句不能缓存。
可以使用dbms_result_cache管理缓存。