CBC latch contention on index root block

From: oracledba <oracledba71_at_gmail.com>
Date: Thu, 17 Jan 2013 14:21:48 -0500
Message-ID: <CA+eRr5G2GTsosnK021gRpLAfZb55x5FLEp-ZQwbnNaBj8JzyhA_at_mail.gmail.com>

All,
In our production system(oracle 10.2.0.4) users are complaining slowness when they try to retrive few rows from a tiny table(133 rows in 5 blocks) using “Unique Index Scan”.The index has only 1 block(root,leaf,branches all in one).I could see a severe latch:cbc contention on two SQLs that are executed at higher rate from multiple users.Both sqls are running in few milliseconds.The execution rates are 10 fold than normal. As you can see below the data block address 400011C is a index root block.

SQLID         NAME                                HMODE
FUNC           OBJECT       Held       Gets  Held %     Held ms Avg hold ms


------------- --------------------- ------------

----------------------------------- ---------- ---------- -------

• ----------- f7srmb2gqa245 cache buffers chains exclusive kcbgtcr: kslbegin excl *400011C* 63904 60072 6.39 11328.901 .189 5cmrd9mq8jnr8 cache buffers chains exclusive kcbgtcr: kslbegin excl *400011C* 56262 52406 5.63 9974.127 .190 f7srmb2gqa245 cache buffers chains exclusive kcbrls: kslbegin *400011C* 39664 37803 3.97 7031.634 .186 5cmrd9mq8jnr8 cache buffers chains exclusive kcbrls: kslbegin *400011C* 33145 30552 3.31 5875.946 .192 5cmrd9mq8jnr8 cache buffers chains exclusive kcbgtcr: kslbegin excl 3406294 892 770 .09 158.134 .205 I am not sure why the latch is held in exclusive mode for a select statement. The buffer state is xcur and it's not dirty with touch count of 657 now.Have seen touch count in the order of 1000s at peak time. This is table is static in nature and there were no DMLs for the past year or so.

STATE BLOCK_CLASS OBJECT_TYPE object TCH MODE_HELD DIRTY TEMP
-------- ------------ ------------ -------------- ---------- ----- -----
xcur data block INDEX xxxxxxxx 657 0 N N The index in contention is a primary key index and has just 1 block.

INDEX_NAME  IDXTYPE UNIQ STATUS   PART TEMP     BLEVEL  H   LFBLKS
CLUF         NDK   NUM_ROWS


------------------ ------- ---- -------- ---- ---- ---------- -- --------

---------- ----------- -------

xxxxxxxxx   NORMAL  YES  VALID    NO   N             0  1        1
5         133        133

I have opened a SR and Oracle is recommending to use IOT with PCTFREE 90. You still end up hitting the same root block even if you make the tiny table as IOT.
They don't have any clues as why the latch is held in exclusive mode for a SELECT. Looking forward to see if anyone had similar issue before...

Thanks

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 17 2013 - 20:21:48 CET