Re: cache buffer chains/where in code

From: Christo Kutrovsky <>
Date: Wed, 9 Dec 2009 22:04:15 -0500
Message-ID: <>

Hi All, an update on this issue. I am actively working on this trying to find and eliminate the cause.

I've separated the CPUs on which Oracle runs. I've assigned it 32 of the 64 threads. Comparable to 8 'real' CPUs. I have 3 applications connecting to this database. Two are remote (same type, different servers) and are read only, 1 is local and is read/write. The remote applications are C++, the local is java. The appications are "driven" via a java test suite simulating users.

For whatever reason, at some random point BOTH remote applications send a storm of requests at a specific instant. This is observed via very deep run queue (400-600 via vmstat) and matching v$active_session_history data.

When this happens, there are 2 scenarios, with one of them occuring most times.
- high user mode CPU with corresponding "cursor: pin S" waits and many sessions on the CPU. This occurs rarelly. - high system (kernel mode) CPU (100% of all assigned threads), which either missed samples in v$active_sesssion_history or cache buffer chain waits.

What I am after is the second scenario, high kernel mode CPU. At this point I will not be addressing the reason for a storm of connections nor reducing the number of connections.

When this high kernel mode CPU is happening, mpstat is reporting a very high number of smtx - spins on mutexes. Aggregated over all 32 threads (virtual CPUs) it's in the 1-2 million range. During "normal" processing it's in the 1-2 thousand range. This "condition" happens for 5-8 seconds at most.

Now I have to find a way to do long term reporting on "lockstat -C" and time/graph where it occurs. At this point I am considering OS bugs as well.

For those interested in how I am collecting and plotting the stats, i used this simplistic approach:

nohup vmstat 1 | perl -ne '/kthr|r/ || print join("\t",scalar(localtime()),split),"\n"' > vmstat_1.log & nohup mpstat -a 1|perl -ne '/^SET/ || print join("\t",scalar(localtime()),split),"\n"' > mpstat_a1.log & nohup mpstat 5|perl -ne '/^CPU/ || print join("\t",scalar(localtime()),split),"\n"' > mpstat_5.log &

And then directly copy/paste into EXCEL. The new version has some very pretty conditional formating - shows relative 'bars' inside the cells and makes thing really easy to spot.

If the list is interested I will keep posting updates. Any ideas are also welcome.

On Sat, Nov 28, 2009 at 2:21 PM, Martin Berger <>wrote:

> It's verry interresting to follow this thread, as it seems there are 2
> discussions:
> Christo wants to know why this particular latch (and only this one) causes
> problems at big load (maybe even not at such big system-load, this is not
> clear to me right now);
> but Greg warns to consider the special 'features' of a CMT system regarding
> general CPU-queues in a OLTP system.
> To hunt Crhistos hot latch, I'd follow Tanels suggestions (LatchprofX) and
> more or less ordinary tuning methods.
> I total agree with Gregs considerations, I just like to see valid numbers
> (response times in relation to any of the possible 'processors') - this
> might enable us to pinpoint gregs 65% to dedicated numbers (processes,
> transactions, whatever).
> If I'm totally wrong, please tell me; otherwise I'll follow this
> conversations (both) with high interrest!
> Am 28.11.2009 um 18:05 schrieb Greg Rahn:
> Given that config, I'd say that system is has at over 4X the amount of
>> db connections it probably should (and needs to work well) - I'd back
>> it down to 64 as a staring point and make sure the connection pool
>> does not grow. Set the initial and max connections to be the same
>> number. One might think that you need more sessions to keep the CPUs
>> busy (and you may need more than 1 per CPU thread) but the reality is
>> this: With a high number of sessions, the queue is longer for
>> everything. The chance of getting scheduled when it needs to goes
>> down and if there is fairly steady and a medium to high load, any
>> "bip" will cause a massive queue for a resource. Consider what
>> happens when calls are taking milliseconds and for a split second,
>> some session holds a shared resource - it may take the system tens of
>> minutes to recover from that backlog. This is why most high
>> throughput OLTP systems only want to run at a max of 65% (or so) CPU
>> utilization with very short run queues - so that if there is any slow
>> down, there is enough resource head room to recover. Otherwise the
>> system will likely be in a unrecoverable flat spin at Mach 5.
>> On Sat, Nov 28, 2009 at 12:13 AM, Christo Kutrovsky
>> <> wrote:
>>> Greg,
>>> It's a single UltraSparc T2 CPU, which is 8 cores, 8 threads. Note that
>>> each
>>> core has 2 integer pipelines. So you could assume 16 CPUs and 64 threads.
>>> There are many things that are wrong with this setup, and reducing the
>>> number of connections is something I am considering. However it's not
>>> that
>>> simple. Imagine that instead of CPU those were doing IO. You want to have
>>> a
>>> relatively deep IO queue to allow the raid array to deliver.
>>> One thing that puzzles me is given that the suspicion is deep cpu run
>>> queue
>>> is problems, why only one very specific latch is causing the problem.
>>> There
>>> are several different types of queries running at the same time, why only
>>> one specific query is causing latch contention, why not the other ones.
>> --
>> Regards,
>> Greg Rahn
>> --

Christo Kutrovsky
Senior Consultant
I blog at

Received on Wed Dec 09 2009 - 21:04:15 CST

Original text of this message