Re: CBC latch contention on index root block

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Fri, 18 Jan 2013 22:23:20 +0200
Message-ID: <CAMHX9J+4Ri8ErYB9LUENmmN2fDHM=cRfJ=y7ShL9nLnYKqe92Q_at_mail.gmail.com>

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_at_gmail.com> 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

>

> --

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

>

>

>


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

Received on Fri Jan 18 2013 - 21:23:20 CET