Oracle11新特性——SQL快取結果集（五）
打算寫一系列的文章介紹11g的新特性和變化。
這篇文章討論DML對SQL快取結果集的影響。
Oracle11新特性——SQL快取結果集（一）：http://yangtingkun.itpub.net/post/468/391015
Oracle11新特性——SQL快取結果集（二）：http://yangtingkun.itpub.net/post/468/391560
Oracle11新特性——SQL快取結果集（三）：http://yangtingkun.itpub.net/post/468/392028
Oracle11新特性——SQL快取結果集（四）：http://yangtingkun.itpub.net/post/468/475130
在看文件的時候發現了這個描述，檢查了一下以前的文章，雖然提到了DML影響緩衝結果集，但是側重點不一樣，並沒有描述當使用者對目標表進行修改後，且事務沒有提交之前，沒有辦法利用快取結果集的特性，具體情況透過一個例子說明：
[oracle@bjtest ~]$ sqlplus TEST/TEST
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 9月 9 01:16:19 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pages 100 lines 120
SQL> show parameter result_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 32M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL> create table t_cache as
2 select *
3 from all_objects;
表已建立。
SQL> set autot on
SQL> select /*+ result_cache */ count(*) from t_cache;
COUNT(*)
----------
69433
執行計劃
----------------------------------------------------------
Plan hash value: 56254237
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 196 (1)| 00:00:03 |
| 1 | RESULT CACHE | cs0yudzh0cqqb1mc32r032mgkq | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T_CACHE | 71356 | 196 (1)| 00:00:03 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(TEST.T_CACHE); attributes=(single-row); name="select /*+ result_cache */ count(*) f
rom t_cache"
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
4 recursive calls
0 db block gets
575 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ result_cache */ count(*) from t_cache;
COUNT(*)
----------
69433
執行計劃
----------------------------------------------------------
Plan hash value: 56254237
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 196 (1)| 00:00:03 |
| 1 | RESULT CACHE | cs0yudzh0cqqb1mc32r032mgkq | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T_CACHE | 71356 | 196 (1)| 00:00:03 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(TEST.T_CACHE); attributes=(single-row); name="select /*+ result_cache */ count(*) f
rom t_cache"
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
快取結果集對第二次查詢已經生效，下面當前會話修改一條記錄：
SQL> delete t_cache where rownum = 1;
已刪除 1 行。
執行計劃
----------------------------------------------------------
Plan hash value: 2616302805
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 196 (1)| 00:00:03 |
| 1 | DELETE | T_CACHE | | | |
|* 2 | COUNT STOPKEY | | | | |
| 3 | TABLE ACCESS FULL| T_CACHE | 71356 | 196 (1)| 00:00:03 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
6 recursive calls
1 db block gets
77 consistent gets
0 physical reads
396 redo size
840 bytes sent via SQL*Net to client
784 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ result_cache */ count(*) from t_cache;
COUNT(*)
----------
69432
執行計劃
----------------------------------------------------------
Plan hash value: 56254237
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 196 (1)| 00:00:03 |
| 1 | RESULT CACHE | cs0yudzh0cqqb1mc32r032mgkq | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T_CACHE | 71356 | 196 (1)| 00:00:03 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(TEST.T_CACHE); attributes=(single-row); name="select /*+ result_cache */ count(*) f
rom t_cache"
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
506 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ result_cache */ count(*) from t_cache;
COUNT(*)
----------
69432
執行計劃
----------------------------------------------------------
Plan hash value: 56254237
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 196 (1)| 00:00:03 |
| 1 | RESULT CACHE | cs0yudzh0cqqb1mc32r032mgkq | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T_CACHE | 71356 | 196 (1)| 00:00:03 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(TEST.T_CACHE); attributes=(single-row); name="select /*+ result_cache */ count(*) f
rom t_cache"
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
506 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
當前會話由於修改了T_CACHE表，因此原本的快取結果集對於當前會話的查詢是不正確的，但是當前會話的第一次查詢並沒有講結果儲存到結果集快取中，第二次執行同樣的查詢仍然需要訪問表。
對於其他的會話由於修改還未提交，因此原本的快取結果集仍然生效：
SQL> set sqlp 'SQL2> '
SQL2> set autot on
SQL2> select /*+ result_cache */ count(*) from t_cache;
COUNT(*)
----------
69433
執行計劃
----------------------------------------------------------
Plan hash value: 56254237
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 196 (1)| 00:00:03 |
| 1 | RESULT CACHE | cs0yudzh0cqqb1mc32r032mgkq | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T_CACHE | 71356 | 196 (1)| 00:00:03 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(TEST.T_CACHE); attributes=(single-row); name="select /*+ result_cache */ count(*) f
rom t_cache"
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Oracle為了保證一致性讀，因此在會話修改了表的記錄後，不再啟用快取結果集的機制。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-673011/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle11新特性——SQL快取結果集（三）OracleSQL快取
- Oracle11新特性——PLSQL函式快取結果（一）OracleSQL函式快取
- Oracle11新特性——PLSQL函式快取結果（三）OracleSQL函式快取
- Oracle11新特性——PLSQL函式快取結果（二）OracleSQL函式快取
- 11g新特性之結果集快取快取
- Oracle11新特性——PLSQL新特性（五）OracleSQL
- Oracle11g新特性-PLSQL函式快取結果（一）[zt]OracleSQL函式快取
- oracle result cache 結果集快取的使用Oracle快取
- PLSQL Language Reference-PL/SQL子程式-PL/SQL函式結果快取-開啟函式結果快取SQL函式快取
- 共享池之五：Shared Pool子池與結果集快取技術快取
- 【11gR2新特性】DBMS_RESULT_CACHE管理結果快取的包快取
- Oracle11新特性——分割槽功能增強（五）Oracle
- Oracle11新特性——PLSQL新特性（七）OracleSQL
- Oracle11新特性——PLSQL新特性（六）OracleSQL
- Oracle11新特性——PLSQL新特性（四）OracleSQL
- Oracle11新特性——PLSQL新特性（三）OracleSQL
- Oracle11新特性——PLSQL新特性（二）OracleSQL
- Oracle11新特性——PLSQL新特性（一）OracleSQL
- Oracle11新特性——備份恢復功能增強（五）Oracle
- Oracle11新特性——線上操作功能增強（五）Oracle
- PHP PDO獲取結果集PHP
- Oracle11新特性——虛擬列Oracle
- PLSQL Language Reference-PL/SQL子程式-PL/SQL函式結果快取(二)SQL函式快取
- 【SQL】獲取指定範圍內結果集的實現方法SQL
- Oracle11新特性——虛擬列（二）Oracle
- Oracle12c中PL/SQL(DBMS_SQL)新特性之隱式語句結果OracleSQL
- Sql_從查詢的結果集中分組後取最後有效的資料成新的結果集小記(待優化)SQL優化
- Oracle11新特性——撤銷事務（一）Oracle
- Oracle11新特性——分割槽功能增強Oracle
- Oracle11新特性——撤銷事務（三）Oracle
- Oracle11新特性——撤銷事務（二）Oracle
- DDL,DML操作對結果快取的影響快取
- Oracle11新特性——分割槽功能增強（四）Oracle
- Oracle11新特性——行列轉換語句（二）Oracle
- Oracle11新特性——行列轉換語句（一）Oracle
- Oracle11新特性——分割槽功能增強（三）Oracle
- Oracle11新特性——分割槽功能增強（二）Oracle
- Oracle11新特性——分割槽功能增強（一）Oracle