Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query runs slower under Oracle8i

Re: Query runs slower under Oracle8i

From: David Fitzjarrell <oratune_at_aol.com>
Date: Tue, 07 Nov 2000 16:49:57 GMT
Message-ID: <8u9brh$lil$1@nnrp1.deja.com>

In our last gripping episode "W³asiuk Krzysztof" <wlasiukk_at_prokom.pl> wrote:
> 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
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Sorry to burst your bubble but Howard is correct -- the only parameter that affects whether sorts occur in memory or on disk is sort_area_size. Yes, sort_area_retained_size affeects how that area is cleaned out but if the sort_area_size is too small then most sorts will occur on disk. Definitely not rocket science.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Nov 07 2000 - 10:49:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US