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
   WHERE CUST_YEAR_OF_BIRTH = 1975;
   

5. Inspect the execution details of a query:

   SELECT SUBSTR(SQL_TEXT,1,30), USERS_EXECUTING,
     EXECUTIONS, LOADS, HASH_VALUE
   FROM V$SQLAREA
   WHERE SQL_TEXT like 'SELECT /* TEST */ %';
   

6. Inspect cached execution...