Re: CBC latch contention on index root block

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Fri, 18 Jan 2013 22:56:35 +0200
Message-ID: <CAMHX9J+eVbMT1ZoPsRjCVmAWiE-G2dfw4NHdOFHZ=ooOY5OTEw_at_mail.gmail.com>

Note that as you have a single block index, you don't really have a root block at all there - just one single LEAF block right after the segment header. So, perhaps there's a special case here, that some optimizations (under the FILTER op) that would be done for root & branch blocks, are not done for LEAF blocks.
So, knowing that you only have one more thing to test could be to create a "big" index where you have one index row per block, you can create such index for example:

CREATE INDEX i2 ON t(object_id, LPAD('x',4000,'x'));

This way you will end up with a bunch of index leaf blocks with only 1-2 rows per leaf block and there definitely will be a root block as well... The root block will (hopefully) be accessed with shared latch gets and you'd spread the contention of that leaf block over many different blocks. But Jonathan's recommendation (of hash clusters) should work as well ...

-- 
Tanel Poder
Online Training! - http://blog.tanelpoder.com/seminar
The Voicee App - http://voic.ee


On Fri, Jan 18, 2013 at 10:28 PM, Tanel Poder <tanel_at_tanelpoder.com> wrote:



> Note that in in 11g the unique index access under INLIST ITERATOR does not

> turn into an INDEX *RANGE* SCAN, but stays as INDEX UNIQUE SCAN as it

> should ...

>

> On Fri, Jan 18, 2013 at 10:23 PM, Tanel Poder <tanel_at_tanelpoder.com>wrote:

>

>> 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"=123 OR "OBJECT_ID"=999999))

>>

>>

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

>>

>>



--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 18 2013 - 21:56:35 CET