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索引
- unusable index對DML/QUERY的影響Index
- mysql的DDL操作對業務產生影響測試MySql
- MySQL的DDL和DML操作語法MySql
- MySQL alter 新增列對dml影響MySql
- DDL、DML、DCL、DQL相關操作
- flush 快取對inmemory有什麼影響？快取
- 探索webpack熱更新對程式碼打包結果的影響（二）Web
- 源資料檔案(.csv)中的空格串和空串對pandas讀取結果的影響
- MsSql 資料庫使用sqlplus建立DDL和DML操作方法SQL資料庫
- MsSql資料庫使用SQL plus建立DDL和DML操作方法SQL資料庫
- NOT IN子查詢中出現NULL值對結果的影響你注意到了嗎Null
- Begin end程式碼段裡面有DDL和DML，如果DDL成功了而DML失敗了，則DDL的程式碼也會回滾
- 突然斷電，是否會影響Mysql的執行結果MySql
- Vue動態路由快取不相互影響的解決辦法Vue路由快取
- 資料庫：淺談DML、DDL、DCL的區別資料庫
- mysql事務對效率的影響分析總結JILEMySql
- Strategy Analytics：蘋果ARM架構晶片對PC晶片市場的影響蘋果架構晶片
- 客快物流大資料專案(四十五)：Spark操作Kudu DML操作大資料Spark
- Redis快取切面，防止Redis當機影響正常業務邏輯Redis快取
- 對於前端快取的理解（快取機制和快取型別）前端快取型別
- MySQL:MySQL客戶端快取結果導致OOMMySql客戶端快取OOM
- Alter修改表結構對資料儲存的影響PP
- 淺析CPU結構對程式的影響以及熔斷原理
- 語言對思維的影響
- Nologging對恢復的影響(一)
- Nologging對恢復的影響(二)
- 網線的分類與對網速的影響 網線對網速影響大嗎？
- 浮動的盒子對img的影響
- iOS快取的總結iOS快取
- MySQL DDL操作表MySql
- GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速MySql快取
- 通用結果類用於返回響應結果
- INDEX建立方式對SQL的影響IndexSQL
- 關於OPcache對Swoole影響的理解opcache
- 來電對播放音樂的影響
- python：super()對多繼承的影響Python繼承
- DB2 HADR對效能的影響DB2
- 04 MySQL 表的基本操作-DDLMySql