Oracle 11g新特性:Result Cache
結果集快取(Result Cache)是Oracle Database 11g新引入的功能,除了可以在伺服器端快取結果集(Server Result Cache)之外,還可以在客戶端快取結果集(Client Result Cache)。下面著重介紹一下伺服器端結果集快取。
伺服器端的Result Cache Memorey由兩部分組成。
·SQL Query Result Cache:儲存SQL查詢的結果集。
·PL/SQL Function Result Cache:用於儲存PL/SQL函式的結果集。
Oracle 通過一個新引入的初始化引數result_cache_max_size來控制該Cache的大小。如果result_cache_max_size=0則表示禁用該特性。引數result_cache_max_result則控制單個快取結果可以佔總的Server Result Cache大小的百分比。
tq@CCDB> select * from v$version where rownum < 2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Productiontq@CCDB> 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 2080K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
上面顯示的引數中result_cache_mode用於控制Server Result cache的模式,該引數有3個可選設定。
·設定auto:則優化器會自動判斷是否將查詢結果快取。
·設定manual:則需要通過查詢提示result_cache來告訴優化器是否快取結果。
·設定force:則儘可能地快取查詢結果(通過提示no_result_cache可以拒絕快取)。
下面通過測試來看一下這一新特性的使用及優勢所在,首先建立一張測試表:
tq@CCDB> create table dbtan as select * from dba_objects;
Table created.
在以前的版本中,第一次執行該SQL可以看到consistent gets和physical reads大致相同:
tq@CCDB> set autotrace on
tq@CCDB> select count(*) from dbtan;
COUNT(*)
----------
70439
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
1119 consistent gets
1036 physical reads
再次執行同樣查詢時,由於資料Cache在記憶體中,physical reads會減少到0,但是consistent gets很難降低:
tq@CCDB> select count(*) from dbtan;
COUNT(*)
----------
70439
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1041 consistent gets
0 physical reads
現在再來看看在Server Result Cache下Oracle的行為,首先在result_cache_mode引數設定為MANUAL時:
tq@CCDB> show parameter result_cache_mode
NAME TYPE VALUE
------------------------------------ -------------------- -----------------
result_cache_mode string MANUAL
需要在SQL語句中手工指定Cache,這需要通過加入一個hints來實現,這個hints是result_cache:
tq@CCDB> select /*+ result_cache */ count(*) from dbtan;
COUNT(*)
----------
70439
Execution Plan
----------------------------------------------------------
Plan hash value: 1782547706------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 |
| 1 | RESULT CACHE | g6tx53yfbxr2fah44y3vvdp4hc | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| DBTAN | 78689 | 290 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(TQ.DBTAN); attributes=(single-row); name="select /*+ result_cache */ count(*) from dbtan"
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1117 consistent gets
0 physical reads
注意到這個執行計劃已經和以往的不同,RESULT CACHE以g6tx53yfbxr2fah44y3vvdp4hc名稱建立。那麼在接下來的查詢中,這個Result Cache就可以被利用:
tq@CCDB> select /*+ result_cache */ count(*) from dbtan;
COUNT(*)
----------
70439
Execution Plan
----------------------------------------------------------
Plan hash value: 1782547706------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 |
| 1 | RESULT CACHE | g6tx53yfbxr2fah44y3vvdp4hc | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| DBTAN | 78689 | 290 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------1 - column-count=1; dependencies=(TQ.DBTAN); attributes=(single-row); name="select /*+ result_cache */ count(*) from dbtan"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
在這個利用到Result Cache的查詢中,consistent gets減少到0,直接訪問結果集,不再需要執行SQL查詢。這就是Result Cache的強大之處。
在以上的測試中,當result_cache_mode設定為MANUAL時,只有使用hints的情況下,Oracle才會利用快取結果集;而如果將result_cache_mode設定為AUTO,Oracle如果發現緩衝結果集已經存在,那麼就會自動使用。但是如果緩衝結果集不存在,Oracle並不會自動進行緩衝,只有使用HINTS的情況下,Oracle才會將執行的結果集快取。
可以通過查詢v$result_cache_memory檢視來看Cache的使用情況:
tq@CCDB> select * from v$result_cache_memory where free='NO';
ID CHUNK OFFSET FREE OBJECT_ID POSITION
---------- ---------- ---------- ------ ---------- ----------
0 0 0 NO 0 0
1 0 1 NO 1 0
2 0 2 NO 2 0
3 0 3 NO 3 0
通過V$RESULT_CACHE_STATISTICS可以查詢Result Cache的統計資訊:
tq@CCDB> select * from v$result_cache_statistics;
ID NAME VALUE
---------- ---------------------------------------- -------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2080
3 Block Count Current 32
4 Result Size Maximum (Blocks) 104
5 Create Count Success 2
6 Create Count Failure 0
7 Find Count 2
8 Invalidation Count 1
9 Delete Count Invalid 0
10 Delete Count Valid 0
V$RESULT_CACHE_OBJECTS記錄了Cache的物件:
tq@CCDB> select id,type,name,block_count,row_count from v$result_cache_objects;
ID TYPE NAME BLOCK_COUNT ROW_COUNT
---------- --------------- ---------------------------------------------- ----------- ----------
2 Dependency TQ.DBTAN 1 0
0 Dependency object-id(98106) 1 0
3 Result select /*+ result_cache */ count(*) from dbtan 1 1
1 Result select /*+ result_cache */ count(*) from t 1 1
一個新的系統包被引入,DBMS_RESULT_CACHE可以用於執行關於Result Cache的管理:
tq@CCDB> set serveroutput on
tq@CCDB> exec 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 = 169352 bytes [0.045% of the Shared Pool]
... Fixed Memory = 5296 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 164056 bytes [0.044% of the Shared Pool]
....... verhead = 131288 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 28 blocks
........... Used Memory = 4 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 2 blocks
................... SQL = 1 blocks (1 count)
................... Invalid = 1 blocks (1 count)PL/SQL procedure successfully completed.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8183550/viewspace-659599/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Query Result Cache的使用和配置--Oracle 11G新特性SQLOracle
- 11G result cache新特性的更多深入研究
- Oracle11g新特性:SQL Result Cache [zt]OracleSQL
- query result cache in oracle 11gOracle
- oracle 11g result_cache分析Oracle
- 11g新特性--result caching
- 聊聊Oracle 11g的Result Cache(一)Oracle
- 聊聊Oracle 11g的Result Cache(二)Oracle
- 聊聊Oracle 11g的Result Cache(三)Oracle
- ORACLE 11g Result cache使用指南Oracle
- 聊聊Oracle 11g的Result Cache(四)Oracle
- 11G result cache新特性的一些發現和個人見解
- Oracle 11gR2 Result Cache特性文章收集Oracle
- Oracle Query Result CacheOracle
- 【11gR2新特性】result cache 的三種模式模式
- 淺談Oracle Result CacheOracle
- Using Oracle Database 11g Release 2 Result Cache in an Oracle RAC EnvironmentOracleDatabase
- Oracle 11.2.0.1 Result Cache 測試 - 12 DBMS_RESULT_CACHE管理包Oracle
- 【ORACLE新特性】11G 分割槽新特性Oracle
- Oracle 11g 的server結果快取result_cache_modeOracleServer快取
- Oracle 11g 新特性Oracle
- oracle 11g 的新特性Oracle
- 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
- 【11gR2新特性】DBMS_RESULT_CACHE管理結果快取的包快取
- Oracle 11g 新特性簡介Oracle
- Oracle 11g 新特性之DRCPOracle
- Oracle 11g 新特性(轉載)Oracle
- Oracle 11g新特性之SecureFilesOracle
- 11g result cache 結果快取記憶體快取記憶體
- oracle DG 11g新特性彙總Oracle
- ORACLE 11G新特性之列新增操作Oracle
- Oracle 11g 新特性 -- SecureFiles 說明Oracle