Re: Query runs slower under Oracle8i

For heavens' sake! There is no mystery surrounding the 8i sort memory parameters. You make it sound like there's a dramatic change or 50 new parameters... it's sort_area_size and has been for at least 2 versions.

*Assuming* that his sort_area_size is the same in 8i as it was under 7 (a bad move in the first place, since 8i is inherently more resource hungry) then yes, your idea of checking that the temporary tablespace is really set to be "temporary" is a good one (check dba_tablespaces to be certain). He's also be well-advised to ensure that the temporary tablespace is properly created with the 'tempfile' keyword, and is using locally managed extents of a unifrom size.

Regards
HJR

--

---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------


"W³asiuk Krzysztof" <wlasiukk_at_prokom.pl> wrote in message
news:8u8ftr$6r9$1_at_korweta.task.gda.pl...


> 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

> >

> >

> >

>

>