I received a question on what’s the point of the use of Mutexes for Oracle cursors in library cache. For short intro, I’m pasting one of my fairly recent answers in Oracle forums about Oracle mutexes here:
In Oracle, latches and mutexes are different things and managed using different modules. KSL* modules for latches and KGX* for mutexes.
General mutex operatins require less CPU instructions than latch operations (as they aren’t as sophisticated as latches and don’t maintain get/miss counts as latches do).
But the main scalability benefit comes from that there’s a mutex structure in each child cursor handle and the mutex itself acts as cursor pin structure. So if you have a cursor open (or cached in session cursor cache) you don’t need to get the library cache latch (which was previously needed for changing cursor pin status), but you can modify the cursor’s mutex refcount directly (with help of pointers in open cursor state area in sessions UGA).
Therefore you have much higher scalability when pinning/unpinning cursors (no library cache/library cache pin latching needed, virtually no false contention) and no separate pin structures need to be allocated/maintained.
Few notes:
- library cache latching is still needed for parsing etc, the mutexes address only the pinning issue in library cache
- mutexes are currently used for library cache cursors (not other objects like PL/SQL stored procs, table defs etc)
- As mutexes are a generic mechanism (not library cache specific) they’re used in V$SQLSTATS underlying structures too
- When mutexes are enabled, you won’t see cursor pins from X$KGLPN anymore (as X$KGLPN is a fixed table based on the KGL pin array – which wouldn’t be used for cursors anymore)
