Re: CBC latch contention on index root block
- From: oracledba <oracledba71@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Fri, 18 Jan 2013 13:11:06 -0500
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 contention
on index root block(height=1)
*
On Fri, Jan 18, 2013 at 3:15 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxx>
> To: <Oracle-L@xxxxxxxxxxxxx>
> 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.
> |
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
