Re: CBC latch contention on index root block
Date: Fri, 18 Jan 2013 13:11:06 -0500
Message-ID: <CA+eRr5F2onvAGia3LPHDaxW+VJ4Q-MzMhHSnz-CyW7ZZGwRJLQ_at_mail.gmail.com>
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 --------------------------------- - -----------------SELECT STATEMENT
FILTER
SORT ORDER BY
FILTER
TABLE ACCESS FULL TAMRABS_PGM INDEX UNIQUE SCAN *PK_AMRABS_PGM* -- *Not on this index root block (Height=2)* TABLE ACCESS BY INDEX ROWID TAMRCUST_GLB_MAP INDEX UNIQUE SCAN *PK_RCUST_GLB_MAP -- latch contentionon index root block(height=1)
*
On Fri, Jan 18, 2013 at 3:15 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> 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
> 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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 18 2013 - 19:11:06 CET