Re: CBC latch contention on index root block
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 18 Jan 2013 08:15:13 -0000
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
Other related posts:
