Re: CBC latch contention on index root block

From: Jonathan Lewis <>
Date: Fri, 18 Jan 2013 23:06:53 -0000
Message-ID: <35F09190692B4ECDB8671198335AF131_at_Primary>

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

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


Jonathan Lewis

Author: Oracle Core (Apress 2011)

  • Original Message ----- From: "Tanel Poder" <> To: <> Cc: "Oracle-L Freelists" <> 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
| done for LEAF blocks.
| So, knowing that you only have one more thing to test could be to create
| "big" index where you have one index row per block, you can create such
| index for example:


Received on Sat Jan 19 2013 - 00:06:53 CET

Original text of this message