Hotsos 2011 Symposium follow-up

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.

Latch statistics

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

Hotsos Symposium 2011

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!

Exclusive latches in memory. Oracle versions 7-11.2

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>

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

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!

Latch get “where” and “why”.

It is widely known that the Oracle server uses kslgetl – Kernel 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?

10th anniversary of Steve Adams book

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.

Hello World!

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

Why?

Once, I had 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…