SQL> SQL> show parameter RESULT_CACHE_MAX_SIZE

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

result_cache_max_size                big integer 2560K

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()

--------------------------------------------------------------------------------

ENABLED

The view V$RESULT_CACHE_STATISTICS and the PL/SQL package procedure

DBMS_RESULT_CACHE.MEMORY_REPORT display information to help you determine

the amount of memory currently allocated to the result cache.

SQL> exec  DBMS_RESULT_CACHE.MEMORY_REPORT(detailed => true) ;

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  = 2560K bytes (2560 blocks)

Maximum Result Size = 128K bytes (128 blocks)

[Memory]

Total Memory = 202536 bytes [0.103% of the Shared Pool]

... Fixed Memory = 5736 bytes [0.003% of the Shared Pool]

....... Memory Mgr = 208 bytes

....... Cache Mgr  = 416 bytes

....... Bloom Fltr = 2K bytes

....... State Objs = 3064 bytes

... Dynamic Memory = 196800 bytes [0.100% of the Shared Pool]

....... Overhead = 164032 bytes

........... Hash Table    = 64K bytes (4K buckets)

........... Chunk Ptrs    = 24K bytes (3K slots)

........... Chunk Maps    = 12K bytes

........... Miscellaneous = 164032 bytes

....... Cache Memory = 32K bytes (32 blocks)

........... Unused Memory = 7 blocks

........... Used Memory = 25 blocks

............... Dependencies = 10 blocks (10 count)

............... Results = 15 blocks

................... CDB     = 10 blocks (10 count)

................... Invalid = 5 blocks (5 count)

PL/SQL procedure successfully completed.

 
清空缓存结果集  

SQL> exec DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

 再次查询会发现下面的现象。 

SQL> exec  DBMS_RESULT_CACHE.MEMORY_REPORT(detailed => true) ;

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          = 0 bytes

Maximum Cache Size  = 0 bytes (0 blocks)

Maximum Result Size = 0 bytes (0 blocks)

[Memory]

Total Memory = 5736 bytes [0.003% of the Shared Pool]

... Fixed Memory = 5736 bytes [0.003% of the Shared Pool]

....... Memory Mgr = 208 bytes

....... Cache Mgr  = 416 bytes

....... Bloom Fltr = 2K bytes

....... State Objs = 3064 bytes

... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

 

SQL> col name for a30
SQL> set lines 1500
SQL> select * from  v$RESULT_CACHE_STATISTICS ; 


        ID NAME                           VALUE
---------- ------------------------------ ---------------------------------------------------------------------------------
         1 Block Size (Bytes)             1024
         2 Block Count Maximum            104864
         3 Block Count Current            160
         4 Result Size Maximum (Blocks)   5243
         5 Create Count Success           539
         6 Create Count Failure           25
         7 Find Count                     922
         8 Invalidation Count             535
         9 Delete Count Invalid           425
        10 Delete Count Valid             0
        11 Hash Chain Length              1
        12 Find Copy Count                922
        13 Global Hit Count               0
        14 Global Miss Count              10
        15 Latch (Share)                  0