Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2)
Oracle Tips by Mike Ault
Monitoring Library and Data
I've spent most of this article looking at 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 area for all data dictionary related definitions.
The script in Figure 9 creates a report on the library caches. The
items of particular interest in the report generated by the script
in Figure 9 (shown in Figure 10) are the various ratios.
rem Title: libcache.sql
rem FUNCTION: Generate a library cache report
column namespace heading "Library Object"
column gets format 9,999,999 heading "Gets"
column gethitratio format 999.99 heading "Get Hit%"
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
start title80 "Library Caches Report"
define output = rep_out\&db\lib_cache
For more information on this topic, I recommend Don
Burleson's latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts:
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.