Re: CBC latch contention on index root block
- From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- To: oracledba71@xxxxxxxxx
- Date: Fri, 18 Jan 2013 22:23:20 +0200
As this is Oracle 10g, then even the "pinless" *consistent gets -
examination* operations (which use shared CBC latches) can be overridden if
there's a chance that you will be revisiting the same index/table within
the same execution. So, looks like the index root block will be pinned
because of the FILTER loop in your plan.
The simplest example I can come up with is this (the object_id column of
table T is indexed with unique index):
SELECT SUM(object_id) INTO :j FROM t WHERE object_id IN (123, 999999)
-----------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
-----------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 5 | |
| 2 | *INLIST ITERATOR * | | | | |
|* 3 | INDEX RANGE SCAN| I | 1 | 5 | 3 (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("OBJECT_ID"3 OR "OBJECT_ID"9999))
The INLIST ITERATOR operation is just a loop, which loops through all
values listed in the IN list and calls its child function (INDEX RANGE
SCAN) once for each value. So the index will be visited again in a loop,
thus it makes sense to keep some blocks pinned. And if you pin blocks,
exclusive latches will be taken.
Try without the FILTER loop (if you know the (max) number of elements to
check for, use multiple queries selecting only one value, concatenated
with UNION ALL - this will make a difference for contention).
--
Tanel Poder
Online Training! - http://blog.tanelpoder.com/seminar
The Voicee App - http://voic.ee
On Thu, Jan 17, 2013 at 9:21 PM, oracledba <oracledba71@xxxxxxxxx> wrote:
> 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.
>
> SQLID NAME HMODE
> FUNC OBJECT Held Gets Held % Held ms Avg hold ms
> ------------- --------------------- ------------
> ----------------------------------- ---------- ---------- -------
> ----------- -----------
> f7srmb2gqa245 cache buffers chains exclusive kcbgtcr: kslbegin
> excl *400011C* 63904 60072 6.39 11328.901 .189
> 5cmrd9mq8jnr8 cache buffers chains exclusive kcbgtcr: kslbegin
> excl *400011C* 56262 52406 5.63 9974.127 .190
> f7srmb2gqa245 cache buffers chains exclusive kcbrls:
> kslbegin *400011C* 39664 37803 3.97
> 7031.634 .186
> 5cmrd9mq8jnr8 cache buffers chains exclusive kcbrls:
> kslbegin *400011C* 33145 30552 3.31
> 5875.946 .192
> 5cmrd9mq8jnr8 cache buffers chains exclusive kcbgtcr: kslbegin
> excl 3406294 892 770 .09 158.134 .205
> I am not sure why the latch is held in exclusive mode for a select
> statement.
> The buffer state is xcur and it's not dirty with touch count of 657
> now.Have seen touch count in the order of 1000s at peak time.
> This is table is static in nature and there were no DMLs for the past year
> or so.
>
> STATE BLOCK_CLASS OBJECT_TYPE object TCH MODE_HELD DIRTY TEMP
> -------- ------------ ------------ -------------- ---------- ----- -----
> xcur data block INDEX xxxxxxxx 657 0 N N
> The index in contention is a primary key index and has just 1 block.
>
> INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP BLEVEL H LFBLKS
> CLUF NDK NUM_ROWS
> ------------------ ------- ---- -------- ---- ---- ---------- -- --------
> ---------- ----------- -------
> xxxxxxxxx NORMAL YES VALID NO N 0 1 1
> 5 133 133
>
> I have opened a SR and Oracle is recommending to use IOT with PCTFREE 90.
> You still end up hitting the same root block even if you make the tiny
> table as IOT.
> They don't have any clues as why the latch is held in exclusive mode for a
> SELECT.
>
> Looking forward to see if anyone had similar issue before...
>
> Thanks
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
