Skip to content
Oracle DBA Scripts & Database Utilities

Library Cache Statistics (vlibrary.sql)

What This Script Does

Section titled “What This Script Does”

This script analyzes library cache performance by:

  • Displaying statistics for each library cache namespace
  • Calculating hit ratios for gets and pins operations
  • Showing reload counts indicating cache misses
  • Providing totals for overall cache analysis

Script

Section titled “Script”
rem  vlibrary.sql
rem
ttitle 'Library Cache'
rem
col namespace format a15 heading 'NAMESPACE'
col gets format b99999999 heading 'GETS'
col gethits format b99999999 heading 'GET HITS'
col pins format b999999999 heading 'PINS'
col pinhits format b999999999 heading 'PIN HITS'
col gethitratio format b990.9 heading 'GET|HIT|RATIO'
col pinhitratio format b990.9 heading 'PIN|HIT|RATIO'
col reloads format b9999999 heading 'RELOAD'
col invalidations format b99999 heading 'INVALID'
rem
break on report
compute sum of gets gethits pins pinhits reloads on report
rem
select namespace,
       gets, gethits, gethitratio * 100 gethitratio,
       pins, pinhits, pinhitratio * 100 pinhitratio,
       reloads
  from v$librarycache
 order by namespace;

Usage

Section titled “Usage”
SQL> @vlibrary.sql

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$LIBRARYCACHE

Sample Output

Section titled “Sample Output”
Library Cache


                        GET                   PIN                   PIN
NAMESPACE           GETS     HITS  HIT     PINS     HITS  HIT    RELOAD
                                 RATIO                   RATIO
--------------- -------- -------- ----- -------- -------- ----- --------
BODY             123,456  122,890  99.5  456,789  455,234  99.7      566
CLUSTER              234      230  98.3      456      450  98.7        4
INDEX                567      560  98.8    1,234    1,220  98.9        7
OBJECT               345      340  98.6      678      670  98.8        5
PIPE                  12       12 100.0       24       24 100.0        0
SQL AREA       45,678,901 44,567,890  97.6 123,456,789 122,345,678  99.1  110,011
TABLE/PROCEDURE   12,345   12,100  98.0   34,567   34,200  99.0      245
TRIGGER              890      885  99.4    1,780    1,770  99.4        5
                -------- -------- ----- -------- -------- ----- --------
sum            45,817,450 44,705,907       123,952,317 122,839,246       110,843

Key Output Columns

Section titled “Key Output Columns”
  • NAMESPACE: Library cache namespace category
  • GETS: Number of get requests for objects
  • GET HITS: Number of successful gets (found in cache)
  • GET HIT RATIO: Percentage of successful gets
  • PINS: Number of pin requests (object execution)
  • PIN HITS: Number of successful pins
  • PIN HIT RATIO: Percentage of successful pins
  • RELOAD: Number of times objects were reloaded into cache

Understanding Library Cache Namespaces

Section titled “Understanding Library Cache Namespaces”

SQL AREA

Section titled “SQL AREA”
  • Parsed SQL statements and execution plans
  • Usually largest namespace
  • High activity area requiring monitoring

BODY

Section titled “BODY”
  • Package bodies, procedure/function code
  • Executable PL/SQL code
  • Important for application performance

TABLE/PROCEDURE

Section titled “TABLE/PROCEDURE”
  • Table and procedure metadata
  • Data dictionary information
  • Object definitions and privileges

INDEX

Section titled “INDEX”
  • Index metadata and statistics
  • Index structure information
  • Critical for query optimization

TRIGGER

Section titled “TRIGGER”
  • Trigger definitions and code
  • Fired during DML operations
  • Can impact transaction performance

Performance Analysis

Section titled “Performance Analysis”

Healthy Ratios

Section titled “Healthy Ratios”
  • Get Hit Ratio: Should be > 95%
  • Pin Hit Ratio: Should be > 95%
  • Low Reloads: Indicates stable cache

Warning Signs

Section titled “Warning Signs”
  • Get Hit Ratio < 90%: Possible shared pool sizing issues
  • High Reloads: Objects being aged out and reloaded
  • Pin Hit Ratio < 90%: Execution cache problems

Common Use Cases

Section titled “Common Use Cases”

  1. Shared Pool Tuning

    • Assess cache efficiency
    • Identify sizing requirements
    • Monitor hit ratios over time

  2. Performance Troubleshooting

    • Find cache-related bottlenecks
    • Identify excessive parsing
    • Locate memory pressure points

  3. Application Analysis

    • Validate cursor sharing
    • Check PL/SQL efficiency
    • Monitor object usage patterns

Tuning Recommendations

Section titled “Tuning Recommendations”

Low Hit Ratios

Section titled “Low Hit Ratios”

  1. Increase Shared Pool Size

    ALTER SYSTEM SET shared_pool_size = 512M;

  2. Enable Automatic Memory Management

    ALTER SYSTEM SET memory_target = 2G;

  3. Pin Frequently Used Objects

    EXEC DBMS_SHARED_POOL.KEEP('package_name', 'P');

High Reload Counts

Section titled “High Reload Counts”

  1. Review SQL Design

    • Use bind variables
    • Avoid literal values in SQL
    • Enable cursor sharing if appropriate

  2. Monitor Large Objects

    • Identify oversized packages
    • Consider code optimization
    • Review object dependencies

Advanced Analysis

Section titled “Advanced Analysis”

Find objects with high reloads:

Section titled “Find objects with high reloads:”
SELECT namespace, sum(reloads) total_reloads,
       sum(gets) total_gets,
       round(sum(reloads)/sum(gets)*100,2) reload_pct
FROM v$librarycache
GROUP BY namespace
HAVING sum(reloads) > 100
ORDER BY reload_pct DESC;
Section titled “Monitor trends over time:”
-- Create baseline snapshots
CREATE TABLE lib_cache_baseline AS
SELECT sysdate snapshot_time, namespace, gets, gethits,
       pins, pinhits, reloads
FROM v$librarycache;
Section titled “Related Scripts”