Re: strange execution plan

frederic.mockel_at_gmx.de (Freddy) wrote in message news:<1704b190.0408050052.a0efbfb_at_posting.google.com>...
> Hi,
>
> we are using Oracle 8.1.7 on W2k ( 2 CPUs, 3 GB Ram) and following
> statement
>
> SELECT
> bpf.PF_ID, ku.KUNDENNR, ku.KUNDENTYP, ku.BONITAET_ID,
> mbra.ID, ku.RVB_ID, bpf.LAUFZEIT_ID, bpf.WAEHRUNGS_ID ,
> sum(bpf.summe_brutto) AS BRUTTO,
> sum(bpf.SUMME_BRUTTO - (case when sw.WERT_GCPM is null then 0
> else sw.WERT_GCPM end))
> AS NETTO, sum(sw.WERT_GCPM) AS SICHWERT,
> ku.ku_txt_01, ku.ku_txt_02, ku.ku_txt_03, ku.ku_txt_04,
> ku.ku_txt_05, ku.ku_txt_06, ku.ku_txt_07, ku.ku_txt_08,
> ku.ku_txt_09,
> ku.ku_txt_10, ku.ku_txt_11, ku.ku_txt_12, ku.ku_txt_13
> FROM
> KUNDE ku, MASTERBRANCHE mbra,
> KU_RVB_BRANCHE kurvbbra, HIERDIMBEZ1 hbra ,
> BASIS_GCPM bpf, SICHWERT_GCPM sw
> WHERE
> bpf.waehrungs_id = sw.waehrungs_id (+)
> and bpf.sz_schluessel = sw.sz_schluessel (+)
> AND bpf.laufzeit_id = sw.laufzeit_id (+)
> and sw.flagaktuell = 1
> and sw.zweckerkl = 'T'
> and (kurvbbra.BEZUG_ID = ku.ID)
> AND (kurvbbra.FLAG = 'K') AND (hbra.ID = kurvbbra.BRANCHE_ID)
> AND (hbra.DIM_BEZK = mbra.BRANCHEN_NR) AND
> (kurvbbra.LAND_ISO = mbra.LAND_ISO) AND (bpf.VKUNDEN_ID = ku.ID)
> GROUP BY
> bpf.PF_ID, ku.kundennr, ku.kundentyp, ku.bonitaet_id,
> mbra.id, ku.rvb_id, bpf.laufzeit_id, bpf.waehrungs_id,
> ku.ku_txt_01, ku.ku_txt_02, ku.ku_txt_03, ku.ku_txt_04,
> ku.ku_txt_05, ku.ku_txt_06, ku.ku_txt_07, ku.ku_txt_08,
> ku.ku_txt_09,
> ku.ku_txt_10, ku.ku_txt_11, ku.ku_txt_12, ku.ku_txt_13
> ORDER BY
> ku.kundennr
>
>
> The plan therefor is
>
> ----------------------------------------------------------
> 0 ° SELECT STATEMENT Optimizer=CHOOSE (Cost=2175415251882 Card=2
> °
> 2119102505655 Bytes=4268986783591420)
>
> 1 0 SORT (ORDER BY) (Cost=2175415251882 Card=22119102505655 By
> °
> tes=4268986783591420)
>
> 2 1 SORT (GROUP BY) (Cost=2175415251882 Card=22119102505655
> °
> Bytes=4268986783591420)
>
> 3 2 NESTED LOOPS (Cost=243466 Card=22119102505655 Bytes=42
> °
> 68986783591420)
>
> 4 3 HASH JOIN (Cost=243466 Card=1065597742 Bytes=1832828
> °
> 11624)
>
> 5 4 TABLE ACCESS (FULL) OF 'MASTERBRANCHE' (Cost=13 Ca
> °
> rd=14598 Bytes=467136)
>
> 6 4 HASH JOIN (Cost=3462 Card=729961462 Bytes=10219460
> °
> 4680)
>
> 7 6 TABLE ACCESS (FULL) OF 'BASIS_GCPM' (Cost=191 Ca
> °
> rd=289128 Bytes=10119480)
>
> 8 6 HASH JOIN (Cost=3006 Card=252470 Bytes=26509350)
> °
> 9 8 INDEX (FULL SCAN) OF 'X_HIERDIMBEZ1_1' (NON-UN
> °
> IQUE) (Cost=1 Card=152 Bytes=912)
>
> 10 8 HASH JOIN (Cost=3004 Card=252470 Bytes=2499453
> °
> 0)
>
> 11 10 TABLE ACCESS (FULL) OF 'KU_RVB_BRANCHE' (Cos
> °
> t=92 Card=252470 Bytes=2777170)
>
> 12 10 TABLE ACCESS (FULL) OF 'KUNDE' (Cost=2388 Ca
> °
> rd=1620048 Bytes=142564224)
>
> 13 3 TABLE ACCESS (BY INDEX ROWID) OF 'SICHWERT_GCPM'
> °
> 14 13 INDEX (RANGE SCAN) OF 'XSICHWERTGCPM1' (NON-UNIQUE
> °
> )
>
>
> The thing i dont understand ist, why are there so high cards and so
> many galaxie dollars (costs)
>
> Freddy

Freddy, there are row filter conditions on a table that is involved in an outer join as the outer table. This is normally an error in coding the query according to Dan Tow in his book, "SQL Tuning", ISBN 0-596-00573-3 pg 198-201.

Since table SW will always return a row either with data or nulls it is questionable if the query result is even correct to begin with. [Note I do agree with the author on all his points but he does raise several interesting and valid one including the correct coding of outer joins]

Try updating the statistics. Compare plans. Then make sure the query driving order matches what you believe should be the correct order. If you think the hash joins should be replaced with indexed access you can order the from clause tables in driving table order and try an ORDERED hint. To get rid of the hash joins I expect you will also need to add a USE_NL hint.

Compare the hinted run time to the current plan.

HTH -- Mark D Powell -- Received on Thu Aug 05 2004 - 09:21:59 CDT