Re: CBC latch contention on index root block

From: oracledba <oracledba71_at_gmail.com>
Date: Fri, 18 Jan 2013 16:54:46 -0500
Message-ID: <CA+eRr5Fx5Y9SawYVzip4sv-mEfmCAuBw2AkaxGpb8-saGCUf_g_at_mail.gmail.com>

Yes Tanel.This is what exactly I have confronted with Oracle support when they recommended IOT.
I have also asked them very specifically whether Oracle treats index root block in a different manner for it's operations when the index has only 1 block.I can create it with higher PCTFREE value to keep root and leaf in separate blocks.
Have a look at the buffer pinned/not pinned counts.

STAT    buffer is pinned count         882
*STAT    buffer is not pinned count     891*
STAT    index fetch by key             892

------------------------------------------------------------------------------------------
 Operation                       | Name             | Starts | E-Rows |

A-Rows || Buffers |

---

  FILTER                         |                  |      1 |        |
21 ||    1018 |
   SORT ORDER BY                 |                  |      1 |     52 |
21 ||    1018 |
    FILTER                       |                  |      1 |        |
21 ||    1018 |
     TABLE ACCESS FULL           | TAMRABS_PGM      |      1 |    892 |
 892 ||     107 |
     INDEX UNIQUE SCAN           | PK_AMRABS_PGM    |     20 |      1 |
 3 ||      40 |
      TABLE ACCESS BY INDEX ROWID| TAMRCUST_GLB_MAP |    871 |      1 |
 0 ||     871 |
       INDEX UNIQUE SCAN         | PK_RCUST_GLB_MAP |    871 |      1 |
 0 ||     871 |

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

> 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 - 22:54:46 CET