Re: Query runs slower under Oracle8i

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
> > >
> > >
> > >
> >
> >
>
>
Received on Tue Nov 07 2000 - 09:43:39 CST