Re: Query runs slower under Oracle8i

Oh dear - isn't it sort operation performed on disk (temp tablespace is usuall 'temporary' and is used almost only for sorting on disk wich is very slow and You should to avoid it by enlarge memory structures used fo sorting. There are some new initial parameters in 8i to maintain memory - find the difference!)
Try to look for session statistics to find a problem.

wishing successfull tuning operation

wlasiukk

U¿ytkownik O.K.Man <okman_at_netvigator.com> w wiadomo¶ci do grup dyskusyjnych napisa³:8u7j12$sj12_at_imsp212.netvigator.com...
> 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
>
>
>
Received on Tue Nov 07 2000 - 02:53:42 CST