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.
The following steps will demonstrate how to tune the Library Cache:
CONNECT / AS SYSDBA
V$LIBRARYCACHEdynamic performance view:
COL NAMESPACE FOR A20 SELECT NAMESPACE, GETS, GETHITRATIO, PINS, PINHITRATIO, RELOADS, INVALIDATIONS FROM V$LIBRARYCACHE; CLEAR COL
SELECT SUM(PINS - RELOADS)*100/SUM(PINS) AS "Hit Ratio" FROM V$LIBRARYCACHE;
SELECT /* TEST */ COUNT(*) FROM SH.CUSTOMERS ...