Detecting Library Cache Contention in Oracle

Detecting library cache contention is very easy with a standard Oracle script. This script will interrogate the V$SESSION_WAIT view and display the total count of all "latch free" events. In a database without contention this count will be zero.
By selecting from V$SESSION_WAIT during a slowdown period you can usually determine very accurately whether you have a problem with latching and which latch is causing the problem. If you see a large number (more than three or four) of processes waiting for the library cache or library cache pin latch, then there may be a problem.

select
   count(*) wait_count
from
   V$session_wait a,
   V$latch b
where 
   a.wait_time = 0
and
   a.event = 'latch free'
and
   a.p2 = b.latch#
and
   b.name like 'library%';
WAIT_COUNT
----------
         0

The primary cause of library cache contention is fragmentation of objects within the shared pool. We will be discussing this fragmentation later in this module, but if you are running on a system with just one or a very small number of CPUs and you have a problem with library cache latch contention, then fragmentation is almost certainly the source of the problem. To reduce fragmentation you can issue the ALTER SYSTEM FLUSH SHARED POOL command.
In the next lesson, we will discuss library cache statistics.

Whenever you experience performance problems relating to space consumption in the shared pool, you can issue the

ALTER SYSTEM FLUSH SHARED_POOL

command. If you are experiencing slow performance due to shared pool fragmentation, this can dramatically improve performance. The first time you flush the shared pool, Oracle flushes all "first-tier" (i.e. non-pinned) items from library cache. If you re-flush, Oracle will flush the second-tier memory structures, including all pinned packages. In short, if you pin all frequently-used items in packages they will not be flushed by the FLUSH command, and this will have minimal overhead.

Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the library cache requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:

1. The chunk size is larger than the required size
2. The space is contiguous
3. The chunk is available (not in use)

Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.
When the shared pool is suffering fragmentation ORA-04031 errors (when the database cannot find a contiguous piece of free memory) may occur. Also as a concequence , the allocation of a piece of free space takes more time an the performance may be affected (the "chunk allocation" is protected by a single latch called "shared pool latch" which is held during the whole operation).
However, ORA-4031 errors don't always affect the performance of the database.

If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of dynamic sql fragmenting the shared pool. This can be caused by:

1. Not sharing SQL
2. Making unnecessary parse calls (soft)
3. Setting session_cached_cursors too high
4. Not using bind variables

To reduce fragmentation you will need to address one or more of the causes described before. In general to reduce fragmentation you must analyze how the application is using the shared pool and maximize the use of sharable cursors. The following views will help you to identify non-sharable versions of SQL/PLSQL text in the shared pool:

Before moving on to the next lesson, click the link below to read about library cache contention.
Library Cache Contention