【11gR2新特性】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：  返回結果快取的當前狀態。
dbms_result_cache.status() from dual;
DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------
ENABLED
二 MEMORY_REPORT： 列出結果快取記憶體利用的一個概要（預設）或詳細的報表。檢視v$result_cache_statistics 是MEMORY_REPORT相同的描述
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.
* 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.
autotrace on
/*+ 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

  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。
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使一特定結果快取無效。
DBMS_RESULT_CACHE.INVALIDATE_OBJECT('7uz1ww4x7gs2a6ba4qjauzt4bq');
PL/SQL procedure successfully completed.
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：  清理整個結果快取的內容。
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
dbms_result_cache.flush();

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.02
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
結果快取已被清除。。