Latch, mutex and beyond

March 15, 2011

Hotsos 2011 Symposium follow-up

Filed under: Uncategorized — andreynikolaev @ 9:58 pm

Just returned from the Hotsos 2011 Symposium. It was an exciting experience to be speaker at this legendary conference. Many thanks to the Hotsos team for this opportunity.
The Symposium and the presentations were inspiring. I hope my presentation was interesting too. It had to be concise because of one hour time limit. Usually these topics discussed during almost half of my training day. I will cover the topics more detaily in future posts.

February 23, 2011

Latch statistics

Filed under: DTrace,Instrumentation,Latch,Latch Statistics,Theory,Uncategorized — andreynikolaev @ 10:36 am

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

(more…)

December 16, 2010

Hotsos Symposium 2011

Filed under: Uncategorized — andreynikolaev @ 9:33 pm

I will be speaking at Hotsos Symposium 2011 which will be held on March 6 — 10, 2011 in Dallas.

Of course, I will speak about “Contemporary Latch Internals“. I would like to post my abstract here:

“Latches in Oracle Database were radically changed during the last 10 years to achieve higher levels of concurrency and performance. Using oradebug and DTrace, this presentation explores how the latch works. Contemporary latch does not use exponential backoff, most of them spin 10 times more then expected, and they may wait for an infinite time in a queue.

This presentation will show how Oracle instruments the latch operations, discuss parameters and statistics related to latch performance diagnostics, and fine grain tuning. It will also explore the long-standing question: when and how to tune the “_spin_count” and “_latch_classes“. DTrace provides additional capabilities to measure effectiveness and statistical properties of latch in production.”

Some parts of this abstract were appeared in my blog and discussed in seminars. There will be many new topics!
The Hotsos Symposium is the unique conference dedicated to Oracle performance. Hopefully to see you there!

August 24, 2010

Exclusive latches in memory. Oracle versions 7-11.2

Filed under: 11.2,Latch,Uncategorized — andreynikolaev @ 8:54 am
Tags:

Contrary to popular believe Oracle latches were significantly evolved through the last decade. Not only additional statistics appeared (and disappeared) and new (shared)  latch type was introduced,  the latch  itself was changed

It is interesting to see how the latch was organized in the past and contemporary  versions.

To see the latch in-memory seems hard, since latches typically held for a small amount of time. Hopefully Oracle gives us a possibility to call any its kernel function using oradebug call utility. We only need to know that Oracle itself uses kslgetl(laddr, wait, why, where) function to acquire the exclusive latch. Recently I blogged about this function and its parameters.

This function can be also used to artificially acquire any latch for demonstration by:


SQL> oradebug call kslgetl <latch address> <wait> <why> >where>

(more…)

July 29, 2010

Strange “db file async I/O submit” wait event

Filed under: 11.2,Uncategorized — andreynikolaev @ 5:05 pm

This post will not be directly related to the blog theme. I would like to discuss “db file async I/O submit” wait event. This new event was introduced in Oracle 11.2. So far it have not been described in Oracle documentation and Metalink.

At the beginning of this story, this event became the topmost background wait for one production instance under HP-UX:


                                                             Avg
                                        %Time Total Wait    wait    Waits   % bg
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
db file async I/O submit        151,159     0     35,625     236      0.7   96.3
log file parallel write         427,728     0        308       1      2.0     .8
...

This looks mystique. HP-UX not supports AIO for filesystem at all!
(more…)

April 12, 2010

Latch get “where” and “why”.

Filed under: Uncategorized — andreynikolaev @ 7:48 pm

It is widely known that the Oracle server uses kslgetlKernel Service Lock Management Get Latch function to acquire the latch. In 2006 Tanel Poder first demonstrated that  oradebug call  kslgetl/kslfre can be used to acquire the latch manually. This is very useful  to simulate latch related hangs and contention.

For several years it was commonly supposed that kslgetl() has two parameters – latch address and wait. But on AIX 5L we have unique procstack tool. This tool shows the actual number of parameters for function. It occurs that kslgetl has four parameters:

...
sskgpwwait(??, ??, ??, ??, ??) + 0x38
skgpwwait(??, ??, ??, ??, ??) + 0xbc
kslges(??, ??, ??, ??, ??) + 0x54c
kslgetl(??, ??, ??, ??) + 0x33c
ksfglt(??, ??, ??, ??, ??) + 0x198
...


We do know the meaning the first two of parameters. What about the others? (more…)

October 3, 2009

10th anniversary of Steve Adams book

Filed under: Uncategorized — andreynikolaev @ 10:10 pm

Famous Steve Adams book ‘Internal Oracle 8i Services for Latch, Locks and Enqueues’ was published 10 years ago on October 1999. This book founded new era of Oracle performance tuning and internals research.

The book opened for us the unknown world of Oracle Internals. Even now, after the decade it is an ultimate reference for many Oracle latch and lock related topics. For ten years we fight with the latch concurency using Steve Adams recipes. There were even rumors that this book was used inside Oracle as the manual. In particular, Oracle University Data Server Internal Seminars, has a lot of parallelism with this book. Everyone who seriously dive into this topic always should read it.

But the Oracle latch technologies were evolved greatly during the last decade! We need the contemporary knowledge.

June 16, 2009

Hello World!

Filed under: Uncategorized — andreynikolaev @ 6:16 pm

First of all, I would like to describe the goals of this blog.
I will not try to cover all variety of interesting Oracle related topics. Instead, I will almost exclusively focus on Oracle spinlocks – latches and mutexes.

Why?

Once, I have been asked before my seminar:
“I doubt that anything new can be said about such well known topic as Oracle latch”.
But I always think that the latch is one of the most mysterious and underestimated Oracle beasts.
To discuss it, I decided to start this very specialised blog…

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 64 other followers