Re: need help tuning a very large delete

does this hold true for hash_area_size? We are doing alot of

insert into table
select * from staging_table
where pk not in (select /*+ hash_aj */ pk from table);

We have rather large tables and i found i have to jack up my hash_area_size. is it not worth jacking this up if I use parallel? I cant in general cases because we run 50 of these large inserts at a time and it doesnt scale.

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3f6b7381$0$6528$afc38c87_at_news.optusnet.com.au...
> Ryan Gaffuri wrote:
>
>
> >
> > so your saying the tuning course says not to have a high
> > sort_area_size if your doing parallel query? Wow....
>
>
> Uh huh. Interesting, isn't it? (Well, 1 MB is quite big, I suppose.
They're
> just saying it seems non-productive to go bigger than that. I guess it's
> because there's an inherent limitation with parallelism, which is that the
> slaves have to message each other to co-ordinate their activity, and that
> messaging is a bottleneck. Throw more sort memory at the slaves, and they
> won't be able to make great use of it because the messaging holds them
back
> from doing so. But that's not in the notes, so I'm just guessing on that
> one. Jonathan???!).
>
> >
> > I thought sort_area_retained size was what Oracle reduced the
> > sort_area_size to similiar to optsize in v$rollstat.
> >
>
> Classic error. It's what gets used for the merge phase of a query (when
you
> bring back the swapped-to-disk partial sort runs and merge them into a
> single, totally sorted result set). It's not 'what happens to
> sort_area_size at the end of a sort' at all.
>
> > so your saying that 'sort_area_size' is the amount per sort, not per
> > session?
>
> Correct, that's what I'm saying, because that's my understanding (and what
> the course notes say... was it you going on the Performance Tuning course
> soon? Then check out Chapter 9, page 15).
>
> >So in a parallel query I can have 2 x sort_area_size per
> > slave right?
>
> Worst case, yes. It's what you should allow for, anyway.
>
> >
> > in a non-parallel session I can have n X sort_area_size where n=
> > number of sorts.
>
> Correct.
>
> Regards
> HJR
Received on Sat Sep 20 2003 - 12:22:43 CDT