Short note on KGX Mutexes

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:

  1. library cache latching is still needed for parsing etc, the mutexes address only the pinning issue in library cache
  2. mutexes are currently used for library cache cursors (not other objects like PL/SQL stored procs, table defs etc)
  3. As mutexes are a generic mechanism (not library cache specific) they’re used in V$SQLSTATS underlying structures too
  4. 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)
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

7 Responses to Short note on KGX Mutexes

  1. Alex says:

    Hi, Tanel. Great blog!

    Can you please explain how mutexes maintained on OS level.
    If i am not wrong, latch – it is a semaphore in OS. And mutex?

  2. tanelp says:

    Thanks Alex,

    Oracle’s internal mutexes which are used for library cache cursor pinning are just memory structures in Oracle’s SGA (like latches, but smaller). Mutex taking is done using an atomic compare-and-swap (or like) operation and mutex release is just a decrement of the mutex value as far as I know.

    They are different from OS mutexes as OS mutexes live in OS kernel space and require a system call for access, but Oracle mutexes live in SGA and do not require syscall, instead just a memory line update.

  3. heliodias says:

    Hi Tanel,
    Very good this post,
    Can you please indicate where can i learn more about it?

    And do you know what is the impact of _kks_use_mutex_pin on shared pool?

    Thanks
    Helio Dias

  4. Ricardo says:

    Hi Tanel,

    Recently I am getting into a problem due to pin S wait on X in a two-node HP RAC environment (10.2.0.3) . When I´m trying a systemdump and execute:
    oradebug setorapid …

    suddenly the wait which has stayed for several seconds (>7000) disappears with the message: ORA-00072: process “28722″ is not active.

    What could be the reason for this?

    Best regards Tanel.

  5. Tanel Poder says:

    Hi Ricardo,

    Looks like the systemstate dump crashes the target process (or the target process is so stuck somehow that it’s unable to handle the oradebug’s SIGUSR2 signal). This is when you get the “not active” messasge.

    Try systemstate dump with a lower level or hanganalyze. Also, v$mutex_sleep (idn) would give you the hash value of object waited for (which you can map to x$kglob to find the object name involved in hang)

  6. Ricardo says:

    Thank you Tanel!

    you are right (as ever ;)) Now I´m studying your notes (presentations) on the web and posts by you in forums about mutex.

    Please, If you can correct me :))

    If I have created a new child which doesn´t exist, it´s necessary to scan the child list getting the right latch which protects the chain and scan the list (the kksfbc function I think). But, is there a mutex which too protects this chain?. If there is a mutex, is this mutex (for the chain) adquired in exclusive mode?

    If the child cursor doesn´t exist it is necessary to create it, but , is the parent cursor (its particular mutex) adquired in exclusive mode?. In other words, when a new child is created, is the parent cursor mutex adquired in exclusive mode?.

    Then, a new child is created,ok. Is there when the mutex ref count gets incremented?.

    Imagine the child exists now. Is still necessary to adquire the parent cursor mutex in exclusive mode? or simply kksfbc scans the list, find it and increase the ref count.

    On the other hand, as I have understood too pin/unpin operations are only required to increase/decrease the ref count but not to allocate or to create a new child cursor. Is this right?.

    The cursor s pin wait on x appears when buffer cache and shared pool exchange memory in my system. I think that this is not the problem but the consequence of a hidden problem. Using your helpful script for mutex I could see a sentence with lot of sleeps. When querying this sentence through sql_id I could see many invalidations and child cursors. My hypotesis is that these sentences (among others with the same problem), have many child cursors not shared, the library cache increases, the shared pool increases -> there is not sufficient memory, and an exchange from different pools occurs. If there is not sufficient memory from other pools the massive reparsing for all the child cursor produces the cursor s pin wait on x.

    Tanel, sorry for all these questions. I hope you can help me …

    Thank you Tanel!

  7. Amir Riaz says:

    excellent

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>