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.
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.
Before moving on to the next lesson, click the link below to read about library cache contention.
Library Cache Contention