Re: CBC latch contention on index root block

From: oracledba <>
Date: Fri, 18 Jan 2013 13:11:06 -0500
Message-ID: <>

Thanks Jonathan!
If you look at the plan of the query there is another unique scan access on a different index.It's also being accessed by as many number of concurrent users.But it's not in contention.The only difference is that it's height is more than 1.
I don't know whether Oracle treats an index root block in a different manner(like holding a latch in exclusive mode) when the index has just one block which is also treated as leaf block.not sure...
Operation                               Name
---------------------------------  - -----------------
    INDEX UNIQUE SCAN              *PK_AMRABS_PGM*     -- *Not on this
index root block (Height=2)*
      INDEX UNIQUE SCAN            *PK_RCUST_GLB_MAP  -- latch contention
on index root block(height=1)

On Fri, Jan 18, 2013 at 3:15 AM, Jonathan Lewis <
> wrote:

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

Received on Fri Jan 18 2013 - 19:11:06 CET

Original text of this message