Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


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

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



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

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter