Re: need help tuning a very large delete

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.
>>
>>