How do determine the origin of a CBC?
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Oct 2006 15:27:10 +0200
I have a DB with quite a few "cache buffers chains" despite it is
already in ASSM which is supposed to be more favourable to reduce CBC of
hot blocks
I am trying to identify the root cause of my CBC. Following various
sources it is either a 'hot block' which will be identified by a high
touch count
or it is the children latch for the 'cache buffers chains' which covers
too much blocks. The problem is that I have it seems that I have both
and I am unable to assess
Who is reponsible in which extend for what:
Tablespace in ASSM, version 9.2.0.7
db_block_size integer 16384
db_cache_size big integer 1711276032 # 104.448
db_block buffers
SQL> select count(1) from v$latch_children where latch# = 98 ;
COUNT(1)
----------
1024
I have many of them reaching the sleeps :
From v$latch_children:
Sub pool
CHILD# Latch Gets Misses Sleep
---------- ---------------- ------------ --------- -------
323 00000003F2A9C888 44745766 7342 494
255 00000003F2A625C8 126467674 4170 371
373 00000003F2AC74E8 70145101 11267 295
542 00000003F2B57E18 42036415 4445 205
971 00000003F2CC6578 23570515 721 180
121 00000003F29EFBA8 29752785 2561 176
543 00000003F2B58BC8 24185782 1277 175
953 00000003F2CB7038 19896257 1240 165
378 00000003F2ACB958 29662897 1709 163
320 00000003F2A99F78 26143392 1761 139
327 00000003F2A9FF48 28339782 2077 137
And if I take the higher :
Latch : 00000003F2A9C888
Segment name EXTENT# BLOCK#
TCH CHILD#
--------------------------------------------- ---------- ----------
---------- ----------
DUOT.FLIGHTS 64 465
1315 323
PARTY.CUSTOMERS 89 145
92 323
PARTY.CUSTOMERS 118 78
78 323
PARTY.CUSTOMERS 92 58
75 323
DUOT.ASG_FLT_PK 72 486
64 323
PARTY.CUSTOMERS 110 14
55 323
PARTY.CUSTOMERS 115 334
54 323
PARTY.CUSTOMERS 103 270
52 323
PARTY.CUSTOMERS 119 145
48 323
PARTY.CUSTOMERS 81 17
48 323
PARTY.CUSTOMERS 112 58
45 323
PARTY.CUSTOMERS 25 17
37 323
.
.
(110 blocks)
So on one side I have heavy touch count and on the other hand I have
chains that are quite long.
Usually they never exceed 35 to 40 blocks, they are over 100 here:
1 select avg(cpt) from (select count(1) cpt, hlADDR from x$bh group by
hladdr )
SQL> /
AVG(CPT)
----------
100.40625
Is there a way to assess which CBC are due to touch count and which are
due to too long buffer chains?
I suspect that most are due to hotblock, even if we are in ASSM, but
can't swear if many are also due to too long chains. At the end of the
day, I simply don't know.
Regards,
B. Polarski
Oracle DBA
Other related posts:
