Shulou(Shulou.com)06/01 Report--

This article is about how to understand DBMS_RESULT_CACHE management results cache package, the editor thinks it is very practical, so share it with you to learn, hope you can get something after reading this article, say no more, follow the editor to have a look.

Oracle 11g provides the DBMS_RESULT_CACHE package to query the status of the SQL result cache content and to properly control the SQL result cache content.

DBMS_RESULT_CACHE functions and stored procedures

Function / stored procedure description

STATUS: returns the current state of the result cache. Values include:

ENABLED: the result cache is active.

DISABLED: the result cache is not available.

BYPASSED: the result cache is temporarily unavailable.

SYNC: the result cache is available, but is currently being resynchronized with other RAC nodes.

MEMORY_REPORT: a summary (default) or detailed report that lists the memory utilization of the result cache.

FLUSH: roll out the contents of the entire result cache.

INVALIDATE: invalidates the cached result of a specific object in the result cache.

INVALIDATE_OBJECT: invalidates a specific result cache based on the cache ID.

The following is introduced through specific experiments:

One STATUS: returns the current status of the result cache.

Yang@rac1 > select dbms_result_cache.status () from dual

DBMS_RESULT_CACHE.STATUS ()

ENABLED

Two MEMORY_REPORT: a summary (default) or detailed report that lists the memory utilization of the result cache. View v$result_cache_statistics is the same description as 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 (787k 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

Get the CACHE_ID from the view v$result_cache_objects.

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

Three INVALIDATE_OBJECT: invalidates a specific result cache according to the cache ID--CACHE_ID.

Yang@rac1 > exec DBMS_RESULT_CACHE.INVALIDATE_OBJECT ('7uz1ww4x7gs2a6ba4qjauzt4bqq')

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

Four FLUSH: clean up the contents of the entire result cache.

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 (104blocks)

[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)

The PL/SQL process completed successfully.

Time spent: 00: 00: 00.35

Yang@rac1 > exec dbms_result_cache.flush ()

The PL/SQL process completed successfully.

Time spent: 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 (104blocks)

[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]

The PL/SQL process completed successfully.

Time spent: 00: 00: 00.02

The result cache has been cleared.

The above is how to understand the package of DBMS_RESULT_CACHE management results cache. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.