If mutex or latch waits are high but shared and Java pool freespace is also high, consider reducing the size of the shared and/or Java pools. When this happens, it could indicate that sessions are spending time scanning the unnecessarily large list of free shared pool chunks (be very careful before you do this—the goal is to get everything in memory—so ensure that you have enough allocated). Monitor the amount of shared and Java pool freespace over time. If ample freespace is available in these pools and no ORA-04031 errors are occurring, consider reducing the sizes of these pools. Investigate when the miss ratio and sleeps are high for any of the mutex/latches in the following list:
- Row cache objects
- Library Cache
- Shared pool
- Shared Java pool
If freespace in the shared and Java pools is low, then you should consider the other tuning areas, such as increasing the shared and/or Java pools, pinning objects, and combining similar SQL statements to use bind variables. The query in this next listing helps you acquire some of these metrics. A mutex (which replaced many types of Library Cache latches other than the library load lock latch) is used to eliminate the possibility of two processes simultaneously using a common resource (while one or both are trying to change it); when one session is changing it, the second can’t view it or change it, and when one session is viewing it, the second can’t change it.
Oracle moved from latches to mutexes (mutual exclusion) in some areas of the Library Cache because mutexes are lighter weight and provide more granular concurrency than latches. Mutexes require less memory space and fewer instructions. Oracle uses mutexes instead of Library Cache latches and Library Cache pin latches to protect objects in the Library Cache. With a mutex, if I have the resource and I can’t get it after trying a specified number of times asking (spins), you sleep and try again a very short time later. Use the V$MUTEX_SLEEP view in addition to the V$LATCH query listed next to query mutex/latch information:
No issues occur in the output above (minimal sleeps). Remember that before increasing the SHARED_POOL_SIZE, you should consider whether there are any shared pool or library latch/mutex waits. Depending on what you observe, it may actually be more appropriate to reduce the size of the shared pool, for instance, if you have a sufficient amount of free shared pool memory available, a low number of reloads, and a high number of shared pool latch waits. The reason to consider reducing the shared pool in this case is that with an oversized shared pool, sessions will hold the shared pool latch slightly longer than is needed otherwise because the shared pool needs to scan a larger amount of space to determine exactly where to allocate the space it is requesting. Fixing the issues causing a shared pool issue and then ensuring it is large enough to fit all statements in memory is a key to good performance. Fix the problem and then make sure the shared pool is large enough (too many people make it way too small and problems occur)!
Leave a Reply