Re: CBC latch contention on index root block
From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sat, 19 Jan 2013 03:33:43 +0400
Message-ID: <CAOVevU7s=tdiVgQ=OM1pzWUAoU9XVZQitfC+o16q96mFcwTxPQ_at_mail.gmail.com>
Since there are 891 rows in main table(TAMRABS_PGM) and 133 rows in subject(TAMRCUST_GLB_MAP) and 0 rows matches to predicates, may be will be better to do at first "hash join outer" with this table? Like that:
)
SELECT
CUST_NUM,
FROM
t1
WHERE
ORDER BY
CUST_NUM DESC On Sat, Jan 19, 2013 at 3:06 AM, 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
>
>
Date: Sat, 19 Jan 2013 03:33:43 +0400
Message-ID: <CAOVevU7s=tdiVgQ=OM1pzWUAoU9XVZQitfC+o16q96mFcwTxPQ_at_mail.gmail.com>
Since there are 891 rows in main table(TAMRABS_PGM) and 133 rows in subject(TAMRCUST_GLB_MAP) and 0 rows matches to predicates, may be will be better to do at first "hash join outer" with this table? Like that:
with t1 as (
SELECT--+ no_merge use_hash(p m) full(m)
CUST_NUM,
ABS_TYP_ID,
ABS_PGM_ID,
ABS_PGM_SHORT_DSCR,
ABS_PGM_LNG_DSCR,
ABS_PGM_DUR_MODE_CD,
ABS_PGM_WFM3G_CD,
m.rowid as rid
FROM
TAMRABS_PGM P
,TAMRCUST_GLB_MAP M
WHERE
:"SYS_B_0" = :"SYS_B_1"
and p.ABS_TYP_ID = :1
and p.CUST_NUM = m.GLB_NUM(+)
and p.ABS_PGM_ID = m.ATRB_TYP_CD(+)
and :"SYS_B_2" = m.TBL_NM(+)
AND :6 = m.CUST_NUM(+)
)
SELECT
CUST_NUM,
ABS_TYP_ID, ABS_PGM_ID, ABS_PGM_SHORT_DSCR, ABS_PGM_LNG_DSCR, ABS_PGM_DUR_MODE_CD, ABS_PGM_WFM3G_CD
FROM
t1
WHERE
CUST_NUM=:2
OR m.rid is not null
OR (
CUST_NUM=:3
AND ABS_PGM_ID NOT IN(
SELECT
ABS_PGM_ID
FROM
TAMRABS_PGM
WHERE
CUST_NUM=:4
AND ABS_TYP_ID=:5
)
)
ORDER BY
CUST_NUM DESC On Sat, Jan 19, 2013 at 3:06 AM, 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
>
>
-- Best regards, Sayan Malakshinov Senior performance tuning engineer PSBank Tel: +7 903 207-1576 -- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 19 2013 - 00:33:43 CET