How do determine the origin of a CBC?

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Wed, 25 Oct 2006 15:27:10 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0FB9F9D8@mailserver1>

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

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

Received on Wed Oct 25 2006 - 08:27:10 CDT