一 DDL 语句对结果缓存的影响。
清理实验环境,使用hint构造结果缓存,cache_id 为93qg9pxgyrhd35bxgp9ay1mvqw。
yang@rac1>exec dbms_result_cache.flush();
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.00
yang@rac1>set autotrace on
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
name="select /*+ result_cache */ object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
753 consistent gets
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
415 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>select object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; type=AUTO; dependencies=(YANG.YANGOBJ); parameters=(nls);
name="select object_type,count(*) from yangobj group by object_type
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
已用时间: 00: 00: 00.06
对表yangobj进行DDL操作,通过视图v$result_cache_objects查看结果缓存的status。
yang@rac1>ALTER TABLE yangobj MODIFY OBJECT_ID NOT NULL;
表已更改。
已用时间: 00: 00: 03.03
yang@rac1>set autotrace off
yang@rac1>select status,cache_id from v$result_cache_objects;
STATUS CACHE_ID
--------- ---------------------------------------------------
Published YANG.YANGOBJ
Invalid 93qg9pxgyrhd35bxgp9ay1mvqw --不可用了,
已用时间: 00: 00: 00.02
yang@rac1>set autotrace on
yang@rac1>select object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
name="select /*+ result_cache */ object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
192 recursive calls
0 db block gets
776 consistent gets
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
yang@rac1>set autotrace off
yang@rac1>select status,cache_id from v$result_cache_objects;
STATUS CACHE_ID
--------- ---------------------------------------------------------------------------------------------
Published YANG.YANGOBJ
Invalid 93qg9pxgyrhd35bxgp9ay1mvqw --老的结果缓存
已用时间: 00: 00: 00.01
yang@rac1>
二 DML 操作对结果缓存的影响。
清理缓存缓存。
yang@rac1>exec dbms_result_cache.flush();
PL/SQL 过程已成功完成。
yang@rac1>alter system set result_cache_mode=manual;
系统已更改。
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 过程已成功完成。
yang@rac1>alter system flush shared_pool;
系统已更改。
构造缓存
yang@rac1>set autotrace on
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
name="select /*+ result_cache */ object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
246 recursive calls
0 db block gets
781 consistent gets
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
name="select /*+ result_cache */object_type,count(*) from yangobj group by obje
ct_type"
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 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>set autotrace off
yang@rac1>select name,cache_id,cache_key from v$result_cache_objects;
NAME CACHE_ID
----------------------------------- -----------------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
YANG.YANGOBJ YANG.YANGOBJ
YANG.YANGOBJ
select /*+ result_cache */ object_t 93qg9pxgyrhd35bxgp9ay1mvqw
ype,count(*) from yangobj group by
object_type
fpn1dsgmvbq9cbhu4vs188mqr3
yang@rac1>select name,status,cache_id,cache_key from v$result_cache_objects;
NAME STATUS CACHE_ID
----------------------------------- --------- -------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
YANG.YANGOBJ Published YANG.YANGOBJ
YANG.YANGOBJ
select /*+ result_cache */ object_t Published 93qg9pxgyrhd35bxgp9ay1mvqw
ype,count(*) from yangobj group by
object_type
fpn1dsgmvbq9cbhu4vs188mqr3
对表yangobj进行dml操作并不提交,
yang@rac1>delete from yangobj where rownum<11;
已删除10行。
yang@rac1>select name,status,cache_id,cache_key from v$result_cache_objects;
NAME STATUS CACHE_ID
----------------------------------- --------- -------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
YANG.YANGOBJ Published YANG.YANGOBJ
YANG.YANGOBJ
select /*+ result_cache */ object_t Published 93qg9pxgyrhd35bxgp9ay1mvqw
ype,count(*) from yangobj group by
object_type
fpn1dsgmvbq9cbhu4vs188mqr3
yang@rac1>set autotrace off
yang@rac1>set autotrace on
再次查询时候,结果缓存不可用了。
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65517
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select /*+ result_cache */object_type,count(*) from yangobj group by obje
ct_type"
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
753 consistent gets --产生了一致性读。
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 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>rollback;
回退已完成。
yang@rac1>set autotrace off
yang@rac1>select name,status,cache_id,cache_key from v$result_cache_objects;
NAME STATUS CACHE_ID
----------------------------------- --------- -------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
YANG.YANGOBJ Published YANG.YANGOBJ
YANG.YANGOBJ
select /*+ result_cache */ object_t Published 93qg9pxgyrhd35bxgp9ay1mvqw
ype,count(*) from yangobj group by
object_type
fpn1dsgmvbq9cbhu4vs188mqr3
yang@rac1>set autotrace on
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select /*+ result_cache */object_type,count(*) from yangobj group by obje
ct_type"
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
对表的DML操作如果提交,是会造成结果缓存的失效的,如果回滚,结果缓存依然可用。