Waits, Enqueues, and Latches - Expert Oracle RAC Performance Diagnostics and Tuning - page 602

The following query will help determine the current number of gets and sleeps for the various mutex wait events
just described:
SELECT to_char(SYSDATE, 'HH:MI:SS') time,
kglnahsh hash,
SUM(sleeps) sleeps,
location,
mutex_type,
substr(kglnaobj, 1, 40) object
FROM x$kglob,
v$mutex_sleep_history
WHERE kglnahsh = mutex_identifier
GROUP BY kglnaobj,
kglnahsh,
location,
mutex_type
ORDER BY sleeps
Similar to latches we discussed earlier, when resources are not available, sessions requesting for a mutex also go
into a sleep mode. If a session tries to get a mutex but another session is already holding it, then the get request cannot
be granted, and the session requesting the mutex will “spin” for the mutex a number of times in the hope that it will
be quickly freed. During each spin, the session will check if the mutex has been freed. If not freed by the end of the
spinning, the session waits. 14
Conclusion
In this chapter, we looked at three primary performance areas. Oracle provides sufficient instrumentation into
these areas to help diagnose and troubleshoot the resource limitations. Performance of systems is affected by lack
of resources. When a session is unable to complete a request because it is not able to obtain the required resource,
it waits. The session waits, hoping it will get the required resources in a sufficiently fair amount of time. However,
when this does not happen, it waits for a period of time. When the wait period increases, it can begin a chain of events
causing several sessions to wait and thus slowing the entire system down. OWI is almost always sufficient to get to the
bottom of most of the performance concerns. However, other areas discussed in this chapter, such as the mutex waits,
latches, and enqueues, are all types of waits and have their own roles; and Oracle provides instrumentation to collect
statistics to support their findings. Although this chapter provides overall guidance to the various interfaces, and
more specifically toward RAC, it does not provide complete reference. Readers are advised to refer to the Oracle Wait
Interface: A Practical Guide To Performance Diagnostics & Tuning (2004, Oracle Press) by Richmond Shee, Kirtikumar
Deshpande, and K. Gopalakrishnan book from for a more detailed discussion on this subject.
14 Metalink Note # 1377998.1. “Troubleshooting: Waits for Mutex Type Events.”