Last December I presented at Tech15, the largest Oracle conference in the Europe. Many thanks to the organizing team of this great conference.
Here is my presentation about contemporary latches and mutexes in Oracle 12.1.0.2. It includes several post-conference additions concerning experiments with latch priorities and Windows time granularity.
Last Thursday I spoke about “Latch Internals” at RuOUG-2012 conference in Moscow.
Large timeslot allowed me discuss new topics. If you are interesting, you can download the presentation and supplementary demo scripts.
The scripts are in .zip archive, rename it after downloading.
Many thanks to the RuOUG team!
To compare old and new latch mechanisms, I found useful the following illustration. Since it is hard for us, humans, to visualize milli- and microseconds, imagine “time microscope” that zooms in timed events one million times.
Alternatively, just imagine contemporary Oracle software running on 1950th style hardware.
Such microscope will magnify the microsecond to second. One real “second” will transform to one million seconds. It takes more than 11 days. Light will travel at sonic speed. Lunar rocket will crawl like snail.
(more…)
A week ago I returned from MEDIAS-2011 conference, which was held in Limassol (Cyprus). 
It was an exciting experience to speak at general Computer Science conference. This was also an opportunity to discuss topics beyond the usual scope of Oracle conferences and see non addicted to Oracle point of view.
As you may expect, my presentation was entitled “Exploring the Oracle latches”. You can download it here. The presentation contains more math and less X$ materials than usual. Also, I added several introductory slides about Oracle, its performance and tuning.
And, of course, Cyprus is a great place!
Thanks to Professor S.V. Klimenko for kindly inviting me to MEDIAS 2011 conference.
Thanks to RDTEX CEO I.G. Kunitsky for financial support.
Thanks to RDTEX Technical Support Centre Director S.P. Misiura for years of encouragement and support of my investigations.
In previous posts, I investigated how the Oracle process spins and waits for the latch. Now we need the tool to estimate when the latch acquisition works efficiently and when we need to tune it. This tool is the latch statistics. Contemporary Oracle documentation describes v$latch statistics columns as:
| Statistic: | x$ksllt column | Documentation description: | When and how it changed: |
| GETS | kslltwgt “wait gets” |
Number of times the latch was requested in willing-to-wait mode | Incremented by one after latch acquisition. Therefore protected by latch |
| MISSES | kslltwff “wait fails” |
Number of times the latch was requested in willing-to-wait mode and the requestor had to wait | Incremented by one after latch acquisition if miss occured |
| SLEEPS | kslltwsl “wait sleeps” |
Number of times a willing-to-wait latch request resulted in a session sleeping while waiting for the latch | Incremented by number of times process slept during latch acquisition |
| SPIN_GETS | ksllthst0 | Willing-to-wait latch requests which missed the first try but succeeded while spinning | Incremented by one after latch acquisition if miss but not sleep occured. Counts only the first spin |
| WAIT_TIME | kslltwtt “wait time” |
Elapsed time spent waiting for the latch (in microseconds) | Incremented by wait time spent during latch acquisition. |
| IMMEDIATE_GETS | kslltngt “nowait gets” |
Number of times a latch was requested in no-wait mode | Incremented by one after each no-wait latch get. May not be protected by latch |
| IMMEDIATE_MISSES | kslltnfa “nowait fails” |
Number of times a no-wait latch request did not succeed | Incremented by one after unsuccessful no-wait latch get. Not protected by latch |
In version 9.2 Oracle introduced new possibilities for fine grain latch tuning. Latch can be assigned to one of eight classes. Each class can have a different spin and wait policy. In addition, exclusive and shared latches behave differently.
By default, all the latches except “process allocation latch” belong to the standard class 0. In my previous posts, I discussed how the standard class exclusive and shared latches spin and wait. Now, it is the time to explore the non-standard class latch behaviors.
(more…)
My previous experiments demonstrated that, opposite to common belief, spin count for exclusive latches in Oracle 9.2-11g cannot be tuned dynamically. The _spin_count parameter is effectively static for exclusive latches. This seems to disagree with the well-known study “Using _spin_count to reduce latch contention in 11g” by Guy Harrison. The study explored how dynamic tuning of _spin_count influenced latch waits, CPU consumption and throughput. I think that there is no contradiction. Probably Guy Harrison’s experiments have been performed with the cache buffers chains latch contention. This is the shared latch.
We already know that exclusive latch in Oracle 9.2-11g uses static spin value from x$ksllclass fixed table. This spin can be adjusted by _latch_class_0 parameter. By default, the exclusive latch spins up to twenty thousand cycles.
This post will show that shared latch in Oracle 9.2-11g is governed by _spin_count value and spins upto four thousand cycles by default.
(more…)
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.
(more…)
The way an Oracle process waits for a latch. This seems trivial. Oracle 11.2 documentation explicitly states about “latch free” wait event:
“… The wait time increases exponentially and does not include spinning on the latch (active waiting). The maximum wait time also depends on the number of latches that the process is holding. There is an incremental wait of up to 2 seconds…”
In this post, I would like to show that this exponential backoff was obsoleted in Oracle 9.2. Since that time, all the Oracle versions use completely new latch wait posting mechanism and FIFO queuing for all, except one latch.
Oracle no longer uses “repeatedly spin and sleep” approach. The process spins and waits only once. The pseudo code for contemporary latch acquisition should looks like:
Immediate latch get
Spin latch get
Add the process to the queue of latch waiters
Sleep until posted
As I described in my previous post, Shared and Exclusive Oracle latches differ significantly. Shared latch behaves like enqueue. It has S and X incompatible modes. Moreover X mode serializes the shared latch. The contention for shared and exclusive latches has different patterns. This leads to different methods to tune such contentions.
But we do not know which latches are shared. Oracle never published the list of shared latches. Every time looking in the AWR or Statspack report we had to guess the type of contending latch. We only know that “cache buffer chains” latches became shared since in 9.2.
Oracle executable internally determines that latch is shared using flag hidden somewhere in x$kslld (v$latchname) structure. Google search shows that KSLLD means [K]ernel [S]ervice [L]ock [L]atch [D]escriptor. Unfortunately this shared flag was not externalized to SQL. It is possible to check the flag manually using oradebug peek or DTrace. But the flag offset is version and platform dependent. We need more systematic way to determine the latch type.
(more…)
Latch, mutex and beyond 