Don't forget that Oracle has redesigned the whole
area of hash buckets and hash latches.
In 8.1, the number of buckets is roughly 2 x db_block_buffers,
but each latch covers multiple buckets (don't forget that
a latch is about 160 bytes, so a noticeable amount of
memory overhead)
Because each
Hi,
while we are on this topic, I would to ask you all about a system I was
working on recently.
The main problem was that approx. 64 threads were almost continuously doing
full table scans on a small table of 800 rows (the developers insisted this
was necessary). This table was in 1 or 2
Really,
The only thing to do is fix the SQL. Each logical I/O or buffer get results
in a cache buffer chain latch get. So by doing less LIO, you will get fewer
latch gets and as a result fewer sleeps on latches. This is how you fix the
*problem*. You can also fix the *symptom*: bump up
Amen. Contention for cache buffers chains means too much logical IO, ie.
find and exterminate heavy SQL.
Anjo Kolk wrote:
Really,The only thing to do is fix the SQL. Each logical I/O or buffer get resultsin a cache buffer chain latch get. So by doing less LIO, you will get fewerlatch gets
[Mogens Nørgaard]
Amen. Contention for cache buffers chains means too much logical IO,
ie. find and exterminate heavy SQL.
I don't see why the heavy SQL would result in the chain having 66 buffer
heads in it, though, or why the sleep count would be so skewed.
And my core question is
James Manning [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
02/28/02 09:13 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: Perf Advice Needed: cache buffers chains, high waits,
_db_block_hash_buckets
db_block_buffers = 360448
db_block_lru_latches = 4
db_block_size = 8192
_db_block_hash_buckets = 720896
Ok, what I have so far is:
- using itrprof, I saw that 35% of my elapsed time was based on waits of
cache buffers chains latches.
- checking v$latch_children (latch#=66), there are a good
