O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Tuning the Library Cache

The Library Cache is part of the Shared Pool, inside the System Global Area. In this recipe, we will see how to inspect the use of the Library Cache, and how to tune it to obtain the best performance from our database.

How to do it...

The following steps will demonstrate how to tune the Library Cache:

  1. Connect to the database as SYSDBA using SQL*Plus:
    CONNECT / AS SYSDBA
    
  2. Query the V$LIBRARYCACHE dynamic performance view:
    COL NAMESPACE FOR A20
    SELECT NAMESPACE, GETS, GETHITRATIO, PINS, PINHITRATIO,
    RELOADS, INVALIDATIONS
    FROM V$LIBRARYCACHE;
    CLEAR COL
    
  3. Calculate the library cache hit ratio:
    SELECT SUM(PINS - RELOADS)*100/SUM(PINS) AS "Hit Ratio"
    FROM V$LIBRARYCACHE;
    
  4. Execute a sample query:
    SELECT /* TEST */ COUNT(*) FROM SH.CUSTOMERS ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required