Measure the Library Cache Hit Ratio with V$LIBRARYCACHE

calendar Nov 6, 2026 / · 9 min read · Library Cache Shared Pool v$librarycache v$sgastat  ·
Share on: linkedin copy

Measure the Library Cache Hit Ratio with V$LIBRARYCACHE

Purpose

V$LIBRARYCACHE contains statistics about library cache performance and activity, split by namespace. The script reads four counters for each namespace. They show how many times objects were pinned, how many of those pins found everything in memory, how many pins had to load the object again, and how many times objects were marked invalid.

The counters are cumulative. They reflect all library cache activity after the most recent database instance startup. A low pin hit rate or a high reload count points to repeated hard parsing. Each hard parse needs more CPU, more library cache latch gets and more shared pool latch gets.

Run the script when parse CPU is high, when sessions wait on library cache or shared pool latches, or before you change the shared pool size.

Code

1SELECT namespace, pins, pinhits, reloads, invalidations
2  FROM V$LIBRARYCACHE
3 ORDER BY namespace;

Code Breakdown

The script is one SELECT on one view, with no WHERE clause. It returns raw counters. It does not compute a ratio itself.

  • NAMESPACE: the library cache namespace. Four rows show activity for SQL statements and PL/SQL blocks: SQL AREA, TABLE/PROCEDURE, BODY and TRIGGER. Rows with other namespace values show activity for object definitions that Oracle Database uses for dependency maintenance. For most parse problems, start with SQL AREA. The column is VARCHAR2(64) in 19c. In 10g Release 1 (10.1) it was VARCHAR2(15). If you set a SQL*Plus COLUMN format for it, size it for the 64-character type.
  • PINS: the number of times a PIN was requested for objects of this namespace. An object (a cursor or a database object) must be pinned each time it is accessed in the cache, so the heap manager does not flush it out while it is in use. For SQL AREA, read PINS as a count of executions.
  • PINHITS: the number of times all of the metadata pieces of the library object were found in memory. A pin that is not a hit had to load at least part of the object.
  • RELOADS: any pin of an object that is not the first pin since the object handle was created, and that requires loading the object from disk. The object was loaded before. Now it has to be loaded again.
  • INVALIDATIONS: the total number of times objects in this namespace were marked invalid because a dependent object was modified. An invalid cursor must be parsed again on its next use.
  • ORDER BY namespace: gives the rows a fixed order, so you can run the query twice and compare the results line by line.

Computing the ratio

The library cache hit ratio is based on pins:

Library Cache Hit Ratio = sum(pinhits) / sum(pins)

Add up PINHITS and PINS across the rows, then divide. For one namespace, divide that row's PINHITS by its PINS, or read that row's PINHITRATIO column, which holds the same ratio for one namespace.

In one worked example, the SQL AREA namespace shows 21,536,413 executions, and 11,204 of them resulted in a library cache miss. The hit percentage in that example is about 99.94%. That means only 0.06% of executions resulted in reparsing.

What the script leaves out

The view has more columns than the script selects. These four sit next to the pin counters:

  • GETS: the number of times a lock was requested for objects of this namespace. It counts how many times since instance startup the system requested handles to library objects in this namespace.
  • GETHITS: the number of times an object's handle was found in memory.
  • GETHITRATIO: the ratio of GETHITS to GETS.
  • PINHITRATIO: the per-namespace pin ratio covered above.

The view also has the DLM_* columns and CON_ID. The Key Points below cover them.

A get is a handle lookup. A pin holds the object in place while it is used. Gets and pins are different events and usually have different values on the same system. This means GETHITRATIO is not the same number as the pin-based library cache hit ratio, and you cannot swap one for the other in a report or an alert threshold.

Key Points

  • Version support. Every column the script uses (NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS) is listed in the 10.1 reference and is still documented in 19c. The script runs unchanged across these releases. The width of NAMESPACE changed from 15 to 64 characters.
  • Multitenant. The 19c view has a CON_ID column, which the 10.1 view does not have. CON_ID 0 marks rows that pertain to the entire CDB, and 0 is also used for rows in a non-CDB. The script does not select CON_ID. In a CDB, add it to the select list to see which container each row belongs to.
  • RAC. The 19c view has DLM_INVALIDATIONS, the number of invalidation pings received from other instances. The script does not select it. On RAC, add it to see the invalidations that come from other instances.

Insights and Best Practices

Sample over an interval

To find a current problem, compare two readings instead of reading the totals. Run the query at the start of a busy period and again at the end. For each namespace, subtract the first PINS, PINHITS, RELOADS and INVALIDATIONS values from the second. Compute the ratio on the differences, not on the totals.

The interval result shows what the library cache did during the problem window.

Read RELOADS and INVALIDATIONS before the ratio

RELOADS should be near zero when the application reuses SQL well and the shared pool is the right size. A steady rise in RELOADS across intervals means objects are removed from the cache to make room for other objects, and then loaded again.

INVALIDATIONS should also be near zero, especially on OLTP systems during peak loads. A non-zero value means SQL that could be shared was invalidated by some operation, such as DDL. DDL on an object that cached SQL depends on invalidates that SQL and raises this counter.

Do not judge library cache health from the hit ratio alone. Check it against the hard parse rate and against latch contention in the shared pool and the library cache.

Fix parsing before adding memory

Library cache latch contention mostly comes from not using bind variables. The cause is excessive parsing of statements. If every session submits unique SQL, the hit ratio will never be good.

Use bind variables instead of literals in SQL statements whenever possible. The best practice is to write sharable SQL and keep the default CURSOR_SHARING value of EXACT. How strict to be depends on the workload:

  • OLTP: binds are mandatory.
  • Mixed use: about 99% binds.
  • Data warehouse: binds are optional.

Data warehouse applications run low-concurrency, high-resource SQL statements. For that work, unshared SQL with literal values is the recommended choice, and the shared pool can support it. A low SQL AREA ratio on a warehouse does not carry the same meaning as on an OLTP system.

A soft parse costs less than a hard parse, but only "no parse" is free. Code that parses a statement once and executes it many times does better than code that soft-parses on every call.

Size the shared pool with the advisory

When reloads stay high after the SQL is fixed, look at V$SHARED_POOL_ADVICE. It shows the estimated parse time in the shared pool for different pool sizes. The sizes range from 10% of the current shared pool size (or the amount of pinned library cache memory, whichever is higher) up to 200% of the current size. Use this estimate to decide on a new size, instead of a guess based on the ratio.

Alternatives

A monitoring script selects the same PINS, PINHITS, RELOADS and INVALIDATIONS columns as the script in this post. It also selects GETS, GETHITS, GETHITRATIO and PINHITRATIO, and it rounds both ratios to 2 places. Use it when you want the get counters and the pin counters in one report.

When to Run This

  • After an application deploy or a DDL window, to check INVALIDATIONS for the namespaces the change touched.
  • When users report slow response together with high parse CPU or library cache latch waits.
  • Before a change to SHARED_POOL_SIZE, to show whether reloads justify more memory.
  • After a third-party application release, to check whether the new code sends literals instead of binds.
  • When you take a baseline on a new instance or a new PDB, so later readings have something to compare against.

Troubleshooting Common Issues

High RELOADS. Check whether the shared pool has free memory:

1SELECT * FROM V$SGASTAT
2 WHERE name = 'free memory'
3   AND pool = 'shared pool';

If free memory is always available in the shared pool, a larger pool offers little or no benefit. A full shared pool is not a problem by itself. If RELOADS is near zero and the shared pool has a small amount of free memory, the pool is large enough to store the most frequently accessed data. If RELOADS is high and free memory stays near zero, check V$SHARED_POOL_ADVICE as described above.

High INVALIDATIONS. Look for DDL against objects that shared SQL depends on. On RAC, add DLM_INVALIDATIONS to the query to count invalidation pings from other instances.

Low SQL AREA ratios. When low SQL AREA ratios come with library cache latch contention, check for statements that do not use bind variables, the usual cause of that contention. To check, read SQL_TEXT from V$SQLAREA, replace the numbers and string literals in each statement with a placeholder, and count how many statements are then identical. A high count for one statement shows SQL that differs only in its literal values. Change those statements to use bind variables.

Ratio looks fine but latches are busy. Use the two-reading method from "Sample over an interval" above. Then check the hard parse rate, because excessive parsing drives library cache latch contention even when the cumulative ratio stays high.

References

Posts in this series