Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  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,
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 “Oracle 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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter