latches: cache buffers chains

[chu...@safeplace.net]

I recently increased the buffer cache size to 300m on a 4 cpu NT Oracle
database. Since then I've noticed a lot more misses on the "cache
buffers chains" latch and a sleeps/miss ratio of 73.2%. What can I do
to reduce the number of misses on the latch and reduce the number of
sleeps? There seems to be no documentation on tuning this latch.

The hit ratio on it is .997. Should I even be concerned about it?

This database used to be heavily i/o bound. Now during peak times I see
all 4 cpus pegged at 100% with very little disk activity. Is this latch
the culprit for the high cpu usage, or is it just a result of having
more buffers in memory to be scanned?

--
--
Chuck Hamilton
chu...@safeplace.net

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

[Jonathan Lewis]

You have a good hit ratio, so I personally wouldn't worry
too much about it.

One thing to look at is the v$latch_children table for
specific latches which individually responsible for
misses (see my posts to Ross Mohan on 'an oldie ...'),
then track the most expensive latch back to the
database block it is protecting.

You can be lucky and change the contention
dramatically by changin the number of db block
hash buckets so that very commonly used blocks
(typically index root blocks) hash to different chains.

Watch out - there are typically db_block_buffers/4
child latches / hash chains.

What is the spin_count ? You can get an idea on how
much of your CPU is simply 'lost' in spinning by doubling
and halving it, and seeing if this makes any significant
difference to the CPU usage and total throughput.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

chu...@safeplace.net wrote in message <7h9luu$85s$1...@nnrp1.deja.com>...

[Ross Mohan]

I agree with JL, and think that at 99.7% you have other cats to fry, but....

I can add when vexed by buffer problems, I usually make hash buckets darn
near 100% of block buffers.......and that in your case, you can kill sleeps
( which are expensive!) by upping the spin_count from the default ( 200 or
so? ).
I have mine currently set to about 1000, and if i do miss a latch ( 10% of
the
time! ), I virtually *always* get it _without_ sleeping.....

N.B.spinning does cost CPU cycles, but you have a better chance of
picking up the latch in a timely fashion than if you sleep for it. Don't
tune for CPU
usage, tune for response time and throughput.....

hth

Jonathan Lewis wrote in message
<926452390.1249.1...@news.demon.co.uk>...