Oracle v$librarycache script
Chapter 2 Inside the
Oracle Shared Pool
By monitoring not only disk reads, but other
performance characteristics such as CPU and total elapsed time, the
DBA gets a full picture of SQL code usage and problem areas. Notice
how the script calculates the average value per execution for the
aggregated values stored in v$sqlarea.
Monitoring Library and Data Dictionary Caches
Much of this chapter has discussed the shared
SQL area of the shared pool. Let's wrap up with a high-level look at
the library and data dictionary caches. The library cache area is
monitored via the v$librarycache view and
contains the SQL area, PL/SQL area, table, index, and cluster cache
areas. The data dictionary caches contain cache areas for all data
dictionary related definitions.
The script below generates a report on the
library caches. The items of particular interest in the report are
the various ratios, which are shown further down.
rem FUNCTION: Generate a library cache report
column namespace heading
column gets format 9,999,999
gethitratio format 999.99 heading "Get Hit%"
Look at the example
output below. All Get Hit% (gethitratio in the view) are greater
than 80-90 percent.
column pins format 9,999,999 heading "Pins"
column pinhitratio format 999.99 heading "Pin Hit%"
column reloads format 99,999 heading "Reloads"
column invalidations format 99,999 heading "Invalid"
column db format a10
set pages 58 lines 80
ttitle "Library Caches Report"
define output = lib_cache
pause Press enter to continue
set pages 22 lines 80
This is the desired range. Notice that the Pin
Hit% is also greater than 90%. This is also to be desired.
Object Gets Get Hit% Pins Pin Hit% Reloads Invalid
The other tuning goals
are to reduce reloads to as small a value as possible (this is done
by proper sizing and pinning), and to reduce invalidations.
Invalidations happen when, for one reason or another, an object
becomes unusable. However, if you must use flushing of the shared
pool reloads, invalidations may occur as objects are swapped in and
out of the shared pool.
--------------- --------- -------- ------------ -------- --------
SQL AREA 5,628,059 98.31 28,080,373 99.26 20,566
TABLE/PROCEDURE 7,140,597 99.15 16,876,602 97.79
BODY 263,096 99.75 259,497 93.32
TRIGGER 5,288 96.41 5,342 92.25
INDEX 530,725 97.12 530,685 94.40
CLUSTER 3,967 98.89 4,266 98.05
OBJECT 0 100.00 0 100.00
PIPE 792,503 100.00 792,578 100.00
8 rows selected.
Proper pinning can reduce the number of objects
reloaded and invalidated.
If no flushing is used, increase the shared
pool size to reduce reloads and invalidations and increase hit
The data dictionary caches used to be tuned
individually through several initialization parameters. Now they are
internally controlled. The script below can be used to monitor the
overall hit ratio for the data dictionary caches.
ttitle "DD Cache Hit Ratio"
pause Press enter to continue
Here is the output from the script above
The reported ratio should always be less than
1. The ratio corresponds to the number of times out of 100 that the
database engine searched the cache without finding anything. A
dictionary cache miss is more expensive than a data block buffer
miss, so if the ratio approaches 1, increase the size of the shared
pool. If the ratio is close to 1, the internal algorithm isn't
allocating enough memory to the data dictionary caches.
The Oracle script collection contains more than 600 working Oracle scripts. You can download them immediately at this link: