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 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 "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
ttitle "Library Caches Report"
define output = lib_cache
spool &output
   gethitratio*100 gethitratio,
   pinhitratio*100 pinhitratio,
spool off
pause Press enter to continue
set pages 22 lines 80
ttitle off
undef output

Look at the example output below. All Get Hit% (gethitratio in the view) are greater than 80-90 percent.

This is the desired range. Notice that the Pin Hit% is also greater than 90%. This is also to be desired.

Library Object       Gets Get Hit%         Pins Pin Hit%  Reloads Invalid
--------------- --------- -------- ------------ -------- -------- -------
SQL AREA        5,628,059    98.31   28,080,373    99.26   20,566  20,322
TABLE/PROCEDURE 7,140,597    99.15   16,876,602    97.79   74,252       0
BODY              263,096    99.75      259,497    93.32   16,645       0
TRIGGER             5,288    96.41        5,342    92.25      147       0
INDEX             530,725    97.12      530,685    94.40        0       0
CLUSTER             3,967    98.89        4,266    98.05        0       0
OBJECT                  0   100.00            0   100.00        0       0
PIPE              792,503   100.00      792,578   100.00        0       0
8 rows selected.

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.

Proper pinning can reduce the number of objects reloaded and invalidated.

Guideline 7

If no flushing is used, increase the shared pool size to reduce reloads and invalidations and increase hit ratios.

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"
spool ddcache
   (SUM(getmisses)/SUM(gets))*100 RATIO
spool off
pause Press enter to continue
ttitle off

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:


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