Tuning Memory - Oracle Database 11gR2 Performance Tuning - page 378

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 plans:

SELECT OPERATION, OBJECT_OWNER, OBJECT_NAME
FROM V$SQL_PLAN
WHERE HASH_VALUE = 3323436660;