Re: CBC latch contention on index root block
- From: oracledba <oracledba71@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Fri, 18 Jan 2013 19:34:23 -0500
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@xxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxx>
> To: <oracledba71@xxxxxxxxx>
> Cc: "Oracle-L Freelists" <Oracle-L@xxxxxxxxxxxxx>
> 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:
> |
> |
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
