Oracle Database 11gR2 Performance Tuning Cookbook

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