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