ITPub博客

【11gR2新特性】DBMS_RESULT_CACHE管理结果缓存的包

Linux操作系统 作者:达芬奇的梦 时间:2018-05-10 10:10:54 0 删除 编辑

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
结果缓存已被清除。。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31444259/viewspace-2154262/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2017-03-15

  • 博文量
    172
  • 访问量
    58014