Mutex Internals

Wenn ich versuchen wollte, das Verhalten von Mutexes genauer zu verstehen, würde ich mich wahrscheinlich bei Andrey Nikolaev informieren, der mal wieder eine Präsentation zum Thema veröffentlicht hat, die eine Menge technischer Details und Vorschläge zur Analyse und zum Tuning dieser Spinlock-Mechanismen enthält.

In seinem Blog hat der Herr Nikolaev zuletzt den Artikel Mutex waits. Part III. Contemporary Oracle wait schemes diversity veröffentlicht, in dem er das Verhalten der Mutexes in 11.2.0.2.2 und 11.2.0.3 erläutert und ein paar hidden parameters erklärt, mit denen sich Mutex Waits beeinflussen lassen. Sein Fazit lautet: "We may choose between four basic wait schemes ranging from superaggressive to having negligible CPU consumption and a variety of parameters to tune them. The next post will discuss when and how use them."

Eine schöne einführende Erklärung der Mutex Waits "cursor: pin s" und "cursor: pin S wait on X" findet man bei Gorjan Todorovski von Pythian, der die Rolle der Mutexes beim Parsen (soft oder hard) recht detailliert erklärt:

• "during the scanning of the cursor children linked-list we need to pin those children with a shared cursor mutex, so they don’t get purged from the shared pool during scanning. It obviously will be longer if the linked list we need to go through is longer. If we have a large number of a particular SQL execution which leads to JUST soft parsing, meaning we successfully reuse cursors, we can experience many brief waits on the cursor: pin S wait event."
• "When we can’t find a suitable match between the cursor children, we need to create a new one. As we said creating a new one, involves hard parsing (syntax and semantic check of the SQL, checking user privileges on objects in the SQL , creating a new exaction plan….). While we do this, we must prevent another session from creating the same type of a cursor child. We also need to prevent other sessions to get this mutex in a shared mode, since it is still not ready for being reused. So we must hold an exclusive lock on the cursor child mutex.  While holding the mutex in X mode, if other sessions are trying to execute the same SQL with the same context (cursor metadata), they will find that a child like that is there but still being held in X mode, so they will wait to get it in a shared (S) mode to reuse it while we see the session waiting on a cursor: pin S wait on X."