如何理解DBMS_RESULT_CACHE管理結果緩存的包

本篇文章給大家分享的是有關如何理解DBMS_RESULT_CACHE管理結果緩存的包，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

Oracle 11g提供了DBMS_RESULT_CACHE包來查詢SQL結果緩存內容的狀態和適當地控制SQL結果緩存內容。

DBMS_RESULT_CACHE功能和存儲過程
功能/存儲過程    描述
STATUS：  返回結果緩存的當前狀態。值包括：
     ENABLED： 結果緩存是激活的。
     DISABLED：結果緩存是不可用的。
     BYPASSED：結果緩存暫時不可用。
     SYNC：    結果緩存是可用的，但是目前正與其他RAC節點重新同步。
MEMORY_REPORT： 列出結果緩存內存利用的一個概要（默認）或詳細的報表。
FLUSH：  推出整個結果緩存的內容。
INVALIDATE：  使結果緩存中一個特定對象的緩存結果無效。
INVALIDATE_OBJECT： 根據緩存ID使一特定結果緩存無效。
下面通過具體的實驗給予介紹：
一 STATUS：  返回結果緩存的當前狀態。
yang@rac1>select dbms_result_cache.status() from dual;
DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------
ENABLED
二 MEMORY_REPORT： 列出結果緩存內存利用的一個概要（默認）或詳細的報表。視圖v$result_cache_statistics 是MEMORY_REPORT相同的描述
yang@rac1>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  = 15744K bytes (15744 blocks)
Maximum Result Size = 787K bytes (787 blocks)
[Memory]
Total Memory = 12704 bytes [0.001% of the Shared Pool]
... Fixed Memory = 12704 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
yang@rac1>select * from v$result_cache_statistics;

        ID NAME                           VALUE
---------- ------------------------------ ---------------------------------------------------------------------------------
         1 Block Size (Bytes)             1024
         2 Block Count Maximum            15744
         3 Block Count Current            0
         4 Result Size Maximum (Blocks)   787
         5 Create Count Success           0
         6 Create Count Failure           0
         7 Find Count                     0
         8 Invalidation Count             0
         9 Delete Count Invalid           0
        10 Delete Count Valid             0
        11 Hash Chain Length              0
        12 Global Hit Count               0
        13 Global Miss Count              0
13 rows selected.
yang@rac1>set autotrace on
yang@rac1>select /*+ result_cache */ count(*) from yangobj;
  COUNT(*)
----------
     74484
Execution Plan
----------------------------------------------------------
Plan hash value: 362321706
------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   297   (1)| 00:00:04 |
|   1 |  RESULT CACHE       | 7uz1ww4x7gs2a6ba4qjauzt4bq |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 61204 |   297   (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=1; dependencies=(YANG.YANGOBJ); attributes=(single-row); name="select /*+ result_cache */ count(*) from yangobj"
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          9  recursive calls
          4  db block gets
       1126  consistent gets
          0  physical reads
        548  redo size
        528  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

yang@rac1>/
  COUNT(*)
----------
     74484
Execution Plan
----------------------------------------------------------
Plan hash value: 362321706
------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |   297   (1)| 00:00:04 |
|   1 |  RESULT CACHE       | 7uz1ww4x7gs2a6ba4qjauzt4bq |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 61204 |   297   (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=1; dependencies=(YANG.YANGOBJ); attributes=(single-row); name="select /*+ result_cache */ count(*) from yangobj"
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
        528  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
從視圖v$result_cache_objects中獲取CACHE_ID。
yang@rac1>select name,status,cache_id from v$result_cache_objects;
NAME                           STATUS    CACHE_ID
------------------------------ --------- -----------------------------------
YANG.YANGOBJ                   Published YANG.YANGOBJ
select /*+ result_cache */ cou Published 7uz1ww4x7gs2a6ba4qjauzt4bq
nt(*) from yangobj

三 INVALIDATE_OBJECT： 根據緩存ID--CACHE_ID使一特定結果緩存無效。
yang@rac1>exec DBMS_RESULT_CACHE.INVALIDATE_OBJECT('7uz1ww4x7gs2a6ba4qjauzt4bq');
PL/SQL procedure successfully completed.
yang@rac1>select name,status,cache_id from v$result_cache_objects;
NAME                           STATUS    CACHE_ID
------------------------------ --------- -----------------------------------
YANG.YANGOBJ                   Published YANG.YANGOBJ
select /*+ result_cache */ cou Invalid   7uz1ww4x7gs2a6ba4qjauzt4bq
nt(*) from yangobj
四 FLUSH：  清理整個結果緩存的內容。
yang@rac1>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 = 107812 bytes [0.049% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 98352 bytes [0.045% of the Shared Pool]
....... verhead = 65584 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 26 blocks
........... Used Memory = 6 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 3 blocks
................... SQL     = 2 blocks (2 count)
................... Invalid = 1 blocks (1 count)

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.35
yang@rac1>exec dbms_result_cache.flush();

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.02
yang@rac1>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 = 9460 bytes [0.004% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.02
結果緩存已被清除。

以上就是如何理解DBMS_RESULT_CACHE管理結果緩存的包，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。