RelationalDBDesign 





Tuning Instance   «Prev  Next»
Lesson 7 Detecting Library Cache Contention
Objective Detect library cache contention.

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.

Library Cache Contention

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