oracle 结果缓存,Result cache结果缓存

结果缓存

结果缓存默认是可以开启的 ， 可以通过下面的方式查询其是否开启

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