RE: bad execution plan

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Tue, 30 Apr 2002 09:59:36 -0800
Message-ID: <F001.00453BBB.20020430095936@fatcity.com>

Joe:  

Me things you are running into one of my favorite optimizer bugs that was SUPPOSEDLY fixed in 8.1.7 (I haven't been able to recreate in 9i...yet.) The CBO sometimes generates extremely poor execution plan when you are sorting on a column in the where clause.  

Try this:
SELECT /*+ NO_MERGE(d) */

      d.EVENT# c1,
      count(distinct d.CONCO#) c2,
      d.EVDESC c3,
      d.CATTYP c4 ,
      count(distinct d.CATCO#) c5
FROM (select  T1.EVENT# c1,
            T1.CONCO# c2,
            T2.EVDESC c3,
            T3.CATTYP c4 ,
            T3.CATCO# c5
         from STAGING.BECONS T1,
                 STAGING.EVMAST T2,
                 STAGING.BECATD T3
            where
                  T1.EVENT# = T2.EVMBRN and
                  T1.EVENT# = T3.EVENT#
            and T1.EVENT#='EV000154') d

group by d.EVENT#, d.EVDESC, d.CATTYP
order by 1 asc, 3 asc, 4 asc;  

Caver

-----Original Message-----
Sent: Tuesday, April 30, 2002 1:24 PM
To: Multiple recipients of list ORACLE-L

AIX, oracle 8.1.7.2, all 3 tables compute statistics, first_rows optimizer_mode  

a simple 3 table join, joined on the appropriate columns, works fine.  

add a fixed constant to the mix and poof, 2 merge-join cartesian.  

Here is first query w/o the fixed value:  

select
  T1.EVENT# c1,
  count(distinct T1.CONCO#) c2,
  T2.EVDESC c3,
  T3.CATTYP c4 ,
  count(distinct T3.CATCO#) c5

from STAGING.BECONS T1,
     STAGING.EVMAST T2,
     STAGING.BECATD T3
where
      T1.EVENT# = T2.EVMBRN and
      T1.EVENT# = T3.EVENT#

group by T1.EVENT#, T2.EVDESC, T3.CATTYP order by 1 asc, 3 asc, 4 asc;  

Plan:
| SELECT STATEMENT |
| SORT GROUP BY |
| HASH JOIN |
| HASH JOIN |
| TABLE ACCESS FULL |EVMAST
| TABLE ACCESS FULL |BECONS
| TABLE ACCESS FULL |BECATD
  Now add one line with a hardcoded value and it appears that oracle is totally ignoring the rest of the where clause(proven later): select
  T1.EVENT# c1,
  count(distinct T1.CONCO#) c2,
  T2.EVDESC c3,
  T3.CATTYP c4 ,
  count(distinct T3.CATCO#) c5

from STAGING.BECONS T1,
     STAGING.EVMAST T2,
     STAGING.BECATD T3
where
      T1.EVENT# = T2.EVMBRN and
      T1.EVENT# = T3.EVENT#

and T1.EVENT#='EV000154'
group by T1.EVENT#, T2.EVDESC, T3.CATTYP order by 1 asc, 3 asc, 4 asc;

plan:
| SELECT STATEMENT |
| SORT GROUP BY |
| MERGE JOIN CARTESIAN |
| MERGE JOIN CARTESIAN |
| TABLE ACCESS FULL |EVMAST
| SORT JOIN |
| TABLE ACCESS BY INDEX ROWID |BECONS
| INDEX RANGE SCAN |IDX01_BECONS
| SORT JOIN |
| TABLE ACCESS BY INDEX ROWID |BECATD
| INDEX RANGE SCAN |INDX01_BECATD
  Now proven here is if i leave out the joins altogether, i get the close the the same execution plan as above :
select
  T1.EVENT# c1,
  count(distinct T1.CONCO#) c2,
  T2.EVDESC c3,
  T3.CATTYP c4 ,
  count(distinct T3.CATCO#) c5

from STAGING.BECONS T1,
     STAGING.EVMAST T2,
     STAGING.BECATD T3

where
T1.EVENT#='EV000154'
group by T1.EVENT#, T2.EVDESC, T3.CATTYP order by 1 asc, 3 asc, 4 asc;  

Plan:
| SELECT STATEMENT |
| SORT GROUP BY |
| MERGE JOIN CARTESIAN |
| MERGE JOIN CARTESIAN |
| TABLE ACCESS BY INDEX ROWID |BECONS
| INDEX RANGE SCAN |IDX01_BECONS
| SORT JOIN |
| TABLE ACCESS FULL |EVMAST
| SORT JOIN |
| TABLE ACCESS FULL |BECATD
  I'm looking for any ideas what the heck the CBO is doing or is this some bug of some sorts?  

thanks, joe  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

Received on Tue Apr 30 2002 - 12:59:36 CDT