Re: CBC latch contention on index root block

From: oracledba <oracledba71_at_gmail.com>
Date: Fri, 18 Jan 2013 19:34:23 -0500
Message-ID: <CA+eRr5HP92eBosRT-r+oM=uybfM+ZUjttOyd9=QUg8xqLr1mLA_at_mail.gmail.com>

Here is the query with the plan and predicate section.CUST_NUM value is same for all 4 bind variables.

---

| Id  | Operation                      | Name             | Rows  | Bytes |
Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |    52 |  4472
|    25   (4)| 00:00:01 |
|   1 |  SORT ORDER BY                 |                  |    52 |  4472
|    25   (4)| 00:00:01 |


|*  2 |   FILTER                       |                  |       |

|            |          |


|*  3 |    TABLE ACCESS FULL           | TAMRABS_PGM      |   892 | 76712

|    24   (0)| 00:00:01 |


|*  4 |    INDEX UNIQUE SCAN           | PK_AMRABS_PGM    |     1 |    15

|     1   (0)| 00:00:01 |


|*  5 |     TABLE ACCESS BY INDEX ROWID| TAMRCUST_GLB_MAP |     1 |    32

|     1   (0)| 00:00:01 |


|*  6 |      INDEX UNIQUE SCAN         | PK_RCUST_GLB_MAP |     1 |

|     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   2 - filter("CUST_NUM"=112805 OR "CUST_NUM"=(-1) AND *NOT EXISTS*(SELECT /*+ */ 0 FROM
              "F0324DB"."TAMRABS_PGM" "TAMRABS_PGM" WHERE "ABS_TYP_ID"='COFM' AND "CUST_NUM"=112805 AND               "ABS_PGM_ID"=:B1) *OR EXISTS* (SELECT /*+ */ 0 FROM "F0324DB"."TAMRCUST_GLB_MAP"
              "TAMRCUST_GLB_MAP" WHERE "ATRB_TYP_CD"=:B2 AND "TBL_NM"='TAMRABS_PGM' AND

              "CUST_NUM"=112805 AND "GLB_NUM"=:B3))
   3 - filter("ABS_TYP_ID"='COFM')
   4 - access("ABS_PGM_ID"=:B1 AND "CUST_NUM"=112805 AND

"ABS_TYP_ID"='COFM')
   5 - filter("GLB_NUM"=:B1)
   6 - access("CUST_NUM"=112805 AND "TBL_NM"='TAMRABS_PGM' AND "ATRB_TYP_CD"=:B1) Statistics

---

         41  recursive calls
        238  db block gets
       1051  consistent gets
         10  physical reads
          0  redo size
       1540  bytes sent via SQL*Net to client
        248  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         21  rows processed

Thanks

On Fri, Jan 18, 2013 at 6:06 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk > wrote:

>
> I wouldn't expect the root block of an index to be pinned on a filter
> subquery - there's an interesting trade-off between a semi (or anti) join
> and a filter subquery: the join pins the root block but has to probe for
> every driving row (cheaper per execution, but potentially higher number of
> executions) while the filter subquery doesn't pin the root block but may
> run fewer times (more expensive per execution, but fewer executions). And,
> to corroborate, the stats from another post show that "buffer is not pinned
> count" is 891, matching the number of index probe starts.
>
> I received the text of the query - but I didn't see it go to the group. It
> contained an OR condition between a NOT IN and an IN subquery, and had four
> predicates of the form cust_num = :bind - but it's not possible to tell how
> many different values were used in the comparison.  We need this
> information
>
> It would be helpful if the list could see the query and the predicate
> section of the execution plan to see how the query has been transformed
> from INs to EXISTS, and how this has affected the predicates.
>
> There was a bug in 10.2.0.3 relating to root block pinning on nested loops
> after rebuilding an index which caused latching problems but ...
> a) it shouldn't apply to filter subqueries
> b) it was supposed to be fixed in 10.2.0.4
>
> However, perhaps there was a related piece of code that suffers a similar
> problem with filter subqueries and root blocks).  It would be a trivial
> check to see if the guilty index had been rebuilt (and if so you could drop
> and recreate it to see if the problem magically disappears).   (I wrote a
> blog note about the bug a few years ago (
> http://jonathanlewis.wordpress.com/2008/02/09/index-rebuild-10g/ )
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com/all-postings
>
> Author: Oracle Core (Apress 2011)
> http://www.apress.com/9781430239543
>
> ----- Original Message -----
> From: "Tanel Poder" <tanel_at_tanelpoder.com>
> To: <oracledba71_at_gmail.com>
> Cc: "Oracle-L Freelists" <Oracle-L_at_freelists.org>
> Sent: Friday, January 18, 2013 8:56 PM
> Subject: Re: CBC latch contention on index root block
>
>
> | 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:
> |
> |
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Sat Jan 19 2013 - 01:34:23 CET