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 184.108.40.206.2 (or 220.127.116.11 PSU2) appeared in April 2011 demonstrated almost negligible CPU consumption during mutex waits. (more…)
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.
Week ago I was back home from the 10th Hotsos Symposium, the best performance dedicated Oracle conference in the world.
This was rare opportunity to see extraordinary presentations and discuss advanced Oracle topics. Inspired by Jonathan Lewis new book Oracle Core: Essential Internals for DBAs and Developers book I was happy to attend his Training Day.
I was lucky to speak at this jubilee Symposium about mutex internals and mutex contention.
Many thanks to the Hotsos team for this great conference.
I would like to describe how Oracle versions 18.104.22.168-22.214.171.124.1 waited for mutexes. This algorithm also appears to be used in post-126.96.36.199.2 PSUs and new 188.8.131.52 patchset as _mutex_wait_scheme=0.
My previous post demonstrated that before version 11.2:
- “Cursor: pin S” was pure wait for CPU. Long “cursor: pin S” waits indicated CPU starvation.
- Mutex contention was almost invisible to Oracle Wait Interface
- Spin time to acquire mutex was accounted as CPU time. It was service time, not waiting time.
Things changed. Mutex waits in Oracle 11.2 significantly differ from previous versions. Contemporary mutex waits are not CPU aggressive anymore, completely visible to Oracle Wait Interface and highly tunable.
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:
Oracle 184.108.40.206 contains enhancements 9282521 and 9239863 named “Library cache: mutex X” for objects highly contended for. Part I and II. These enhancements introduce new interesting possibilities to tune some types of the mutex contention.
Contention for heavily accessed objects can now be divided between multiple copies of object in the library cache. According to notes 9282521.8 and 9239863.8 describing the patches, the enhancements should be used:
When there is true contention on a specific library cache object….
Let me investigate this deeper. I will use Oracle 220.127.116.11.2 for Solaris SPARC (64-bit) on 8Cores/32Threads Sun Fire T2000. I chose this platform in order to emphasize how the enhancements work. (more…)
I like testcases. One testcase results in more understanding than ten page article or weeks of data collection. This is why we need reproducible testcases if we want to explore mutex contention. Testcases will also give me a possibility to demonstrate how to use mutex contention diagnostics tools embedded in Oracle. I will use Oracle 18.104.22.168 for Linux X86 32bit on my Dual-Core laptop in this posts. Your numbers for other Oracle versions and platforms may vary.
I. “Cursor: pin S” contention testcase:
Each time the session execute SQL operator, it needs to ‘pin’ the cursor in library cache using mutex. True mutex contention arises when the same SQL operator executes concurrently at high frequency. Therefore the simplest testcase for “Cursor: pin S” contention should look like:
for i in 1..1000000
execute immediate 'select 1 from dual where 1=2';
Many people asked me about the second part of my blog title – the mutex. This is the first post about it. Mutexes is another Oracle spinlock, which was appeared in version 10.2.0.2. Despite being known since 2005, Oracle mutex internals is still Terra incognita.
This post is inspired by several recent escalations due to mutex contention. It occurs that 22.214.171.124 patchset contains extraordinary number of mutex related changes. Some enhancements like 10411618 exist only for 126.96.36.199. The following patches even changed the mutex architecture: (more…)