Re: CBC latch contention on index root block

From: oracledba <oracledba71_at_gmail.com>
Date: Sat, 19 Jan 2013 19:47:02 -0500
Message-ID: <CA+eRr5H8sLd6KkJgnY_XFyiZNuARtbCO+qej1-oahZAn13QRjA_at_mail.gmail.com>

If I use NOT EXISTS in the 2nd part it doesn't return any rows.I know with the current bind values the 3rd query doesn't return any rows.so i am including only 2 parts here.
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
   F0324DB.TAMRABS_PGM
where cust_num=112805 and ABS_TYP_ID='COFM' union all
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
    F0324DB.TAMRABS_PGM
 WHERE CUST_NUM= -1 AND ABS_TYP_ID='COFM' AND  ABS_PGM_ID NOT IN(SELECT ABS_PGM_ID FROM F0324DB.TAMRABS_PGM where cust_num=112805 and ABS_TYP_ID='COFM')
/
21 rows selected.

if i use NOT EXISTS then it's not returning any rows for the 2nd part.

AND NOT exists(SELECT ABS_PGM_ID FROM F0324DB.TAMRABS_PGM where cust_num=112805 and ABS_TYP_ID='COFM')
4 rows selected.

thanks

On Sat, Jan 19, 2013 at 3:00 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
> Your original query transforms into something of the form:
>
> select where
> cust_num = K
> or exists (subquery for cust_num = K)
> or (cust_num = -1 and not exists (subquery for cust_num = K)
>
> The optimizer cannot handle (predicate OR subquery) efficiently (see
> http://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/ ) so it
> has had to do a tablescan to get 892 rows that might match the customer or
> the subquery, with the consequence that it has ended up running the
> subquery 871 times. Either you have found a latch-related bug which makes
> this subquery execution highly contentious, or it is the number of calls -
> combined with the number of concurrent users - that makes this so
> inefficient that you have a problem. If it's a bug you need a patch; if
> it's inherent then you can rewrite the query
>
> select
> where cust_num = K
> union all
> select where cust_num = -1 and not exists (subquery for cust_num = K)
> union all
> select where exists(subquery for cust_num = K)
>
> With suitable indexing the first two parts can be more efficient that your
> current query (I've omitted the abs_typ_id for clarity, but an index
> starting with abs_typ_id and cust_num in some order would be useful). You
> don't have to worry about accidental duplicates from the first two pieces
>
> For the last part of the union all you need to ensure that you include a
> predicate to eliminate the rows already returned through the first two
> parts - and your optimum plan would be unnesting and converting to a nested
> loop join. I am assuming that this table will always be a very small table
> and that you would drive off an index on the cust_num when accessing this
> table because very few of your customers will appear in the table.
>
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com/all-postings
>
> Author: Oracle Core (Apress 2011)
> http://www.apress.com/9781430239543
>
> ----- Original Message -----
> From: "oracledba" <oracledba71_at_gmail.com>
> To: <jonathan_at_jlcomp.demon.co.uk>
> Cc: "Oracle-L Freelists" <Oracle-L_at_freelists.org>
> Sent: Saturday, January 19, 2013 12:34 AM
> Subject: Re: CBC latch contention on index root block
>
>
> | Here is the query with the plan and predicate section.CUST_NUM value is
> | same for all 4 bind variables.
> |
> |
> ---------------------------------------------------------------------------------------------------
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Sun Jan 20 2013 - 01:47:02 CET