Re: need help tuning a very large delete

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3f6abd5a$0$28120$afc38c87_at_news.optusnet.com.au>...
> Ryan wrote:
>
> > didnt realize that... its on ixora, but is it on the otn docs? that is
> > nasty and dangerous.
>
>
> It is taught on the Oracle Performance Tuning course (though not explained
> particularly well). So I would imagine that it's in the docs, because
> that's where a lot of the training material originates from.
>
> It's not particularly 'nasty' though. And it's not unique to parallelism.
> Any query can suffer the same problem. Suppose you join two tables together
> on a column that isn't indexed (so there's a sort merge join required) and
> then you slap an 'order by' clause in. Whilst the order by is being
> processed in the sort_area_size, two dollops of sort_area_retained_size
> will be in use for the join sorts. That's potentially three lots of
> sort_area_size (assuming s_a_r_s is set to the same as s_a_s), on a single,
> non-parallelised query.
>
> With parallelism, Oracle limits the number of concurrent sorts to 2 per
> slave, so you need 2 lots of sort_area_size x 2 x the degree of
> parallelism. And then, hold you breath, you need sort_area_retained_size x
> degree of parallelism x the number of concurrent sorts above 2.
>
> With all that said, there's an interesting note in the Performance Tuning
> course notes that says, and I quote, 'If you can still afford the memory,
> the optimal value for Sort_area_size and Sort_area_retained_size with
> parallel query is one megabyte. In testing, larger values than this have
> not improved performance significantly'. Make of that what you will.
>
> Thank God for PGA_AGGREGATE_TARGET is all I can say!
>
> Regards
> HJR
>
>
> >
> > if it consumes all that ram can you get the same 'out of memory' error?
> > and have the transaction end?
> > "Tanel Poder" <change_to_my_first_name_at_integrid.info> wrote in message
> > news:3f6a148f$1_1_at_news.estpak.ee...
> >>
> >> > its gonna run all night. Tired of messing around. Ill do it in
> >> > parallel. but the big slow down is the sorting to temp. Its evil.
> >> >
> >> > 4.2 GB table minus a 2.5GB table then a create table as of a 1.7GB
> >> > table. with just 83MB of sort_area_size. if i could jack up the
> >> > sort_area_size to 1GB we would be fine.
> >>
> >> Remember that with parallel execution, each slave can consume up to 2x
> >> sort_area_size. Make sure you don't run out of memory.
> >> Check ixora for more information about this.
> >>
> >> Tanel.
> >>
> >>

so your saying the tuning course says not to have a high sort_area_size if your doing parallel query? Wow....

I thought sort_area_retained size was what Oracle reduced the sort_area_size to similiar to optsize in v$rollstat.

so your saying that 'sort_area_size' is the amount per sort, not per session? So in a parallel query I can have 2 x sort_area_size per slave right?

in a non-parallel session I can have n X sort_area_size where n= number of sorts. Received on Fri Sep 19 2003 - 09:31:02 CDT