【DB筆試面試611】在Oracle中，什麼是結果集緩存？

• 2019 年 10 月 10 日
• 筆記

題目部分

在Oracle中，什麼是結果集緩存？

♣

答案部分

結果集緩存（Result Cache）是Oracle 11g的新特性，用於存儲經常使用的SQL語句和函數的查詢結果。當相同語句再次執行的時候，Oracle就不用再次重複執行（包括掃描索引、回表、計算、邏輯讀、物理讀等操作），而是直接訪問內存得到結果。結果集緩存可以將SQL語句查詢的結果緩存在內存（SGA的Shared Pool）中，從而顯着地改進需要多次執行和查詢相同結果的SQL語句的性能。

結果集緩存的優點是可以重用相同的結果集，減少邏輯I/O，從而提高系統性能。結果集緩存最適合的是靜態表（例如只讀表），即結果集緩存最適合返回同樣結果的查詢。若SQL語句中包含的對象（例如表）做了UPDATE，INSERT，DELETE或是DDL操作，則相關的所有SQL的緩存結果集就自動失效了。所以，Result Cache只對那些在平時幾乎沒有任何DML操作的只讀表比較有用，可以減輕I/O的壓力。

在實際情況中，結果集緩存僅在少數的情況下是有效的。在以下情況中，結果集不會被緩存：

①　查詢使用非確定性的函數、序列和臨時表的結果集不會被緩存。

②　查詢違反了讀一致性時結果集將不會被緩存。

③　引用數據字典視圖的查詢的結果集不會被緩存。

④　查詢結果集大於可用緩存結果集可用空間的不會被緩存。

⑤　對依賴對象的任何改變（DML、DDL）都會使整個緩存的結果集變為無效，結果集緩存最適合那些只讀或接近只讀的表。

⑥　ADG的備庫不能使用結果集緩存。

Oracle數據庫引擎提供了3種結果集緩存，包括：服務器查詢結果集緩存、PL/SQL函數結果集緩存和客戶端結果集緩存。

（一）服務器查詢結果集緩存

服務器查詢結果集緩存由以下一些參數控制：

l RESULT_CACHE_MODE：該參數用來控制結果集緩存的操作模式。AUTO表示優化程序將根據重複的執行操作確定將哪些結果存儲在高速緩存中。MANUAL表示只有使用了RESULT_CAHCE提示的查詢或對帶有RESULT_CACHE屬性的表訪問的查詢才會被緩存，MANUAL為該參數的默認值。FORCE表示所有合適的查詢都會被緩存。對於AUTO和FORCE設置，如果語句中包含[NO_]RESULT_CACHE提示，那麼該提示優先於參數設置。

l RESULT_CACHE_MAX_SIZE：控制結果集緩存的大小，默認值取決於其它內存設置（MEMORY_TARGET的0.25%或SGA_TARGET的0.5%或SHARED_POOL_SIZE的1%）。當RESULT_CACHE_MAX_SIZE為0的時候，代表不啟用結果集緩存。需要注意的是，若將該參數的值從0修改為非0值後，則需要重啟數據庫才能啟用服務器查詢結果集緩存特性，反之亦然。

l RESULT_CACHE_MAX_RESULT：單個結果集能夠消耗的緩存的最大百分比，比這個值大的結果集將不能被緩存，默認大小為RESULT_CACHE_MAX_SIZE的5%。

l RESULT_CACHE_REMOTE_EXPIRATION：設置遠程數據庫結果集緩存過期的時間，以分鐘為單位，默認值為0，表示不緩存遠程數據庫結果集。

與結果集緩存相關的一些視圖：

l V$RESULT_CACHE_STATISTICS：列出各種緩存設置和內存使用統計數據。

l V$RESULT_CACHE_MEMORY：列出所有的內存塊和相應的統計信息。

l V$RESULT_CACHE_OBJECTS：列出所有的對象（緩存的結果和依賴的對象）和它們的屬性。

l V$RESULT_CACHE_DEPENDENCY：列出緩存的結果和依賴對象間的依賴詳情。

與結果集緩存相關的包是DBMS_RESULT_CACHE。

l STATUS函數：返回值若為DISABLED則表示沒有開啟結果集緩存；若為ENABLED則表示已經開啟並且可以使用結果集緩存；若為BYPASS則表示已經開啟結果集緩存但不可以使用結果集緩存，此時可以通過執行「EXEC DBMS_RESULT_CACHE.BYPASS(FALSE);」來使用結果集緩存。執行後如果返回值仍然是BYPASS，那麼可能是參數RESULT_CACHE_MAX_SIZE的值為0的原因。STATUS函數返回值若為SYNC則表示結果緩存是可用的，但是目前正與其它RAC節點重新同步。可以使用SQL語句「SELECT DBMS_RESULT_CACHE.STATUS FROM DUAL;」來檢查是否開啟了結果集緩存機制。

l MEMORY_REPORT存儲過程：列出結果緩存內存利用的一個概要（默認）或詳細的報表。如下所示：

SYS@orclasm > set serveroutput on  SYS@orclasm > 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  = 2816K bytes (2816 blocks)  Maximum Result Size = 140K bytes (140 blocks)  [Memory]  Total Memory = 169328 bytes [0.034% of the Shared Pool]  ... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]  ... Dynamic Memory = 163976 bytes [0.033% of the Shared Pool]  ....... Overhead = 131208 bytes  ....... Cache Memory = 32K bytes (32 blocks)  ........... Unused Memory = 25 blocks  ........... Used Memory = 7 blocks  ............... Dependencies = 2 blocks (2 count)  ............... Results = 5 blocks  ................... SQL     = 5 blocks (5 count)    PL/SQL procedure successfully completed.  

l FLUSH函數：清空整個結果緩存的內容。

l INVALIDATE函數：使結果緩存中某個特定對象的緩存結果無效。

l INVALIDATE_OBJECT函數：根據緩存ID使某個特定結果緩存無效。

可以在表級別設置RESULT_CACHE，如下所示：

ALTER TABLE HR.EMPLOYEES RESULT_CACHE(MODE FORCE);  

下面給出一個服務器查詢結果集緩存示例：

LHR@orclasm > 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                      2816K  result_cache_mode                    string                           MANUAL  result_cache_remote_expiration       integer                          0    LHR@orclasm > create table t_rc_20170611_lhr as select * from dba_objects;    Table created.      LHR@orclasm > set autotrace traceonly  LHR@orclasm > select /*+result_cache*/ * from  t_rc_20170611_lhr where object_id=10;      Execution Plan  ----------------------------------------------------------  Plan hash value: 3007689452    -------------------------------------------------------------------------------------------------  | Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT   |                            |    13 |  2691 |   312   (1)| 00:00:04 |  |   1 |  RESULT CACHE      | 4ns8fbzpm3a10agkanndg1mqbn |       |       |            |          |  |*  2 |   TABLE ACCESS FULL| T_RC_20170611_LHR          |    13 |  2691 |   312   (1)| 00:00:04 |  -------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - filter("OBJECT_ID"=10)    Result Cache Information (identified by operation id):  ------------------------------------------------------       1 - column-count=15; dependencies=(LHR.T_RC_20170611_LHR); name="select /*+result_cache*/ * from  t_rc_20170611_lhr where object_id=10"    Note  -----     - dynamic sampling used for this statement (level=2)      Statistics  ----------------------------------------------------------           24  recursive calls            0  db block gets         1226  consistent gets         1440  physical reads            0  redo size         1609  bytes sent via SQL*Net to client          519  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed    LHR@orclasm > select /*+result_cache*/ * from  t_rc_20170611_lhr where object_id=10;      Execution Plan  ----------------------------------------------------------  Plan hash value: 3007689452    -------------------------------------------------------------------------------------------------  | Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT   |                            |    13 |  2691 |   312   (1)| 00:00:04 |  |   1 |  RESULT CACHE      | 4ns8fbzpm3a10agkanndg1mqbn |       |       |            |          |  |*  2 |   TABLE ACCESS FULL| T_RC_20170611_LHR          |    13 |  2691 |   312   (1)| 00:00:04 |  -------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - filter("OBJECT_ID"=10)    Result Cache Information (identified by operation id):  ------------------------------------------------------       1 - column-count=15; dependencies=(LHR.T_RC_20170611_LHR); name="select /*+result_cache*/ * from  t_rc_20170611_lhr where object_id=10"    Note  -----     - dynamic sampling used for this statement (level=2)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets            0  consistent gets            0  physical reads            0  redo size         1609  bytes sent via SQL*Net to client          519  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed  SYS@orclasm > SELECT d.NAMESPACE,d.DEPEND_COUNT,d.SCN,d.INVALIDATIONS FROM V$RESULT_CACHE_OBJECTS d WHERE d.CACHE_ID='4ns8fbzpm3a10agkanndg1mqbn';    NAMES DEPEND_COUNT        SCN INVALIDATIONS  ----- ------------ ---------- -------------  SQL              1   66062134             0  SYS@orclasm > select * from v$result_cache_statistics;            ID NAME                                     VALUE  ---------- ---------------------------------------- -------------------           1 Block Size (Bytes)                       1024           2 Block Count Maximum                      2816           3 Block Count Current                      32           4 Result Size Maximum (Blocks)             140           5 Create Count Success                     5           6 Create Count Failure                     0           7 Find Count                               8           8 Invalidation Count                       0           9 Delete Count Invalid                     0          10 Delete Count Valid                       0          11 Hash Chain Length                        1          12 Find Copy Count                          8    

在第二次查詢的執行計劃中，發現邏輯讀已經變成0了，說明Result Cache起作用了。

（二）函數結果集緩存

Oracle數據庫用一個單獨的緩存區為每一個函數同時保存輸入和返回值。這個緩存區被連接到這個數據庫實例的所有會話共享。每當函數被調用時，數據庫就會檢查是否已經緩存了相同的輸入值。如果是，那麼函數就不用重新執行了，而是把緩存中的值簡單返回即可。每當發現要修改的是緩存所依賴的表，數據庫就會自動把緩存失效。

有兩種函數緩存機制，分別是確定性函數緩存和函數結果集緩存。對於一個函數，如果有相同的IN和IN OUT參數，且函數的返回結果也相同，那麼這個函數就是確定性的（DETERMINISTIC）。Oracle通過關鍵字DETERMINISTIC來表明一個函數是確定性的，確定性函數可以用於創建基於函數的索引。函數結果集緩存指的是Oracle通過關鍵字RESULT_CACHE對函數返回的結果進行緩存，緩存結果可以被所有會話共享。

下面給出一個函數結果集緩存的示例：

準備表：

CREATE TABLE T_FRC_20170611_LHR AS SELECT * FROM DBA_OBJECTS;  CREATE OR REPLACE FUNCTION FUN_OBJECTNAME_LHR(P_ID IN NUMBER) RETURN VARCHAR2 RESULT_CACHE AS    V_OBJECT VARCHAR2(200);  BEGIN    SELECT OBJECT_NAME INTO V_OBJECT FROM T_FRC_20170611_LHR WHERE OBJECT_ID = P_ID;    RETURN V_OBJECT;  END FUN_OBJECTNAME_LHR;  

執行查詢：

LHR@orclasm > SELECT FUN_OBJECTNAME_LHR(10) FROM DUAL;      Execution Plan  ----------------------------------------------------------  Plan hash value: 1388734953    -----------------------------------------------------------------  | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |  -----------------------------------------------------------------  |   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |  |   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |  -----------------------------------------------------------------      Statistics  ----------------------------------------------------------           30  recursive calls            0  db block gets         1216  consistent gets         1117  physical reads            0  redo size          545  bytes sent via SQL*Net to client          519  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed    LHR@orclasm > SELECT FUN_OBJECTNAME_LHR(10) FROM DUAL;      Execution Plan  ----------------------------------------------------------  Plan hash value: 1388734953    -----------------------------------------------------------------  | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |  -----------------------------------------------------------------  |   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |  |   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |  -----------------------------------------------------------------      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets            0  consistent gets            0  physical reads            0  redo size          545  bytes sent via SQL*Net to client          519  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed  SYS@orclasm > SELECT d.NAMESPACE,d.DEPEND_COUNT,d.SCN,d.INVALIDATIONS FROM V$RESULT_CACHE_OBJECTS d WHERE D.NAMESPACE='PLSQL';    NAMES DEPEND_COUNT        SCN INVALIDATIONS  ----- ------------ ---------- -------------  PLSQL            2   66067285             0    LHR@orclasm > DELETE FROM T_FRC_20170611_LHR WHERE ROWNUM<=1;    1 row deleted.  LHR@orclasm > COMMIT;    Commit complete.    LHR@orclasm >  

（三）客戶端結果集緩存

初始化參數CLIENT_RESULT_CACHE_SIZE表示所有客戶端的總緩存大小，有關客戶端結果緩存本書不再詳解，讀者可以查閱相關的官方文檔來學習。

& 說明：

有關結果集緩存的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140589/

本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。