Re: CBC latch contention on index root block

I don't think I can give you a guaranteed explanation of why you're seeing the problem, but I can give you a workaround.

Create a single table hash cluster specifying a couple of hundred keys and sizes that require one block per key, and specify your current PK as the hash key.
Unless you are unlucky you should get one row per block (no collision on hashing). Because of the PK declaration the optimizer will know that a single table by hash key access is the most efficient possible, so you shouldn't even access the root block, let alone get contention on it.

What query are you using to get the first bit of the output you supplied ?

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "oracledba" <oracledba71_at_gmail.com> To: <Oracle-L_at_freelists.org> Sent: Thursday, January 17, 2013 7:21 PM Subject: CBC latch contention on index root block

| 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.
|

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