FAQ
Okay ... so I have a query that works fine in RBO (8161). In CBO (9201) is
returns wrong results (stats are recreated this AM). By adding a group by
the CBO query returns the right data.

Furthermore on CBO, the explain plan shows that Oracle is not even parsing
the query completely. If drops off a huge EXISTS clause.

Does anyone know what is going on? I have a sev 1 tar open and waiting for
OWS to call me.

Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

text/plain attachment: ESPN_Disclaimer.txt

Search Discussions

  • Stephane Faroult at Oct 16, 2002 at 8:06 pm

    "Jamadagni, Rajendra" wrote:

    Okay ... so I have a query that works fine in RBO (8161). In CBO
    (9201) is returns wrong results (stats are recreated this AM). By
    adding a group by the CBO query returns the right data.

    Furthermore on CBO, the explain plan shows that Oracle is not even
    parsing the query completely. If drops off a huge EXISTS clause.

    Does anyone know what is going on? I have a sev 1 tar open and waiting
    for OWS to call me.

    Raj
    Raj,

    I understand your concern about the disappearing EXISTS but I think
    that it would be sensible to try to get the plan using different tools
    (plain old EXPLAIN followed by a query on PLAN_TABLE, set autotrace,
    tkprof ...) to be sure that the bug really is in the parser, and not in
    the output of the plan.
    Your 'adding a GROUP BY' leaves me a little suspicious. Are you sure
    that your query was correct (in the absolute sense) initially? I am
    asking the question because I remember one day somebody coming to me
    triumphantly saying 'I have found a big bug in Oracle' because a query
    which used to give the good result (not exactly the same as 'which was
    correct') was suddenly (after I think the addition of hints) miserably
    failing with a mysterious data conversion error. On closer inquiry, it
    appeared that a VARCHAR2 column was compared to a number, which, as most
    people who once asked themselves 'Why is Oracle *NOT* using my index ?'
    know, forces an implicit to_number() of the VARCHAR2 column. What
    happened is that this column mostly contained numbers represented as
    strings, but for a '***' or similar somewhere. With one execution plan,
    other criteria caused this row to be filtered out before the comparison
    on this very column. Not with the other execution plan.
    The GROUP BY sounds a bit like a DISTINCT to me, a convenient way to fix
    a missing join, or perhaps some hazardous play on ROWNUM or similar.
    Just my gut feeling.

    HTH,

    Stephane Faroult
    Oriole Software

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jamadagni, Rajendra at Oct 16, 2002 at 8:20 pm
    Thanks ...

    so far Oracle had completely understood the problem and looks like we may be
    hitting some bug in CBO. I am awaiting the bug#, I don't know that yet.

    The same query works fine if I do a 'alter session set optimizer_mode=rule';
    though ..

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    -----Original Message-----
    Sent: Wednesday, October 16, 2002 4:06 PM
    To: Multiple recipients of list ORACLE-L
    "Jamadagni, Rajendra" wrote:

    Okay ... so I have a query that works fine in RBO (8161). In CBO
    (9201) is returns wrong results (stats are recreated this AM). By
    adding a group by the CBO query returns the right data.

    Furthermore on CBO, the explain plan shows that Oracle is not even
    parsing the query completely. If drops off a huge EXISTS clause.

    Does anyone know what is going on? I have a sev 1 tar open and waiting
    for OWS to call me.

    Raj
    Raj,

    I understand your concern about the disappearing EXISTS but I think
    that it would be sensible to try to get the plan using different tools
    (plain old EXPLAIN followed by a query on PLAN_TABLE, set autotrace,
    tkprof ...) to be sure that the bug really is in the parser, and not in
    the output of the plan.
    Your 'adding a GROUP BY' leaves me a little suspicious. Are you sure
    that your query was correct (in the absolute sense) initially? I am
    asking the question because I remember one day somebody coming to me
    triumphantly saying 'I have found a big bug in Oracle' because a query
    which used to give the good result (not exactly the same as 'which was
    correct') was suddenly (after I think the addition of hints) miserably
    failing with a mysterious data conversion error. On closer inquiry, it
    appeared that a VARCHAR2 column was compared to a number, which, as most
    people who once asked themselves 'Why is Oracle *NOT* using my index ?'
    know, forces an implicit to_number() of the VARCHAR2 column. What
    happened is that this column mostly contained numbers represented as
    strings, but for a '***' or similar somewhere. With one execution plan,
    other criteria caused this row to be filtered out before the comparison
    on this very column. Not with the other execution plan.
    The GROUP BY sounds a bit like a DISTINCT to me, a convenient way to fix
    a missing join, or perhaps some hazardous play on ROWNUM or similar.
    Just my gut feeling.

    HTH,

    Stephane Faroult
    Oriole Software

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    text/plain attachment: ESPN_Disclaimer.txt
  • Whittle Jerome Contr NCI at Oct 16, 2002 at 9:26 pm
    What happens if you use the Rule hint? Inquiring minds would like to know....

    Jerry Whittle
    ACIFICS DBA

    NCI Information Systems Inc.
    jerome.whittle_at_scott.af.mil
    618-622-4145
    -----Original Message-----
    From: Jamadagni, Rajendra [SMTP:Rajendra.Jamadagni_at_espn.com]

    Thanks ...

    so far Oracle had completely understood the problem and looks like we may be hitting some bug in CBO. I am awaiting the bug#, I don't know that yet.

    The same query works fine if I do a 'alter session set optimizer_mode=rule'; though ..

    Raj
    ______________________________________________________
    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
    QOTD: Any clod can have facts, but having an opinion is an art!


    -----Original Message-----
    From: Stephane Faroult []
    "Jamadagni, Rajendra" wrote:

    Okay ... so I have a query that works fine in RBO (8161). In CBO
    (9201) is returns wrong results (stats are recreated this AM). By
    adding a group by the CBO query returns the right data.

    Furthermore on CBO, the explain plan shows that Oracle is not even
    parsing the query completely. If drops off a huge EXISTS clause.

    Does anyone know what is going on? I have a sev 1 tar open and waiting
    for OWS to call me.

    Raj
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Whittle Jerome Contr NCI
    INET: Jerome.Whittle_at_scott.af.mil

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mark Richard at Oct 17, 2002 at 12:08 am
    Raj,

    We'd love to see the query in question. And, if feasible an example of the
    correct and incorrect results. Can you provide this?

    Cheers,

    Mark.

    "Jamadagni,
    Rajendra" To: Multiple recipients of list ORACLE-L
    Subject: RE: Urgent problem with query in CBO Vs RBO
    Sent by:
    root_at_fatcity.com

    17/10/02 06:20
    Please respond to
    ORACLE-L

    Thanks ...

    so far Oracle had completely understood the problem and looks like we may
    be hitting some bug in CBO. I am awaiting the bug#, I don't know that yet.

    The same query works fine if I do a 'alter session set
    optimizer_mode=rule'; though ..

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.
    QOTD: Any clod can have facts, but having an opinion is an art!

    -----Original Message-----
    Sent: Wednesday, October 16, 2002 4:06 PM
    To: Multiple recipients of list ORACLE-L
    "Jamadagni, Rajendra" wrote:

    Okay ... so I have a query that works fine in RBO (8161). In CBO
    (9201) is returns wrong results (stats are recreated this AM). By
    adding a group by the CBO query returns the right data.

    Furthermore on CBO, the explain plan shows that Oracle is not even
    parsing the query completely. If drops off a huge EXISTS clause.

    Does anyone know what is going on? I have a sev 1 tar open and waiting
    for OWS to call me.

    Raj
    Raj,

    I understand your concern about the disappearing EXISTS but I think
    that it would be sensible to try to get the plan using different tools
    (plain old EXPLAIN followed by a query on PLAN_TABLE, set autotrace,
    tkprof ...) to be sure that the bug really is in the parser, and not in
    the output of the plan.
    Your 'adding a GROUP BY' leaves me a little suspicious. Are you sure
    that your query was correct (in the absolute sense) initially? I am
    asking the question because I remember one day somebody coming to me
    triumphantly saying 'I have found a big bug in Oracle' because a query
    which used to give the good result (not exactly the same as 'which was
    correct') was suddenly (after I think the addition of hints) miserably
    failing with a mysterious data conversion error. On closer inquiry, it
    appeared that a VARCHAR2 column was compared to a number, which, as most
    people who once asked themselves 'Why is Oracle *NOT* using my index ?'
    know, forces an implicit to_number() of the VARCHAR2 column. What
    happened is that this column mostly contained numbers represented as
    strings, but for a '***' or similar somewhere. With one execution plan,
    other criteria caused this row to be filtered out before the comparison
    on this very column. Not with the other execution plan.
    The GROUP BY sounds a bit like a DISTINCT to me, a convenient way to fix
    a missing join, or perhaps some hazardous play on ROWNUM or similar.
    Just my gut feeling.

    HTH,

    Stephane Faroult
    Oriole Software

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing). (See
    attached file: ESPN_Disclaimer.txt)

    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Privileged/Confidential information may be contained in this message.
    If you are not the addressee indicated in this message
    (or responsible for delivery of the message to such person),
    you may not copy or deliver this message to anyone.
    In such case, you should destroy this message and kindly notify the sender
    by reply e-mail or by telephone on (61 3) 9612-6999.
    Please advise immediately if you or your employer does not consent to
    Internet e-mail for messages of this kind.
    Opinions, conclusions and other information in this message
    that do not relate to the official business of
    Transurban City Link Ltd
    shall be understood as neither given nor endorsed by it.
    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mark Richard
    INET: mrichard_at_transurban.com.au

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    application/octet-stream attachment: ESPN_Disclaimer.txt
  • Jamadagni, Rajendra at Oct 17, 2002 at 1:13 pm
    Here is an example ...

    RULE Hint works ... but we can't find all the SQLs that will fail. The only
    other option suggested by OWS is setting optimizer_features_enable='8.1.6'.
    It works fine ...

    Raj

    --------------- start ----------------------------

    set feedback off
    set define off
    drop table mjc_junk;
    drop table mjc_mst;
    prompt Creating MJC_JUNK...
    -- Create table
    create table MJC_JUNK
    (
    COLA NUMBER(9)

    );
    prompt Creating MJC_MST...
    -- Create table
    create table MJC_MST
    (
    COLA NUMBER(9) not null,
    COLB NUMBER(9) not null,
    CDT DATE

    );

    prompt Loading MJC_MST...
    insert into MJC_MST (COLA, COLB, CDT)
    values (1284457, 213841, to_date('06-06-2001 09:28:40', 'dd-mm-yyyy
    hh24:mi:ss'));
    commit;
    prompt 41 records loaded
    declare
    begin
    sys.dbms_stats.gather_table_stats(ownname => null,

    tabname => 'MJC_JUNK',
    estimate_percent => 20,
    block_sample => false,
    method_opt => 'FOR ALL COLUMNS',
    degree => 4,
    granularity => 'DEFAULT',
    cascade => true);

    end;
    /
    set feedback on
    set define on

    prompt This query returns invalid results:
    select sUM(a.colb)
    FROM mjc_mst a
    WHERE a.colb = 213841
    AND a.cdt = (SELECT MAX(b.cdt)

    FROM mjc_mst b
    WHERE b.cola = a.cola
    AND b.colb = a.colb

    )
    and exists ( select 1 from mjc_junk b, mjc_junk m where

    a.colb = m.cola
    and 1 != 1);

    prompt This query that returns valid results:
    select sum(a.colb)
    FROM mjc_mst a
    WHERE a.colb = 213841
    AND a.cdt = (SELECT MAX(b.cdt)

    FROM mjc_mst b
    WHERE b.cola = a.cola
    AND b.colb = a.colb
    group by 1

    )
    and exists ( select 1 from mjc_junk b, mjc_junk m where

    a.colb = m.cola
    and 1 != 1)

    prompt Go figure.

    end ----------------------

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    text/plain attachment: ESPN_Disclaimer.txt
  • Mark Richard at Oct 17, 2002 at 11:33 pm
    Raj,

    It's early in the morning and I may not be thinking straight yet, but...

    It's certainly interesting that the group by in the location shown changes
    the results - obviously a max() function should return 1 row only and
    therefore not require a group by. I'm more interested though in the second
    clause of your query... You have a second table aliased to "b" which isn't
    even used in the subquery, and you have a "and 1 != 1" clause.

    Is this a trick to get a particular result set? I would have thought that
    "and 1 != 1" will always return false and therefore the subquery will never
    return a row. Does the fact that one table is not joined to (ie: is a
    cartesian instead) somehow affect this? What business rule would a query
    like this implement? I ask purely because I'm always looking for new ways
    to express business rules in queries, just recently I learnt how to use an
    analytical function to return only the first two rows from a child table
    for each different requested parent in a single query.

    I'm wondering if you have hit a bug by reusing the alias "b" as a cartesian
    in a subquery - just trying to think of things the Oracle folk may never
    have tested for. If adding the group by changes those results though then
    yes, you seem to have found a bug of some sort, regardless of the query.

    Cheers.

    "Jamadagni,
    Rajendra" To: Multiple recipients of list ORACLE-L
    Subject: RE: Urgent problem with query in CBO Vs RBO
    Sent by:
    root_at_fatcity.com

    17/10/2002 23:13
    Please respond to
    ORACLE-L

    Here is an example ...

    RULE Hint works ... but we can't find all the SQLs that will fail. The only
    other option suggested by OWS is setting optimizer_features_enable='8.1.6'.
    It works fine ...

    Raj

    --------------- start ----------------------------

    set feedback off
    set define off
    drop table mjc_junk;
    drop table mjc_mst;
    prompt Creating MJC_JUNK...
    -- Create table
    create table MJC_JUNK
    (
    COLA NUMBER(9)

    );
    prompt Creating MJC_MST...
    -- Create table
    create table MJC_MST
    (
    COLA NUMBER(9) not null,
    COLB NUMBER(9) not null,
    CDT DATE

    );

    prompt Loading MJC_MST...
    insert into MJC_MST (COLA, COLB, CDT)
    values (1284457, 213841, to_date('06-06-2001 09:28:40', 'dd-mm-yyyy
    hh24:mi:ss'));
    commit;
    prompt 41 records loaded
    declare
    begin
    sys.dbms_stats.gather_table_stats(ownname => null,

    tabname => 'MJC_JUNK',
    estimate_percent => 20,
    block_sample => false,
    method_opt => 'FOR ALL COLUMNS',
    degree => 4,
    granularity => 'DEFAULT',
    cascade => true);

    end;
    /
    set feedback on
    set define on

    prompt This query returns invalid results:
    select sUM(a.colb)
    FROM mjc_mst a
    WHERE a.colb = 213841
    AND a.cdt = (SELECT MAX(b.cdt)

    FROM mjc_mst b
    WHERE b.cola = a.cola
    AND b.colb = a.colb

    )
    and exists ( select 1 from mjc_junk b, mjc_junk m where

    a.colb = m.cola
    and 1 != 1);

    prompt This query that returns valid results:
    select sum(a.colb)
    FROM mjc_mst a
    WHERE a.colb = 213841
    AND a.cdt = (SELECT MAX(b.cdt)

    FROM mjc_mst b
    WHERE b.cola = a.cola
    AND b.colb = a.colb
    group by 1

    )
    and exists ( select 1 from mjc_junk b, mjc_junk m where

    a.colb = m.cola
    and 1 != 1)

    prompt Go figure.

    end ----------------------

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.
    QOTD: Any clod can have facts, but having an opinion is an art! (See
    attached file: ESPN_Disclaimer.txt)

    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Privileged/Confidential information may be contained in this message.
    If you are not the addressee indicated in this message
    (or responsible for delivery of the message to such person),
    you may not copy or deliver this message to anyone.
    In such case, you should destroy this message and kindly notify the sender
    by reply e-mail or by telephone on (61 3) 9612-6999.
    Please advise immediately if you or your employer does not consent to
    Internet e-mail for messages of this kind.
    Opinions, conclusions and other information in this message
    that do not relate to the official business of
    Transurban City Link Ltd
    shall be understood as neither given nor endorsed by it.
    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mark Richard
    INET: mrichard_at_transurban.com.au

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    application/octet-stream attachment: ESPN_Disclaimer.txt
  • Jamadagni, Rajendra at Oct 18, 2002 at 1:59 pm
    I don't think the reply I sent last night made it to the list.

    We eventually traced it to the behavior of _unnest_subquery parameter.
    Setting it to false solved the problem. This was first reported in 817 and
    supposed to be fixed in 8173 and 901 code base. Looks like the lid on the
    "jar of bugs" is leaking ... and it made it into 920 code as well.

    The biggest problem we found that the explain plan shows that it doesn't
    even look at a major portion of the sql statement when it fails. 10053 trace
    shows that the optimizer looking at all tables involved and checking for
    different paths, but the explain plan is not reporting all the tables
    involved.

    Here is an example ...

    this doesn't work ---------------------
    select SUM(a.csed_dollars)
    FROM client_supplied_ep_dollars a
    WHERE a.csed_pob_id = 213841
    AND a.csed_date = (SELECT MAX(b.csed_date)
    FROM client_supplied_ep_dollars b
    WHERE b.csed_ep_number = a.csed_ep_number
    AND b.csed_pob_id = a.csed_pob_id)
    AND EXISTS (SELECT 'x'
    FROM v_log_master l,
    episode_airings e,
    units u
    WHERE u.ut_ea_ep_number = a.csed_ep_number
    AND u.ut_pob_id = a.csed_pob_id
    AND u.ut_disposition_ind IS NULL
    AND e.life_cycle_status > 2
    AND e.ep_number = u.ut_ea_ep_number
    AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/yyyy') and
    to_date('03/31/2002','mm/dd/yyyy')
    -- AND e.est_dt BETWEEN m_start_date AND m_end_date
    AND l.log_date = e.est_dt
    AND l.log_network = NVL(e.act_log_network, e.log_network)
    AND l.log_no = NVL(e.act_log_number,e.log_number)
    AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status
    = 9
    OR
    (NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date
    IS NULL)))

    SQL> /

    SUM(A.CSED_DOLLARS)

    1230000

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=41)

    1 0 SORT (AGGREGATE)

    2 1 VIEW (Cost=13 Card=1 Bytes=41)

    3 2 FILTER

    4 3 SORT (GROUP BY) (Cost=13 Card=1 Bytes=42)

    5 4 HASH JOIN (Cost=11 Card=1 Bytes=42)

    6 5 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2
    Card=12 Bytes=192)

    7 5 TABLE ACCESS (BY INDEX ROWID) OF

    'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=8 Card=12 Bytes=312)

    8 7 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2
    Card=1)

    SQL> spool off

    ------------------------ end --------------------------------------------

    And

    --------------------------- this works ----------------------------------

    select SUM(a.csed_dollars)

    FROM client_supplied_ep_dollars a
    WHERE a.csed_pob_id = 213841
    AND a.csed_date = (SELECT MAX(b.csed_date)
    FROM client_supplied_ep_dollars b
    WHERE b.csed_ep_number = a.csed_ep_number
    AND b.csed_pob_id = a.csed_pob_id
    group by 1) -- this group by fixes the query ...
    AND EXISTS (SELECT 'x'
    FROM v_log_master l,
    episode_airings e,
    units u
    WHERE u.ut_ea_ep_number = a.csed_ep_number
    AND u.ut_pob_id = a.csed_pob_id
    AND u.ut_disposition_ind IS NULL
    AND e.life_cycle_status > 2
    AND e.ep_number = u.ut_ea_ep_number
    AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/yyyy') and
    to_date('03/31/2002','mm/dd/yyyy')
    -- AND e.est_dt BETWEEN m_start_date AND m_end_date
    AND l.log_date = e.est_dt
    AND l.log_network = NVL(e.act_log_network, e.log_network)
    AND l.log_no = NVL(e.act_log_number,e.log_number)
    AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status
    = 9
    OR
    (NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date

    IS NULL)))

    SQL> /

    SUM(A.CSED_DOLLARS)

    150000

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=19)

    1 0 SORT (AGGREGATE)

    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_SUPPLIED_EP_DOLLARS'
    (Cost=3 Card=1 Bytes=19)
    3 2 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=1)

    4 3 SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=16)

    5 4 FIRST ROW (Cost=2 Card=1 Bytes=16)

    6 5 INDEX (RANGE SCAN (MIN/MAX)) OF 'CSED_PRIM' (UNIQUE)
    (Cost=2 Card=12488)
    7 3 NESTED LOOPS (Cost=8 Card=1 Bytes=65)

    8 7 NESTED LOOPS (Cost=5 Card=1 Bytes=55)

    9 8 NESTED LOOPS (Cost=5 Card=1 Bytes=41)

    10 9 NESTED LOOPS (Cost=4 Card=1 Bytes=35)

    11 10 TABLE ACCESS (BY INDEX ROWID) OF 'EPISODE_AIRINGS'
    (Cost=3 Card=1 Bytes=21)
    12 11 INDEX (UNIQUE SCAN) OF 'EPI_PK' (UNIQUE) (Cost=2
    Card=997448)
    13 10 TABLE ACCESS (BY INDEX ROWID) OF 'LOGS' (Cost=1
    Card=1 Bytes=14)
    14 13 INDEX (UNIQUE SCAN) OF 'LOG_PK_PRIM' (UNIQUE)
    15 9 TABLE ACCESS (BY INDEX ROWID) OF
    'INVOICE_NETWORK_XREF' (Cost=1 Card=1 Bytes=6)
    16 15 INDEX (UNIQUE SCAN) OF 'INVOICE_NETWORK_XREF_PK'
    (UNIQUE)
    17 8 INDEX (UNIQUE SCAN) OF 'SYS_C0018280' (UNIQUE)
    18 7 TABLE ACCESS (BY INDEX ROWID) OF 'UNITS' (Cost=3 Card=1
    Bytes=10)
    19 18 INDEX (RANGE SCAN) OF 'UT_POB_FRGN'

    (NON-UNIQUE)(Cost=2 Card=1)

    SQL> spool off

    ------------------------ end --------------------------------------------

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    text/plain attachment: ESPN_Disclaimer.txt
  • Johnston, Tim at Oct 18, 2002 at 3:54 pm
    "Looks like the lid on the "jar of bugs" is leaking ... and it made it into
    920 code as well."


    I think this a pretty common occurrence at Oracle... I think it has to do
    with the way they branch their code streams... Subsequent patches are often
    not integrated into the latest stream.... I've run into this kind of thing
    several times...


    Tim

    -----Original Message-----
    Sent: Friday, October 18, 2002 9:59 AM
    To: Multiple recipients of list ORACLE-L

    I don't think the reply I sent last night made it to the list.

    We eventually traced it to the behavior of _unnest_subquery parameter.
    Setting it to false solved the problem. This was first reported in 817 and
    supposed to be fixed in 8173 and 901 code base. Looks like the lid on the
    "jar of bugs" is leaking ... and it made it into 920 code as well.

    The biggest problem we found that the explain plan shows that it doesn't
    even look at a major portion of the sql statement when it fails. 10053 trace
    shows that the optimizer looking at all tables involved and checking for
    different paths, but the explain plan is not reporting all the tables
    involved.

    Here is an example ...

    this doesn't work ---------------------
    select SUM(a.csed_dollars)
    FROM client_supplied_ep_dollars a
    WHERE a.csed_pob_id = 213841
    AND a.csed_date = (SELECT MAX(b.csed_date)
    FROM client_supplied_ep_dollars b
    WHERE b.csed_ep_number = a.csed_ep_number
    AND b.csed_pob_id = a.csed_pob_id)
    AND EXISTS (SELECT 'x'
    FROM v_log_master l,
    episode_airings e,
    units u
    WHERE u.ut_ea_ep_number = a.csed_ep_number
    AND u.ut_pob_id = a.csed_pob_id
    AND u.ut_disposition_ind IS NULL
    AND e.life_cycle_status > 2
    AND e.ep_number = u.ut_ea_ep_number
    AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/yyyy') and
    to_date('03/31/2002','mm/dd/yyyy')
    -- AND e.est_dt BETWEEN m_start_date AND m_end_date
    AND l.log_date = e.est_dt
    AND l.log_network = NVL(e.act_log_network, e.log_network)

    AND l.log_no = NVL(e.act_log_number,e.log_number)
    AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status
    = 9
    OR
    (NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date

    IS NULL)))

    SQL> /

    SUM(A.CSED_DOLLARS)

    1230000

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=41)

    1 0 SORT (AGGREGATE)

    2 1 VIEW (Cost=13 Card=1 Bytes=41)

    3 2 FILTER

    4 3 SORT (GROUP BY) (Cost=13 Card=1 Bytes=42)

    5 4 HASH JOIN (Cost=11 Card=1 Bytes=42)

    6 5 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2
    Card=12 Bytes=192)

    7 5 TABLE ACCESS (BY INDEX ROWID) OF

    'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=8 Card=12 Bytes=312)

    8 7 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2
    Card=1)

    SQL> spool off

    ------------------------ end --------------------------------------------

    And

    --------------------------- this works ----------------------------------

    select SUM(a.csed_dollars)

    FROM client_supplied_ep_dollars a
    WHERE a.csed_pob_id = 213841
    AND a.csed_date = (SELECT MAX(b.csed_date)
    FROM client_supplied_ep_dollars b
    WHERE b.csed_ep_number = a.csed_ep_number
    AND b.csed_pob_id = a.csed_pob_id
    group by 1) -- this group by fixes the query ...
    AND EXISTS (SELECT 'x'
    FROM v_log_master l,
    episode_airings e,
    units u
    WHERE u.ut_ea_ep_number = a.csed_ep_number
    AND u.ut_pob_id = a.csed_pob_id
    AND u.ut_disposition_ind IS NULL
    AND e.life_cycle_status > 2
    AND e.ep_number = u.ut_ea_ep_number
    AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/yyyy') and
    to_date('03/31/2002','mm/dd/yyyy')
    -- AND e.est_dt BETWEEN m_start_date AND m_end_date
    AND l.log_date = e.est_dt
    AND l.log_network = NVL(e.act_log_network, e.log_network)

    AND l.log_no = NVL(e.act_log_number,e.log_number)
    AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status
    = 9
    OR
    (NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date

    IS NULL)))

    SQL> /

    SUM(A.CSED_DOLLARS)

    150000

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=19)

    1 0 SORT (AGGREGATE)

    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_SUPPLIED_EP_DOLLARS'
    (Cost=3 Card=1 Bytes=19)

    3 2 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=1)

    4 3 SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=16)

    5 4 FIRST ROW (Cost=2 Card=1 Bytes=16)

    6 5 INDEX (RANGE SCAN (MIN/MAX)) OF 'CSED_PRIM' (UNIQUE)
    (Cost=2 Card=12488)

    7 3 NESTED LOOPS (Cost=8 Card=1 Bytes=65)

    8 7 NESTED LOOPS (Cost=5 Card=1 Bytes=55)

    9 8 NESTED LOOPS (Cost=5 Card=1 Bytes=41)

    10 9 NESTED LOOPS (Cost=4 Card=1 Bytes=35)

    11 10 TABLE ACCESS (BY INDEX ROWID) OF 'EPISODE_AIRINGS'
    (Cost=3 Card=1 Bytes=21)

    12 11 INDEX (UNIQUE SCAN) OF 'EPI_PK' (UNIQUE) (Cost=2
    Card=997448)
    13 10 TABLE ACCESS (BY INDEX ROWID) OF 'LOGS' (Cost=1
    Card=1 Bytes=14)
    14 13 INDEX (UNIQUE SCAN) OF 'LOG_PK_PRIM' (UNIQUE)
    15 9 TABLE ACCESS (BY INDEX ROWID) OF
    'INVOICE_NETWORK_XREF' (Cost=1 Card=1 Bytes=6)
    16 15 INDEX (UNIQUE SCAN) OF 'INVOICE_NETWORK_XREF_PK'
    (UNIQUE)
    17 8 INDEX (UNIQUE SCAN) OF 'SYS_C0018280' (UNIQUE)
    18 7 TABLE ACCESS (BY INDEX ROWID) OF 'UNITS' (Cost=3 Card=1
    Bytes=10)
    19 18 INDEX (RANGE SCAN) OF 'UT_POB_FRGN'

    (NON-UNIQUE)(Cost=2 Card=1)

    SQL> spool off

    ------------------------ end --------------------------------------------

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Johnston, Tim
    INET: TJohnston_at_quallaby.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mercadante, Thomas F at Oct 18, 2002 at 4:04 pm
    isn't this what we pay the maintenance costs for - for them to get this
    thing right?


    no excuse for this shoddy bit of work.


    -----Original Message-----
    Sent: Friday, October 18, 2002 11:54 AM
    To: Multiple recipients of list ORACLE-L

    "Looks like the lid on the "jar of bugs" is leaking ... and it made it into
    920 code as well."


    I think this a pretty common occurrence at Oracle... I think it has to do
    with the way they branch their code streams... Subsequent patches are often
    not integrated into the latest stream.... I've run into this kind of thing
    several times...


    Tim

    -----Original Message-----
    Sent: Friday, October 18, 2002 9:59 AM
    To: Multiple recipients of list ORACLE-L

    I don't think the reply I sent last night made it to the list.

    We eventually traced it to the behavior of _unnest_subquery parameter.
    Setting it to false solved the problem. This was first reported in 817 and
    supposed to be fixed in 8173 and 901 code base. Looks like the lid on the
    "jar of bugs" is leaking ... and it made it into 920 code as well.

    The biggest problem we found that the explain plan shows that it doesn't
    even look at a major portion of the sql statement when it fails. 10053 trace
    shows that the optimizer looking at all tables involved and checking for
    different paths, but the explain plan is not reporting all the tables
    involved.

    Here is an example ...

    this doesn't work ---------------------
    select SUM(a.csed_dollars)
    FROM client_supplied_ep_dollars a
    WHERE a.csed_pob_id = 213841
    AND a.csed_date = (SELECT MAX(b.csed_date)
    FROM client_supplied_ep_dollars b
    WHERE b.csed_ep_number = a.csed_ep_number
    AND b.csed_pob_id = a.csed_pob_id)
    AND EXISTS (SELECT 'x'
    FROM v_log_master l,
    episode_airings e,
    units u
    WHERE u.ut_ea_ep_number = a.csed_ep_number
    AND u.ut_pob_id = a.csed_pob_id
    AND u.ut_disposition_ind IS NULL
    AND e.life_cycle_status > 2
    AND e.ep_number = u.ut_ea_ep_number
    AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/yyyy') and
    to_date('03/31/2002','mm/dd/yyyy')
    -- AND e.est_dt BETWEEN m_start_date AND m_end_date
    AND l.log_date = e.est_dt
    AND l.log_network = NVL(e.act_log_network, e.log_network)

    AND l.log_no = NVL(e.act_log_number,e.log_number)
    AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status
    = 9
    OR
    (NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date

    IS NULL)))

    SQL> /

    SUM(A.CSED_DOLLARS)

    1230000

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=41)

    1 0 SORT (AGGREGATE)

    2 1 VIEW (Cost=13 Card=1 Bytes=41)

    3 2 FILTER

    4 3 SORT (GROUP BY) (Cost=13 Card=1 Bytes=42)

    5 4 HASH JOIN (Cost=11 Card=1 Bytes=42)

    6 5 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2
    Card=12 Bytes=192)

    7 5 TABLE ACCESS (BY INDEX ROWID) OF

    'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=8 Card=12 Bytes=312)

    8 7 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2
    Card=1)

    SQL> spool off

    ------------------------ end --------------------------------------------

    And

    --------------------------- this works ----------------------------------

    select SUM(a.csed_dollars)

    FROM client_supplied_ep_dollars a
    WHERE a.csed_pob_id = 213841
    AND a.csed_date = (SELECT MAX(b.csed_date)
    FROM client_supplied_ep_dollars b
    WHERE b.csed_ep_number = a.csed_ep_number
    AND b.csed_pob_id = a.csed_pob_id
    group by 1) -- this group by fixes the query ...
    AND EXISTS (SELECT 'x'
    FROM v_log_master l,
    episode_airings e,
    units u
    WHERE u.ut_ea_ep_number = a.csed_ep_number
    AND u.ut_pob_id = a.csed_pob_id
    AND u.ut_disposition_ind IS NULL
    AND e.life_cycle_status > 2
    AND e.ep_number = u.ut_ea_ep_number
    AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/yyyy') and
    to_date('03/31/2002','mm/dd/yyyy')
    -- AND e.est_dt BETWEEN m_start_date AND m_end_date
    AND l.log_date = e.est_dt
    AND l.log_network = NVL(e.act_log_network, e.log_network)

    AND l.log_no = NVL(e.act_log_number,e.log_number)
    AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status
    = 9
    OR
    (NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date

    IS NULL)))

    SQL> /

    SUM(A.CSED_DOLLARS)

    150000

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=19)

    1 0 SORT (AGGREGATE)

    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_SUPPLIED_EP_DOLLARS'
    (Cost=3 Card=1 Bytes=19)

    3 2 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=1)

    4 3 SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=16)

    5 4 FIRST ROW (Cost=2 Card=1 Bytes=16)

    6 5 INDEX (RANGE SCAN (MIN/MAX)) OF 'CSED_PRIM' (UNIQUE)
    (Cost=2 Card=12488)

    7 3 NESTED LOOPS (Cost=8 Card=1 Bytes=65)

    8 7 NESTED LOOPS (Cost=5 Card=1 Bytes=55)

    9 8 NESTED LOOPS (Cost=5 Card=1 Bytes=41)

    10 9 NESTED LOOPS (Cost=4 Card=1 Bytes=35)

    11 10 TABLE ACCESS (BY INDEX ROWID) OF 'EPISODE_AIRINGS'
    (Cost=3 Card=1 Bytes=21)

    12 11 INDEX (UNIQUE SCAN) OF 'EPI_PK' (UNIQUE) (Cost=2
    Card=997448)
    13 10 TABLE ACCESS (BY INDEX ROWID) OF 'LOGS' (Cost=1
    Card=1 Bytes=14)
    14 13 INDEX (UNIQUE SCAN) OF 'LOG_PK_PRIM' (UNIQUE)
    15 9 TABLE ACCESS (BY INDEX ROWID) OF
    'INVOICE_NETWORK_XREF' (Cost=1 Card=1 Bytes=6)
    16 15 INDEX (UNIQUE SCAN) OF 'INVOICE_NETWORK_XREF_PK'
    (UNIQUE)
    17 8 INDEX (UNIQUE SCAN) OF 'SYS_C0018280' (UNIQUE)
    18 7 TABLE ACCESS (BY INDEX ROWID) OF 'UNITS' (Cost=3 Card=1
    Bytes=10)
    19 18 INDEX (RANGE SCAN) OF 'UT_POB_FRGN'

    (NON-UNIQUE)(Cost=2 Card=1)

    SQL> spool off

    ------------------------ end --------------------------------------------

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 16, '02 at 4:29p
activeOct 18, '02 at 4:04p
posts10
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase