Mutex waits. Part III. Contemporary Oracle wait schemes diversity.

Several months have passed since my previous “mutex wait” post. I was so busy with work and conference presentations. Thanks to all my listeners at UKOUG2011, Hotsos2012 and Medias2012 conferences and several seminars for inspiring questions and conversations.

I. Unexpected change.

Now it is time to discuss how contemporary Oracle waits for mutexes. My previous posts described evolution of “invisible and aggressive” 10.2-11.1 mutex waits into fully accounted and less aggressive 11gR2 mutexes. Surprisingly Oracle 11.2.0.2.2 (or 11.2.0.2 PSU2) appeared in April 2011 demonstrated almost negligible CPU consumption during mutex waits.

Advertisement

MEDIAS-2012

A week ago I was back home from MEDIAS-2012 conference. It was held in Limassol (Cyprus), near the spectacular ruins of ancient city Amathus. This was unique style general Computer Science conference with speakers including legendary Soviet cosmonaut Alexandr Serebrov and the inventor of Mean Value Analysis Professor Martin Reiser.

In my experience the Reiser’s Law stating that “Software is getting slower more rapidly than hardware becomes faster” has been repeatedly illustrated by numerous performance problems I observe.

RDTeX presentations covered Oracle topics ranged from Data Warehousing by Mikhail Kozyr to Oracle Coherence by Alexei Zolotarev.

This conference gave me unique opportunity to discuss mathematics related to Oracle mutexes. If you are interested, you can download my presentation here.

And, of course, Cyprus is a great place to visit!

Thanks to Professor S.V. Klimenko for kindly inviting me to MEDIAS 2012 conference.
Thanks to RDTEX for financial support.
Thanks to RDTEX Technical Support Centre Director S.P. Misiura for years of encouragement and support of my investigations.

Mutex waits. Part 1. “Cursor: Pin S” in Oracle 10.2-11.1. Invisible and aggressive.

Oracle KGX mutexes appeared more than 7 years ago. However, mutex waits are still obscure. Oracle Documentation provided only brief description of mutex wait events without any information about wait durations and timeouts.
Look at the following timeline:

• In 2005 mutexes were introduced with Oracle 10.2.0.2 patchset. Documentation states that “cursor: pin S” wait event should rarely be seen.”
• In June 2008, Oracle released Patch 6904068: High CPU usage when there are “cursor: pin S” waits. Patch used “_first_spare_parameter” to tune the waits duration.

Spin tales: Part 1. Exclusive latches in Oracle 9.2-11g

How does Oracle process spin for a latch? How many times does it check the latch before going to sleep? Anyone knows. This is the _spin_count=2000. Two thousand cycles by default. Oracle established this value long ago in version 6 at the beginning of 90s. However, let me count.

My previous investigation showed that latch wait was cardinally changed in Oracle 9.2. At that time, the exponential backoff disappeared. The latches have been using completely new wait posting since 2002. We may expect that latch spin have been changed too. Controversial results of _spin_count tuning in Oracle 9.2 confirm this also. In this series of posts, I will explore how the contemporary Oracle latches spin. The first post is about exclusive latches that form the majority of Oracle latches. For example, 460 out of 551 latches are exclusive in Oracle 11.2.0.2.

I will demonstrate that exclusive latches spin 10 times more than we expected. The _spin_count occurred to be effectively static for exclusive latches, and there is a big difference between not setting _spin_count and setting it to 2000.

Oracle latches and general Spinlocks

First of all we need to describe common background. According to Oracle® Database Concepts 11g Release 2 (11.2) latch is: “A simple, low-level serialization mechanism to protect shared data structures in the System Global Area.”

Latches and mutexes are the Oracle proprietary realizations of general spinlock concept. Later I will show that Oracle latch is one of the simplest and “obsolete” spinlock – TTS plus wait. Here I will describe where (and why) latch is placed in general spinlock theory.