Re: Cost- vs. Rule-Optimizer

1. You may have unreasonably high values for sort_area_size and dB_file_multiblock_read_count
2. Do you need first_row or all_rows optimisation ?
3. Are any of your tables overallocated with lots of gaps
4. Do any of your tables have extremes in data distribution in column involved in the joins
5. Have you indicated (using the relevant init.ora parameters) the probability of index blocks being in memory and the weighting that Oracle should give to index blocks ? (I think these exist in 8.0.5, but might be 8.1.5)

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Rene' Kulschewski wrote in message <7pj06e$9k4$1_at_nnrp1.deja.com>...
>Hi,
>
>i have the following sql-statement running against 8.0.5/NT:
>
>SELECT W.WSCHEIN_NR,T.DATUM,T2.ID, L.*
>FROM WIDA W, TOUR T2,TOURP T, AFTP A, TGUT T1, LAGA L
>WHERE W.TOUR_ID=T2.ID
>AND T2.TOUR_ID=T.TOUR_ID
>AND T2.DATUM=T.DATUM
>AND T.AFTP_ID=A.ID
>AND A.TGUT_ID = T1.ID
>AND T1.LAGA_ID = L.ID
>
>If i use cost-based optimizing (the tables and indizees are analyzed),
>the following execution-plan is generated:
>OPERATION OPTIONS OBJECT_NAME
>-------------------- ----------- -----------
>SELECT STATEMENT
>HASH JOIN
>TABLE ACCESS FULL WIDA
>HASH JOIN
>TABLE ACCESS FULL LAGA
>HASH JOIN
>TABLE ACCESS FULL TGUT
>HASH JOIN
>TABLE ACCESS FULL TOUR
>MERGE JOIN
>SORT JOIN
>TABLE ACCESS FULL TOURP
>SORT JOIN
>TABLE ACCESS FULL AFTP
>
>The execution time for this statement will be about 45 sec.
>
>If i use explizit rule-based optimization (via hint /*+ RULE +*/) i get
>the following plan:
>
>OPERATION OPTIONS OBJECT_NAME
>------------------ ------------------ ---------------------
>SELECT STATEMENT
>NESTED LOOPS
>NESTED LOOPS
>NESTED LOOPS
>NESTED LOOPS
>NESTED LOOPS
>TABLE ACCESS FULL LAGA
>TABLE ACCESS BY INDEX ROWID TGUT
>INDEX RANGE SCAN TGUT_LAGA_ID_IDX
>TABLE ACCESS BY INDEX ROWID AFTP
>INDEX RANGE SCAN AFTP_TGUT_IDX
>TABLE ACCESS BY INDEX ROWID TOURP
>INDEX RANGE SCAN TOURP_AFTPID_IDX
>TABLE ACCESS BY INDEX ROWID TOUR
>INDEX RANGE SCAN TOUR_TOURID_DATUM_IDX
>TABLE ACCESS BY INDEX ROWID WIDA
>INDEX RANGE SCAN WIDA_TOURID_IDX
>
>The execution time is about 0,05 sec.
>
>How can this happen ?
>
>Since the Oracle-doc points out, that the rule-based approach in future
>releases will not be supported, what should i do to get the same
>results with cost-based optimization as with rule-based optimization ?
>
>Regards
>Rene' Kulschewski
>____________________________________________
>IVU Traffic Technologies AG, Berlin, Germany
>
>--
>IVU Traffic Technologies AG
>Bundesallee 88, D-12161 Berlin
>Fon: +49.30.85 906-327, Fax: +49.30.85 906-313
>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Fri Aug 20 1999 - 03:24:17 CDT