RE: How do determine the origin of a CBC?
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: "Riyaj Shamsudeen" <rshamsud@xxxxxxxxxxxx>
- Date: Thu, 26 Oct 2006 08:56:16 +0200
1) yes this is production and that's precisely the problem : the touch
count problem is known and will be tackle, but customer expect that once
the hot blocks is resolved, the latch free wait for CBC will be gone. I
was a bit worried that if a fraction of the CBC comes from the too long
chain, I will still have CBC after the reboot
in a significant number. So I wanted to estimated the proportion of who
si responsible for what.
2) I have not drill down for the problem of these hots blocks is known
and must be addressed by the DEV in next release.
3) all latches have between 80 to 120 blocks, but vast majority is
around 100.
4) You guess it right, consistents gets is mad, due to useless access
from the application. But my concern was only to know if the hot blocks
were not hidding the long chains, especially given he very high number
of gets.
This snapshot diff on v$systat event class8 is taken on one second and
represent the usual acitvity :
Delta Value Name
----- --------- -------------------------------
Diff : 2 commit cleanouts
Diff : 2 commit cleanouts successfully completed
Diff : 16395 consistent gets
Diff : 8368 consistent gets - examination
Diff : 10 db block changes
Diff : 7 db block gets
Diff : 1 free buffer requested
Diff : 1 redo synch time
Diff : 2 redo synch writes
Diff : 53 shared hash latch upgrades - no wait
Diff : 1 switch current to new buffer
I find this DB very instructive. we have lots of touch count and lots of
gets, so who is doing the more CBC? I would have liked to know how to
distinguish them and address them for the next reboot. This is the
problem of the production DBA : you are granted one stop every x months
to correct problems, and people expect that all problem analysed will be
solved after the re-boot. But even AskTOM acknowleged that it is
impossible to forecast the exact metrics of a tunning action in a
complex system. So after a reboot, if the problem is still there, but
due to another cause, the DBA will somehow be accounted responsible for
the relative failure.
bp
_____
From: Riyaj Shamsudeen [mailto:rshamsud@xxxxxxxxxxxx]
Sent: Wednesday, 25 October, 2006 3:57 PM
To: Polarski, Bernard
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: How do determine the origin of a CBC?
Bernard
There is no guaranteed way to know whether CBC latch contention
is due to genuinely long hash chains or due to just one hot block
(without changing any parameter). In either case, latch covering that
chain/bucket will have contention.
If I have to guess, then I would say that you should concentrate
on the block with higher touch count. If you can reduce contention for
that block, then you will be relieving stress on that latch.
1. Is this a production database or performance benchmark
database ? If it is later, you could potentially set
_db_block_hash_latches and _db_block_hash_buckets to db_block_buffers/2.
This would enable you to see whether the problem is due to long hash
chain or hot block.
2. What type of block is that ? Root block of a busy index ?
small table ? segment header ?
3. I see that in average 100 blocks are protected by CBC
latches in average. But, that doesn't tell you much. Can you find how
many blocks are protected by the latches with higher # of misses and
sleeps ?
4. I am also wondering whether you have any consistent read
issues ? Do you see many copies of this block in this hash chain?
Other related posts:
