Re: CBC latch contention on index root block

From: Jonathan Lewis <>
Date: Fri, 18 Jan 2013 08:15:13 -0000
Message-ID: <47DD6DDB90DC49EBBD6D9C7FDD8B80AD_at_Primary>

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 ?


Jonathan Lewis

Author: Oracle Core (Apress 2011)

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

| All,
| In our production system(oracle 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
| 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

Received on Fri Jan 18 2013 - 09:15:13 CET

Original text of this message