MEDIAS-2011

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.

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