Oracle11gr2新增表的RESULT CACHE屬性
在11.2中，Oracle新增了一個表的引數屬性RESULT_CACHE，設定是否將表的查詢結果放到RESULT CACHE中。
在11.1中，Oracle引入了RESULT CACHE功能，設定RESULT CACHE可以透過HINT以及初始化引數兩種方式實現。但是使用初始化引數設定RESULT CACHE的覆蓋面太大，會導致一些不希望被CACHE的查詢也被放到RESULT CACHE中。而使用HINT方式的覆蓋面又太窄，只對指定的查詢有效，使用相對比較麻煩。
於是在11.2中，Oracle推出了表級的RESULT CACHE，當設定表的RESULT CACHE後，這個表的所有查詢都被放到RESULT CACHE緩衝區中：
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
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
2 RESULT_CACHE (MODE FORCE)
3 AS SELECT *
4 FROM ALL_OBJECTS;
表已建立。
SQL> SET AUTOT ON
SQL> SELECT 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 | 563an9xyg23fyb37x3qwjkadfa | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T_CACHE | 71348 | 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 COUNT(*) FROM T_CACHE"
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
28 recursive calls
0 db block gets
577 consistent gets
502 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 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 | 563an9xyg23fyb37x3qwjkadfa | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T_CACHE | 71348 | 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 COUNT(*) FROM 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
當設定了RESULT_CACHE(MODE FORCE)屬性後，對於表的查詢自動使用RESULT CACHE機制，即使初始化引數RESULT_CACHE_MODE設定了MANUAL，且並沒有使用HINT來強制使用RESULT CACHE。
不過需要注意，RESULT_CACHE的預設設定是DEFAULT，只要一個查詢訪問的表中有一個是DEFAULT狀態，整個查詢就不會使用RESULT CACHE機制：
SQL> CREATE TABLE T_CACHE2
2 RESULT_CACHE (MODE DEFAULT)
3 AS SELECT *
4 FROM USER_OBJECTS;
表已建立。
SQL> SELECT COUNT(*)
2 FROM T_CACHE T1, T_CACHE2 T2
3 WHERE T1.OBJECT_ID = T2.OBJECT_ID;
COUNT(*)
----------
13798
執行計劃
----------------------------------------------------------
Plan hash value: 3151804270
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 239 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 12465 | 316K| 239 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| T_CACHE2 | 12465 | 158K| 42 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T_CACHE | 71348 | 905K| 196 (1)| 00:00:03 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
52 recursive calls
0 db block gets
742 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並沒有嘗試生成RESULT CACHE記錄。如果修改T_CACHE2的屬性：
SQL> SET AUTOT OFF
SQL> SELECT TABLE_NAME, RESULT_CACHE
2 FROM USER_TABLES
3 WHERE TABLE_NAME LIKE 'T_CACHE%';
TABLE_NAME RESULT_
------------------------------ -------
T_CACHE2 DEFAULT
T_CACHE FORCE
SQL> ALTER TABLE T_CACHE2 RESULT_CACHE (MODE FORCE);
表已更改。
SQL> SET AUTOT ON
SQL> SELECT COUNT(*)
2 FROM T_CACHE T1, T_CACHE2 T2
3 WHERE T1.OBJECT_ID = T2.OBJECT_ID;
COUNT(*)
----------
13798
執行計劃
----------------------------------------------------------
Plan hash value: 3151804270
---------------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows |Bytes|Cost (%CPU)|Time |
-----------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 26| 239 (1)|00:00:04|
| 1| RESULT CACHE | 9ngkv4bm47t0a2u6h7zbrrpyh4 | | | | |
| 2| SORT AGGREGATE | | 1| 26| | |
|*3| HASH JOIN | |12465| 316K| 239 (1)|00:00:04|
| 4| TABLE ACCESS FULL| T_CACHE2 |12465| 158K| 42 (0)|00:00:01|
| 5| TABLE ACCESS FULL| T_CACHE |71348| 905K| 196 (1)|00:00:03|
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(TEST.T_CACHE, TEST.T_CACHE2); attributes=(single-row); name="SELECT COUNT(*)
FROM T_CACHE T1, T_CACHE2 T2
WHERE T1.OBJECT_ID = T2.OBJECT_ID"
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
187 recursive calls
0 db block gets
756 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
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT COUNT(*)
2 FROM T_CACHE T1, T_CACHE2 T2
3 WHERE T1.OBJECT_ID = T2.OBJECT_ID;
COUNT(*)
----------
13798
執行計劃
----------------------------------------------------------
Plan hash value: 3151804270
-------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows | Bytes |Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 26 | 239 (1)|00:00:04|
| 1| RESULT CACHE | 9ngkv4bm47t0a2u6h7zbrrpyh4 | | | | |
| 2| SORT AGGREGATE | | 1| 26 | | |
|*3| HASH JOIN | |12465| 316K| 239 (1)|00:00:04|
| 4| TABLE ACCESS FULL| T_CACHE2 |12465| 158K| 42 (0)|00:00:01|
| 5| TABLE ACCESS FULL| T_CACHE |71348| 905K| 196 (1)|00:00:03|
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(TEST.T_CACHE, TEST.T_CACHE2); attributes=(single-row); name="SELECT COUNT(*)
FROM T_CACHE T1, T_CACHE2 T2
WHERE T1.OBJECT_ID = T2.OBJECT_ID"
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
有了表級的RESULT CACHE，利用RESULT CACHE來提升系統效能變得更加容易和方便。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-672612/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- FileReader result 屬性
- html5之新增表單屬性HTML
- Oracle Query Result CacheOracle
- HTML5表單新增元素與屬性HTML
- Oracle 11.2.0.1 Result Cache 測試 - 12 DBMS_RESULT_CACHE管理包Oracle
- rcp新增屬性
- 淺談Oracle Result CacheOracle
- HTML5表單新增元素與屬性 （續）HTML
- KEEP POOL和CACHE屬性的區別
- class屬性的新增刪除
- 表的itl 屬性
- 聊聊Oracle 11g的Result Cache（一）Oracle
- 聊聊Oracle 11g的Result Cache（二）Oracle
- 聊聊Oracle 11g的Result Cache（三）Oracle
- 聊聊Oracle 11g的Result Cache（四）Oracle
- 為textarea新增maxlength屬性
- query result cache in oracle 11gOracle
- 12c設定RESULT_CACHE_MODE=MANUAL發生'Result Cache:RC Latch'型別的Latch Free等待型別
- Category新增weak屬性的精簡版本Go
- oracle result cache 結果集快取的使用Oracle快取
- Runtime之分類新增屬性
- css3新增屬性APICSSS3API
- 使用RESULT CACHE加速SQL查詢效率SQL
- Oracle 11.2.0.1 Result Cache 測試 - 1Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 5Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 6Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 7Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 8Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 9Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 10Oracle
- Oracle 11g新特性：Result CacheOracle
- oracle 11g result_cache分析Oracle
- 11g中關於表新增欄位default屬性研究
- PostgreSQL：表的儲存屬性SQL
- 表屬性設定
- css屬性與js中style物件的屬性對應表CSSJS物件
- 為普通Object新增類似AttachedProperty的屬性Object
- ORACLE 11g Result cache使用指南Oracle