Re: Query runs slower under Oracle8i

The best point so far.

Assuming you have exactly the same
data, with exactly the same stats in
place, and exactly the same init.ora
parameters ...

8.1 has some very clever new optimisation algorithms - unfortunately they are sometimes too clever by half, and result in less suitable execution paths.

For example, you may find that 7..3.4 cannot unfold you in-line view and merge it with the outer table, so does a small instantiation followed by a small merge join; whereas
8.1 may be able to unfold the view, and end up doing a much larger merge join as a
consequence. (Alternatively, it may be
7.3.4 that does unfold, and 8.1 that is
really smart and doesn't - it's hard to tell without calling on EXPLAIN PLAN).

--

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

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison Wesley Longman
Book bound date now 8th Dec 2000

jdarrah_co_at_my-deja.com wrote in message <8u9g0h$pob$1_at_nnrp1.deja.com>...


>Have you done an explain plan to see if this query has the same plan on

>both database versions?

>In article <8u7j12$sj12_at_imsp212.netvigator.com>,

>  "O.K.Man" <okman_at_netvigator.com> wrote:

>> Dear all,

>>

>> I have a query in the format like this :

>>

>> SELECT TAB1.COL1

>> ,TAB1.COL2

>> ,SUM(TMP.COL4)

>> FROM TAB1

>> ,(SELECT TAB2.COL1

>> ,TAB3.COL2

>> ,TAB3.COL3

>> ,SUM(TAB3.COL4) COL4

>> FROM TAB2

>> ,TAB3

>> WHERE TAB2.COL2 = TAB3.COL1

>> AND TAB2.COL3 = TAB3.COL5

>> GROUP BY

>> TAB2.COL1

>> ,TAB3.COL2

>> ,TAB3.COL3

>> ) TMP

>> WHERE TAB1.COL3 = TMP.COL1

>> AND TAB1.COL4 = TMP.COL2

>> GROUP BY

>> TAB1.COL1

>> ,TAB1.COL2

>>

>> which run fine under Oracle7 (7.3.4). However, under Oracle8i

 (8.1.6), I


>> found that the query generated a lot of file i/o on the temporary

 tablespace


>> (there is no file i/o on the temporary tablespace under Oracle7

 during the


>> execution of the same query) and caused the query run much slower

 than under


>> Oracle7. Is there any default setting for Oracle8i would cause the

 optimizer


>> to write the temporary result to disk ?

>>

>> Thanks in advance,

>>

>> O.K.Man

>>

>>

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.