DDL,DML操作對結果快取的影響
一 DDL 語句對結果快取的影響。
清理實驗環境，使用hint構造結果快取，cache_id 為93qg9pxgyrhd35bxgp9ay1mvqw。
dbms_result_cache.flush();
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.00
autotrace on
/*+ 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
再次查詢時，使用到上次快取的結果集。
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。
TABLE yangobj MODIFY OBJECT_ID NOT NULL;
表已更改。
已用時間: 00: 00: 03.03
autotrace off
status,cache_id from v$result_cache_objects;
STATUS CACHE_ID
--------- ---------------------------------------------------
Published YANG.YANGOBJ
Invalid 93qg9pxgyrhd35bxgp9ay1mvqw --不可用了，
已用時間: 00: 00: 00.02
autotrace on
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
autotrace off
status,cache_id from v$result_cache_objects;
STATUS CACHE_ID
--------- ---------------------------------------------------------------------------------------------
Published YANG.YANGOBJ
Invalid 93qg9pxgyrhd35bxgp9ay1mvqw --老的結果快取
已用時間: 00: 00: 00.01
>
二 DML 操作對結果快取的影響。
清理快取快取。
dbms_result_cache.flush();
PL/SQL 過程已成功完成。
system set result_cache_mode=manual;
系統已更改。
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 過程已成功完成。
system flush shared_pool;
系統已更改。
構造快取
autotrace on
/*+ 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
/*+ 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
autotrace off
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
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操作並不提交，
from yangobj where rownum<11;
已刪除10行。
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
autotrace off
autotrace on
再次查詢時候，結果快取不可用了。
/*+ 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
回滾之後，結果快取可用。
;
回退已完成。
autotrace off
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
autotrace on
/*+ 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操作如果提交，是會造成結果快取的失效的，如果回滾，結果快取依然可用。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-702130/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle點陣圖索引對DML操作的影響Oracle索引
- mysql的DDL操作對業務產生影響測試MySql
- unusable index對DML/QUERY的影響Index
- MySQL alter 新增列對dml影響MySql
- Oracle DML（非select） 操作不commit 對select的影響OracleMIT
- Sql Server之旅——第十站 看看DML操作對索引的影響SQLServer索引
- 【SQL*Plus】直接X掉視窗對SQL*Plus中的操作結果的影響SQL
- flush 快取對inmemory有什麼影響？快取
- SQL查詢結果集對注入的影響及利用SQL
- DML操作 DDL觸發器觸發器
- 驗證資料壓縮對DML的影響
- 探索webpack熱更新對程式碼打包結果的影響（二）Web
- 探索webpack熱更新對程式碼打包結果的影響（一）Web
- 小議分析函式中排序對結果的影響（二）函式排序
- 小議分析函式中排序對結果的影響（一）函式排序
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- DDL、DML、DCL、DQL相關操作
- Mysql 基礎操作 DDL DML DCLMySql
- 源資料檔案(.csv)中的空格串和空串對pandas讀取結果的影響
- DML, DDL操作的自動提交問題
- 查詢快取(query_cache)的影響快取
- 配置支援DML和DDL操作同步的GoldenGateGo
- 關於drop操作對role的影響
- Oracle DBLINK 抽數以及DDL、DML操作Oracle
- 使用Logminer工具分析DML和DDL操作
- oracle result cache 結果集快取的使用Oracle快取
- reverse index 對於 MAX/MIN操作的影響Index
- 處理器快取影響大觀園快取
- NOT IN子查詢中出現NULL值對結果的影響你注意到了嗎Null
- 分割槽表的不同操作對索引的影響索引
- 突然斷電，是否會影響Mysql的執行結果MySql
- mysql刪除和更新操作對效能的影響MySql
- Nologging操作對standby的影響 (zt)
- CONTEXT索引對COMMIT操作的影響 （ZT）Context索引MIT
- PLSQL Language Reference-PL/SQL子程式-PL/SQL函式結果快取-開啟函式結果快取SQL函式快取
- 構建高效且可伸縮的結果快取快取
- php中對MYSQL操作之批量執行，與獲取批量結果PHPMySql