Oracle11新特性——SQL快取結果集（三）
打算寫一系列的文章介紹11g的新特性和變化。
這篇文章繼續介紹SQL快取結果集的特點並簡單介紹DBMS_RESULT_CACHE包。
Oracle11新特性——SQL快取結果集（一）：http://yangtingkun.itpub.net/post/468/391015
Oracle11新特性——SQL快取結果集（二）：http://yangtingkun.itpub.net/post/468/391560
上一篇介紹了RESULT CACHE的一些特點，其中最後提到了使用RESULT CACHE可能會導致查詢結果不正確。下面看一個簡單的例子：
SQL> SET AUTOT ON
SQL> ALTER SESSION SET RESULT_CACHE_MODE = MANUAL;
會話已更改。
SQL> SELECT /*+ FULL(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;
OBJECT_NAME
------------------------------
ICOL$
執行計劃
----------------------------------------------------------
Plan hash value: 508354683
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 1 | 25 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
377 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 /*+ INDEX(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;
OBJECT_NAME
------------------------------
C_OBJ#
執行計劃
----------------------------------------------------------
Plan hash value: 370698254
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T_OBJECT_ID | 68324 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
343 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
先將RESULT_CACHE_MODE設定為MANUAL，觀察全表掃描和索引全掃描兩種查詢的結果。由於執行計劃的不同，雖然語句本身是一樣的，但是返回結果中記錄的順序是不同的。在這個例子中，如果使用全表掃描會返回ICOL$，而採用索引掃描，返回結果為C_OBJ#。
下面看看啟用快取結果集的情況：
SQL> SELECT /*+ RESULT_CACHE FULL(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;
OBJECT_NAME
------------------------------
ICOL$
執行計劃
----------------------------------------------------------
Plan hash value: 508354683
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 668yp49bw4mfnak70m2pww541d | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| T | 1 | 25 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(YANGTK.T); name="SELECT /*+ RESULT_CACHE FULL(T) */ OBJECT_NAME
= 1"
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
377 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 INDEX(T) */ OBJECT_NAME FROM T WHERE ROWNUM = 1;
OBJECT_NAME
------------------------------
ICOL$
執行計劃
----------------------------------------------------------
Plan hash value: 370698254
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | 668yp49bw4mfnak70m2pww541d | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 25 | 3 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | IND_T_OBJECT_ID | 68324 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(YANGTK.T); name="SELECT /*+ RESULT_CACHE INDEX(T) */ OBJECT_NAM
M = 1"
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由於第二個查詢指定使用了RESULT CACHE，雖然Oracle顯示的執行計劃是索引掃描，但是利用的快取結果集是全表掃描生成的，所以第二個查詢得到的結果也和第一個查詢一樣。
對於以前的系統中，試圖利用索引按照順序返回結果集的特點來避免排序的寫法，在利用RESULT CACHE時，很可能會出現問題。
不知道Oracle是否會認為這是一個bug，是否以後會有所改進。
RESULT CACHE不僅對查詢的最終結果生效，查詢的中間結果也是可以使用RESULT CACHE的。
在進行測試之前，先將前面的RESULT CACHE清除掉。Oracle提供了一個DBMS_RESULT_CACHE包來管理RESULT CACHE：
SQL> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM V$RESULT_CACHE_OBJECTS;
COUNT(*)
----------
5
SQL> EXEC DBMS_RESULT_CACHE.FLUSH
PL/SQL 過程已成功完成。
SQL> SELECT COUNT(*) FROM V$RESULT_CACHE_OBJECTS;
COUNT(*)
----------
0
這個包還包括其他一些功能，比如BYPASS可以設定當前例項繞過RESULT CACHE機制。INVALIDATE可以設定某個物件關聯的所有RESULT CACHE失效。STATUS返回結果集狀態。
下面看看Oracle如何對查詢的中間結果使用RESULT CACHE：
SQL> SET AUTOT ON
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SEGMENTS;
表已建立。
SQL> SELECT /*+ RESULT_CACHE */ OWNER, COUNT(*) NUM FROM T GROUP BY OWNER;
OWNER NUM
------------------------------ ----------
WKSYS 371
MDSYS 1281
YANGTK 13
.
.
.
WMSYS 315
SI_INFORMTN_SCHEMA 8
已選擇22行。
執行計劃
----------------------------------------------------------
Plan hash value: 47235625
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 132 | 287 (3)| 00:00:04 |
| 1 | RESULT CACHE | g02167xk9g68s52fnfssvx00tr | | | | |
| 2 | HASH GROUP BY | | 22 | 132 | 287 (3)| 00:00:04 |
| 3 | TABLE ACCESS FULL| T | 68324 | 400K| 282 (1)| 00:00:04 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANGTK.T); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ O
ROM T GROUP BY OWNER"
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
1011 consistent gets
0 physical reads
0 redo size
826 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22 rows processed
SQL> SELECT /*+ RESULT_CACHE */ OWNER, SUM(BYTES)/1024 K FROM T1 GROUP BY OWNER;
OWNER K
------------------------------ ----------
WKSYS 7616
MDSYS 47744
YANGTK 11392
.
.
.
SYS 854656
WMSYS 7296
已選擇18行。
執行計劃
----------------------------------------------------------
Plan hash value: 136660032
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7146 | 209K| 36 (6)| 00:00:01 |
| 1 | RESULT CACHE | f072b82hjsvsm9yvr91n766xwp | | | | |
| 2 | HASH GROUP BY | | 7146 | 209K| 36 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 7146 | 209K| 34 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANGTK.T1); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
24 K FROM T1 GROUP BY OWNER"
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
68 recursive calls
0 db block gets
183 consistent gets
109 physical reads
0 redo size
773 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
SQL> SELECT A.OWNER, NUM, K FROM
2 (SELECT /*+ RESULT_CACHE */ OWNER, COUNT(*) NUM FROM T GROUP BY OWNER) A,
3 (SELECT /*+ RESULT_CACHE */ OWNER, SUM(BYTES)/1024 K FROM T1 GROUP BY OWNER) B
4 WHERE A.OWNER = B.OWNER;
OWNER NUM K
------------------------------ ---------- ----------
WKSYS 371 7616
MDSYS 1281 47744
YANGTK 13 11392
.
.
.
SYS 29743 854656
WMSYS 315 7296
已選擇18行。
執行計劃
----------------------------------------------------------
Plan hash value: 3810504953
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7146 | 418K| 323 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 7146 | 418K| 323 (3)| 00:00:04 |
| 2 | VIEW | | 22 | 660 | 287 (3)| 00:00:04 |
| 3 | RESULT CACHE | g02167xk9g68s52fnfssvx00tr | | | | |
| 4 | HASH GROUP BY | | 22 | 132 | 287 (3)| 00:00:04 |
| 5 | TABLE ACCESS FULL| T | 68324 | 400K| 282 (1)| 00:00:04 |
| 6 | VIEW | | 7146 | 209K| 36 (6)| 00:00:01 |
| 7 | RESULT CACHE | f072b82hjsvsm9yvr91n766xwp | | | | |
| 8 | HASH GROUP BY | | 7146 | 209K| 36 (6)| 00:00:01 |
| 9 | TABLE ACCESS FULL| T1 | 7146 | 209K| 34 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OWNER"="B"."OWNER")
Result Cache Information (identified by operation id):
------------------------------------------------------
3 - column-count=2; dependencies=(YANGTK.T); parameters=(nls); name="SELECT /*+ RESULT_CACHE */ O
ROM T GROUP BY OWNER"
7 - column-count=2; dependencies=(YANGTK.T1); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
24 K FROM T1 GROUP BY OWNER"
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
127 recursive calls
8 db block gets
194 consistent gets
0 physical reads
0 redo size
889 bytes sent via SQL*Net to client
388 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
18 rows processed
需要注意，對於SQL中間結果使用RESULT CACHE必須使用RESULT_CACHE提示進行強制。
RESULT CACHE功能對於下列情況是無效的：系統表和臨時表；序列的NEXTVAL和CURRVAL偽列；SYSDATE、SYSTIMESTAMP等函式；所有非確定性PL/SQL函式。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-69413/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【11gR2新特性】DBMS_RESULT_CACHE管理結果快取的包快取
- Sql_從查詢的結果集中分組後取最後有效的資料成新的結果集小記(待優化)SQL優化
- MySQL:MySQL客戶端快取結果導致OOMMySql客戶端快取OOM
- Mybatis(三) 快取MyBatis快取
- iOS FMDB有返回結果集和無返回結果集iOS
- 23C新特性SQL防火牆 （SQL Firewall）SQL防火牆
- PB帶引數帶結果集的動態SQL查詢SQL
- 面試總結 —— Redis “快取穿透”、“快取擊穿”、“快取雪崩”面試Redis快取穿透
- MaxCompute如何對SQL查詢結果實現分頁獲取SQL
- C++11新特性（三）：語言特性C++
- SQL語言_多表查詢和TOP限制結果集_PAGE3SQL
- JPA SQL 查詢、結果集對映(@NamedNativeQuery、@ColumnResult註解說明)SQL
- Redis快取高可用叢集Redis快取
- 動態 SQL 和快取機制SQL快取
- Redis 快取雪崩，快取擊穿和快取穿透技術方案總結Redis快取穿透
- 新華三智慧計算聯盟，集結！
- 批次對比結果集
- 結果集 (ResultSet)全面解析
- HTTP三種快取方式HTTP快取
- Kotlin 1.5.20 釋出了～快來看看新特性Kotlin
- 23c 新特性之SQL_transpilerSQL
- ABAP 740新的OPEN SQL增強特性SQL
- SQL?Server新特性SequenceNumber用法介紹YTZBSQLServer
- 蘋果推出“蘋果邊緣快取”（蘋果企業簽名）蘋果快取
- STM32定時器觸發ADC多通道連續取樣，DMA快取結果定時器快取
- iOS快取的總結iOS快取
- Redis 6.0 新特性篇：深度剖析客戶端快取（Client side caching）原理與效能Redis客戶端快取clientIDE
- Redis 的高效能快取機制的三類問題：快取擊穿、快取雪崩 和 快取穿透Redis快取穿透
- Java 8新特性（三）：Optional類Java
- SQL Server 2008中Analysis Services的新特性——深入SQL Server 2008SQLServer
- TiDB 4.0 新特性前瞻（三）再也不用擔心我的 SQL 突然變慢了TiDBSQL
- 如何使用yii2的快取依賴特性快取
- html5新特性總結HTML
- css3新特性總結CSSS3
- ES6新特性總結
- React 16 新特性使用總結React
- JDK1.8新特性總結JDK
- 【10g SQL新特性】q-quote使用SQL
- mysql group by 取想要的結果MySql