Re: Query runs slower under Oracle8i

Hiya,

I *hope* I didn't say that parameters haven't changed at all between versions! There's stacks of new ones every release... but, er, sort_area_size is still the one you're after as far as determining whether you get your sorts in memory or swapping out to disk. Sort_area_retained_size affects how big the PGA is retained at after the sort's finished, and the others tend to affect what happens once the system decides that swapping out to disk must take place.

The first thing that springs to mind is that a 64K PGA is small, and if you've the memory, it would be a good idea to increase it a bit (to say 128K?). But that's by way of general advice -I guess your real concern is why it goes like the clappers under 8 and does disk swaps under 8i?

I can only unscientifically suggest again that 8i is a known resource-hog, and that it wouldn't surprise me to discover that you need a fraction more PGA to perform exactly the same sorts between versions (but I haven't tested that). Second, and rather obviously, you've got sort_area_retained_size set differently between your two versions. Try setting them identically, and see how you go. Third, you might be getting a lot of i/o if your temporary tablespace is not properly set to be temporary -check the contents column of dba_tablespaces and make sure it doesn't say 'permanent' (which would mean that every disk-based sort has to mark out its own extents and drop them afterwards. If it's proper 'temporary' tablespace, extents are merely re-used, which makes things go much faster) -in other words, perhaps your sorts *were* swapping to disk in 8, but you didn't really notice because the tablespace was configured correctly, and maybe now its not.

Incidentally, guru-meister Steve Adams states that your PGA has to be at least 640K before sort_direct_writes actually has any effect. Check out http://www.ixora.com.au/q+a/sorts.htm

Glad you liked the Nun! I'm inspired every time I look at the tongue!!

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:8u97uh$3fd$1_at_korweta.task.gda.pl...


> thanks for suport ... but

>

> select name,value,description from v$parameter where upper(name) like

> '%SORT%'

>

> in 8.0.5

> NAME                           VALUE      DESCRIPTION

> ------------------------------ ---------- --------------------------------

 --


> ------------------------

> nls_sort                                  NLS linguistic definition name

> sort_area_size                 65536      size of in-memory sort work area

> sort_area_retained_size        65536      size of in-memory sort work area

> retained between fetch calls

> sort_multiblock_read_count     2          multi-block read count for sort

>

> in 8.1.6

> NAME                           VALUE      DESCRIPTION

> ------------------------------ ---------- --------------------------------

 --


> ------------------------

> nls_sort                                  NLS linguistic definition name

> sort_area_size                 65536      size of in-memory sort work area

> sort_area_retained_size        0          size of in-memory sort work area

> retained between fetch calls

> sort_direct_writes             AUTO       use direct write

> sort_write_buffers             2          number of sort direct write

> buffers

> sort_write_buffer_size         32768      size of each sort direct write

> buffer

> sort_spacemap_size             512        size of sort disk area space map

> sort_read_fac                  20         multi-block read factor for sort

>

>

> actually i don't have any 7, but it should be closer to 8.0.5. Please have

 a


> glance at sort_area_retained_size (the parameter values are defaults!)

>

>

> PS : your homesite is very nice, especially this mindhungry Nun - it takes

> me about 15s. to find the click

>

> Regards

> WK

>

> U¿ytkownik Howard J. Rogers <howardjr_at_www.com> w wiadomo¶ci do grup

> dyskusyjnych napisa³:3a07dd02_at_news.iprimus.com.au...

> > 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

> > > >

> > > >

> > > >

> > >

> > >

> >

> >

>

>