SQL Query Result Cache的使用和配置--Oracle 11G新特性

raysuen發表於2017-06-14
SQLOracle
##buffer cache和result cache的區別
1 Buffer Cache中儲存的是需要訪問的資料
2 Result Cache是訪問資料的結果資料
因此Result Cache的作用比Buffer Cache有效很多

##引數含義和配置建議
#result_cache_mode
MANUAL : 表示當遇見中增加相關的hint(/*+ result_cache */)時，才使用Result Cache技術。該值為預設值。
FORCE ：表示只要有可能，所有查詢語句都將使用Result Cache技術。
AUTO ：表示最佳化程式將根據重複的執行操作確定將哪些結果儲存在快取記憶體中。

#result_cache_max_size，該引數設定Result Cache的最大容量。如果設定為0，則將關閉Result Cache功能。該引數的預設值，依賴於記憶體的管理模式和相關引數配置
memory_target設定時 : result_cache_max_size=memory_target*0.25%
sga_target設定時 : result_cache_max_size=sga_target*0.5%
shared_pool_size設定時 : result_cache_max_size=shared_pool_size*1%
該引數最大不能超過shared_pool_size的75%。

#result_cache_max_result，該引數為單個sql查詢語句設定可使用的最大Result Cache容量，預設為result_cache_max_size的5%

#result_cache_remote_expiration，該參數列示當sql語句訪問遠端資料庫物件時，允許遠端物件資料發生變化的過期時間，預設值為0，表示一旦遠端物件資料發生變化，相關查詢的Result Cache資料變為invalid。

##Result Cache的使用
/*+ result_cache */
/*+ no_result_cache */

##Result Cache的管理
SQL> desc DBMS_RESULT_CACHE
PROCEDURE BYPASS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
BYPASS_MODE                    BOOLEAN                 IN
SESSION                        BOOLEAN                 IN     DEFAULT
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER                          VARCHAR2                IN
NAME                           VARCHAR2                IN
PROCEDURE DELETE_DEPENDENCY
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER                          VARCHAR2                IN
NAME                           VARCHAR2                IN
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID                      BINARY_INTEGER          IN
PROCEDURE DELETE_DEPENDENCY
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID                      BINARY_INTEGER          IN
FUNCTION FLUSH RETURNS BOOLEAN
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM                      BOOLEAN                 IN     DEFAULT
RETAINSTA                      BOOLEAN                 IN     DEFAULT
GLOBAL                         BOOLEAN                 IN     DEFAULT
PROCEDURE FLUSH
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM                      BOOLEAN                 IN     DEFAULT
RETAINSTA                      BOOLEAN                 IN     DEFAULT
GLOBAL                         BOOLEAN                 IN     DEFAULT
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER                          VARCHAR2                IN
NAME                           VARCHAR2                IN
PROCEDURE INVALIDATE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER                          VARCHAR2                IN
NAME                           VARCHAR2                IN
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID                      BINARY_INTEGER          IN
PROCEDURE INVALIDATE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID                      BINARY_INTEGER          IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
ID                             BINARY_INTEGER          IN
PROCEDURE INVALIDATE_OBJECT
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
ID                             BINARY_INTEGER          IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID                       VARCHAR2                IN
PROCEDURE INVALIDATE_OBJECT
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID                       VARCHAR2                IN
PROCEDURE MEMORY_REPORT
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
DETAILED                       BOOLEAN                 IN     DEFAULT
FUNCTION STATUS RETURNS VARCHAR2

#查詢Result Cache的狀態
SQL> select DBMS_RESULT_CACHE.status from dual;

STATUS
--------------------------------------------------------------------------------
ENABLED

SQL>

#Result Cache的使用情況
SQL> set serveroutput on
SQL> 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  = 37056K bytes (37056 blocks)
Maximum Result Size = 1852K bytes (1852 blocks)
[Memory]
Total Memory = 169328 bytes [0.015% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.000% of the Shared Pool]
... Dynamic Memory = 163976 bytes [0.014% of the Shared Pool]
....... Overhead = 131208 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL     = 1 blocks (1 count)

PL/SQL procedure successfully completed.

SQL>

#清空 Result Cache
SQL> exec DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

SQL> 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          = 0 bytes
Maximum Cache Size  = 0 bytes (0 blocks)
Maximum Result Size = 0 bytes (0 blocks)
[Memory]
Total Memory = 5352 bytes [0.000% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.000% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

SQL>

#將指定表的Result Cache設定為invalid
SQL> exec DBMS_RESULT_CACHE.INVALIDATE('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL>

##Result Cache的相關檢視

(g)v$result_cache_statistics : 該檢視顯示Result Cache設定和該記憶體使用情況的統計資訊
SQL> desc v$result_cache_statistics
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(128)
VALUE                                              VARCHAR2(81)

SQL>

(g)v$result_cache_memory : 該檢視顯示Result Cache 所有記憶體卡和相關統計資訊
SQL> desc v$result_cache_memory
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
CHUNK                                              NUMBER
OFFSET                                             NUMBER
FREE                                               VARCHAR2(3)
OBJECT_ID                                          NUMBER
POSITION                                           NUMBER

SQL>

(g)v$result_cache_objects : 該檢視顯示Result Cache 中被快取的物件，包括結果集資料和依賴的表及相關屬性資料。
SQL> desc v$result_cache_objects
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
TYPE                                               VARCHAR2(10)
STATUS                                             VARCHAR2(9)
BUCKET_NO                                          NUMBER
HASH                                               NUMBER
NAME                                               VARCHAR2(128)
NAMESPACE                                          VARCHAR2(5)
CREATION_TIMESTAMP                                 DATE
CREATOR_UID                                        NUMBER
DEPEND_COUNT                                       NUMBER
BLOCK_COUNT                                        NUMBER
SCN                                                NUMBER
COLUMN_COUNT                                       NUMBER
PIN_COUNT                                          NUMBER
SCAN_COUNT                                         NUMBER
ROW_COUNT                                          NUMBER
ROW_SIZE_MAX                                       NUMBER
ROW_SIZE_MIN                                       NUMBER
ROW_SIZE_AVG                                       NUMBER
BUILD_TIME                                         NUMBER
LRU_NUMBER                                         NUMBER
OBJECT_NO                                          NUMBER
INVALIDATIONS                                      NUMBER
SPACE_OVERHEAD                                     NUMBER
SPACE_UNUSED                                       NUMBER
CACHE_ID                                           VARCHAR2(93)
CACHE_KEY                                          VARCHAR2(93)
DB_LINK                                            VARCHAR2(3)
CHECKSUM                                           NUMBER

SQL>

(g)v$result_cache_dependency : 該檢視顯示結果集資料和依賴表的關聯關係
SQL> desc v$result_cache_dependency
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
RESULT_ID                                          NUMBER
DEPEND_ID                                          NUMBER
OBJECT_NO                                          NUMBER

SQL>

##Result Cache相關技術點
#Result Cache與RAC
RAC支援Result Cache技術。
RAC環境中的每個例項都有自己的Result Cache，每個例項的Result Cache不能共享。即儲存在Result Cache中的資料只能被本例項的應用訪問。
但是，一旦保持在某個Result Cache中的資料變成invalid，則整個RAC環境中的各個Result Cache中的該資料豆漿變成invalid。
ORACLE透過專門的RCBG程式，處理RAC環境下Result Cache之間的資料同步。

#Result Cache與並行處理
並行處理也支援Result Cache技術。在並行查詢中，整個查詢結果集將被儲存在Result Cache中，也就是說，整個並行查詢語句方可使用Result Cache中的查詢結果，單個並行查詢子程式無法訪問Result Cache。
在RAC環境下，並行查詢結果儲存在查詢協調程式(query coordinator)所在市裡的Result Cache中。

#Result Cache的侷限
1 系統臨時表(Temporary Table)和資料字典寶不支援Result Cache技術
2 非確定的(Nodeterministic) PL/SQL 函式不支援Result Cache技術
3 查詢語句若出現序列的currval、nextval，則不支援Result Cache技術
4 查詢語句若出現current_date()、sysdate()、sys_guid()等函式，則不支援Result Cache技術

##Result Cache的其他技術點
#Result Cache支援flashback查詢
#Result Cache不會自動釋放記憶體，oracle將一直使用該記憶體到最大限額值，然後透過FIFO技術釋放相關記憶體。也可透過DBMS_RESULT_CACHE.FLUSH情況Result Cache記憶體
#Result Cache支援繫結變數。針對繫結變數語句，Result Cache不僅儲存結果集，二期儲存相關變數值。這樣只有相同變數值的查詢語句，方可使用其在Result Cache中的結果集


##客戶端Result Cache技術
#客戶端引數
client_result_cache_size : 該引數定義單個客戶端程式所使用的Result Cache最大值。如果設定為0，則關閉Result Cache客戶端技術。該引數預設值為0
client_result_cache_lag: 該引數定義客戶端與伺服器端最後一次往返時間閥值，在該時間閥值內，客戶端將向服務端查詢客戶端Result Cache中的資料是否發生變化，該引數的預設值為3000毫秒。即每3秒，oracle將客戶端Result Cache的資料與服務端進行一次同步

#客戶端Result Cache配置
在sqlnet.ora中設定
oci_client_result_cache_max_size;
oci_client_result_cache_max_rset_size;
oci_client_result_cache_max_rset_rows;


##PL/SQL使用Result Cache
create or replace function ProductName
(varName varType)
return nvarchar2
Result_Cache relies_on (table_name)
is
variables_name varTytpe;
begin
end;
/
函式宣告部分增加Result_Cache關鍵字，並且可增加relies_on短語，表示函式依賴後面的括號裡面的表。當依賴表的資料發生變更時，該函式的結果集將變為invalid。


內容整理自：羅敏 <<感悟oracle核心技術>>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28572479/viewspace-2140708/，如需轉載，請註明出處，否則將追究法律責任。

相關文章