Library Cache Performance Tuning

V$LIBRARYCACHE
Collecting Library Cache Statistics
Interpretation
Tuning Suggestions
Allocating More Memory
Write Identical SQL Statements
Speeding Access to Shared SQL Areas on Execution Calls
Caching Session Cursors
The statistics found in the v$librarycache table reflect all library cache activity since the most recent instance
startup. By default, this table is only available to the user SYS and to users granted SELECT ANY TABLE
system privilege, such as SYSTEM.
Each row in this table contains statistics for a specific item kept in the library cache. The item described by each
row is identified by the value of the NAMESPACE column. Rows of the table with the following NAMESPACE
values reflect library cache activity for SQL statements and PL/SQL blocks:

'SQL AREA'

'TABLE/PROCEDURE'

'BODY'

'TRIGGER'
Rows with other NAMESPACE values reflect library cache activity for object definitions that Oracle uses for
dependency maintenance. The following columns of the V$LIBRARYCACHE table reflect library cache misses
on execution calls:

PINS This column shows the number of times an item in the library cache was executed.
RELOADS This column shows the number of library cache misses on execution steps.

Note: If you get a chance take a look at the SNMP . Its pretty cool.
Collecting Library Cache Statistics
To collect statistics query the V$LIBRARYCACHE table over a period of time with this query:
SELECT SUM(pins) "Executions",
SUM(reloads) "Cache Misses while Executing"
FROM v$librarycache;
Executions Cache Misses while Executing
---------- ----------------------------
320871 549
Interpreting the V$LIBRARYCACHE Table
Examining the data returned by the sample query leads to these observations:

The sum of the PINS column indicates that SQL statements, PL/SQL blocks, and object definitions were
accessed for execution a total of 320,871 times.

The sum of the RELOADS column indicates that 549 of those executions resulted in library cache misses
causing Oracle to implicitly reparse a statement or block or reload an object definition because it had aged
out of the library cache.

The ratio of the total RELOADS to total PINS is about 0.17%. This value means that only 0.17% of
executions resulted in reparsing.
Total RELOADS should be near 0. If the ratio of RELOADS to PINS is more than 1%, then you should reduce
these library cache misses through the means discussed in the next section.
Reducing Library Cache Misses
You can reduce library cache misses by

Allocating Additional Memory for the Library Cache
You may be able to reduce library cache misses on execution calls by allocating additional memory for the
library cache. To ensure that shared areas remain in the cache once their SQL statements are parsed,
increase the amount of memory available to the library cache until the V$LIBRARYCACHE.RELOADS
value is near 0. To increase the amount of memory available to the library cache, increase the value of the
initialization parameter SHARED_POOL_SIZE. The maximum value for this parameter depends on your
operating system. This measure will reduce implicit reparsing of SQL statements and PL/SQL blocks on
execution. To take advantage of additional memory available for shared SQL areas, you may also need to
increase the number of cursors permitted for a session. You can increase this limit by increasing the value
of the initialization parameter OPEN_CURSORS. Be careful not to induce and swapping by
allocating too much memory for the library cache. The benefits of a library cache large enough to avoid
cache misses can be partially offset by reading shared SQL areas into memory from disk whenever you
need to access them.

Writing Identical SQL Statements
You may be able to reduce library cache misses on parse calls by ensuring that SQL statements and
PL/SQL blocks share a shared SQL area whenever possible. For two different occurrences of a SQL
statement or PL/SQL block to share a shared SQL area, they must be identical according to these criteria:
The text of the SQL statements or PL/SQL blocks must be identical, character for character,

including spaces and case. For example, these statements cannot use the same shared SQL area:
SELECT * FROM emp;
SELECT * FROM emp;
These statements cannot use the same shared SQL area:
SELECT * FROM emp;
SELECT * FROM Emp;

References to schema objects in the SQL statements or PL/SQL blocks must resolve to the same object in
the same schema. For example, if the schemas of the users BOB and ED both contain an EMP table and
both users issue the following statement, their statements cannot use the same shared SQL area:
SELECT * FROM emp;
SELECT * FROM emp;
If both statements query the same table and qualify the table with the schema, as in the following
statement, then they can use the same shared SQL area: SELECT * FROM bob.emp;

Bind variables in the SQL statements must match in name and datatype. For example, these statements
cannot use the same shared SQL area: SELECT * FROM emp WHERE deptno = :department_no;
SELECT * FROM emp WHERE deptno = :d_no;

The SQL statements must be optimized using the same optimization approach and, in the case of the costbased
approach, the same optimization goal. For information on optimization approach and goal, see
Chapter 9, "Tuning SQL Statements". Shared SQL areas are most useful for reducing library cache misses
for multiple users running the same application. Discuss these criteria with the developers of such
applications and agree on strategies to ensure that the SQL statements and PL/SQL blocks of an application
can use the same shared SQL areas:

Use bind variables rather than explicitly specified constants in your statements whenever possible. For
example, the following two statements cannot use the same shared area because they do not match
character for character: SELECT ename, empno FROM emp WHERE deptno = 10; SELECT ename,
empno FROM emp WHERE deptno = 20; You can accomplish the goals of these statements by using the
following statement that contains a bind variable, binding 10 for one occurrence of the statement and 20 for
the other: SELECT ename, empno FROM emp WHERE deptno = :department_no; The two occurrences of
the statement can use the same shared SQL area.

Be sure that users of the application do not change the optimization approach and goal for their
individual sessions. You can also increase the likelihood that SQL statements issued by different
applications can share SQL areas by establishing these policies among the developers of these applications:

Standardize naming conventions for bind variables and spacing conventions for SQL statements and
PL/SQL blocks.

Use stored procedures whenever possible. Multiple users issuing the same stored procedure automatically
use the same shared PL/SQL area. Since stored procedures are stored in a parsed form, they eliminate
runtime parsing altogether.
Speeding Access to Shared SQL Areas on Execution Calls
If you have no library cache misses, you may still be able to speed execution calls by setting the value of
the initialization parameter CURSOR_SPACE_FOR_TIME. This parameter specifies when a shared SQL
area can be deallocated from the library cache to make room for a new SQL statement. The default value of
this parameter is FALSE, meaning that a shared SQL area can be deallocated from the library cache
regardless of whether application cursors associated with its SQL statement are open. The value of TRUE
means that a shared SQL area can only be deallocated when all application cursors associated with its
statement are closed. Depending on the value of CURSOR_SPACE_FOR_TIME, Oracle behaves
differently when an application makes an execution call. If the value is FALSE, Oracle must take time to
check that a shared SQL area containing the SQL statement is in the library cache. If the value is TRUE,
Oracle need not make this check because the shared SQL area can never be deallocated while an
application cursor associated with it is open. Setting the value of the parameter to TRUE saves Oracle a
small amount of time and may slightly improve the performance of execution calls. This value also
prevents the deallocation of private SQL areas until associated application cursors are closed.
Do not set the value of CURSOR_SPACE_FOR_TIME to TRUE if there are library cache misses on
execution calls. Such library cache misses indicate that the shared pool is not large enough to hold the
shared SQL areas of all concurrently open cursors. If the value is TRUE and there is no space in the shared
pool for a new SQL statement, the statement cannot be parsed and Oracle returns an error saying that there
is no more shared memory. If the value is FALSE and there is no space for a new statement, Oracle
deallocates an existing shared SQL area. Although deallocating a shared SQL area results in a library cache
miss later, it is preferable to an error halting your application because a SQL statement cannot be parsed.
Do not set the value of CURSOR_SPACE_FOR_TIME to TRUE if the amount of memory available to
each user for private SQL areas is scarce. This value also prevents the deallocation of private SQL areas
associated with open cursors. If the private SQL areas for all concurrently open cursors fills the user's
available memory so that there is no space to allocate a private SQL area for a new SQL statement, the
statement cannot be parsed and Oracle returns an error indicating that there is not enough memory.
Caching Session Cursors
If an application repeatedly issues parse calls on the same set of SQL statements, the reopening of the
session cursors can affect system performance. Session cursors can be stored in a session cursor cache.
This feature can be particularly useful for applications designed using Oracle Forms because switching
between forms closes all session cursors associated with a form.
Oracle uses the shared SQL area to determine if more than three parse requests have been issued on a given
statement. If so, Oracle assumes the session cursor associated with the statement should be cached and
moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the
same session will then find the cursor in the session cursor cache.
To enable caching of session cursors, you must set the initialization parameter
SESSION_CACHED_CURSORS. This parameter is a positive integer that specifies the maximum number
of session cursors kept in the cache. A least recently used (LRU) algorithm ages out entries in the session
cursor cache to make room for new entries when needed.
You can also enable the session cursor cache dynamically with the ALTER SESSION SET
SESSION_CACHED_CURSORS command.
To determine whether the session cursor cache is sufficiently large for your instance, you can examine the
session statistic "session cursor cache hits" in the V$SESSTAT view. This statistic counts the number of
times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of
the total parse call count for the session, you should consider setting SESSION_CACHED_CURSORS to a
larger value.