RE: How do determine the origin of a CBC?

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Thu, 26 Oct 2006 08:56:16 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0D56873F@mailserver1>

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_at_jcpenney.com] Sent: Wednesday, 25 October, 2006 3:57 PM To: Polarski, Bernard
Cc: oracle-l_at_freelists.org
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?

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 26 2006 - 01:56:16 CDT