如何理解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管理结果缓存的包，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。