| |
 |
|
Oracle Tips by Burleson |
Monitoring Library and Data
Dictionary Caches
I've spent most of this section discussing 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, which 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.
Source 13.11 creates a report on the library caches. The items of
particular interest in the report generated by Source 13.11 (shown
in Listing 13.12) are the various ratios.
SOURCE 13.11 Library caches
report.
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
spool &output
select namespace, gets, gethitratio*100 gethitratio,
pins, pinhitratio*100 pinhitratio, RELOADS,
INVALIDATIONS
from v$librarycache;
spool off
pause Press enter to continue
set pages 22 lines 80
ttitle off
undef output
In Listing 13.12 we see that all Get Hit% (gethitratio in the view),
except for indexes, are greater than 80 to 90 percent. This is the
desired state; the value for indexes is low because of the few
accesses of that type of object. Notice that the Pin Hit% is also
greater than 90 percent (except for indexes);this is also desirable.
The other goals of tuning this area 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 and invalidations may
occur as objects are swapped in and out of the shared pool. Proper
pinning can reduce the number of objects reloaded and invalidated.
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
