Re: Doing large sort in RAM - sort workarea manipulation
From: Greg Rahn <greg_at_structureddata.org>
Date: Sat, 12 Nov 2011 07:45:35 -0800
Message-ID: <CAGXkmiu+KGaDOTzcQAP6AxxrjcXXpC0p86Qyx5Lq=jodcPBZDw_at_mail.gmail.com>
If you need to allocate more pga... then parallel execution will probably be your friend. PX lets you address more memory for the sort by scaling it out. At that point you just need to have a large enough pga_aggregate_target and large enough DOP to keep the operation from spilling.
Date: Sat, 12 Nov 2011 07:45:35 -0800
Message-ID: <CAGXkmiu+KGaDOTzcQAP6AxxrjcXXpC0p86Qyx5Lq=jodcPBZDw_at_mail.gmail.com>
If you need to allocate more pga... then parallel execution will probably be your friend. PX lets you address more memory for the sort by scaling it out. At that point you just need to have a large enough pga_aggregate_target and large enough DOP to keep the operation from spilling.
On Sat, Nov 12, 2011 at 2:36 AM, Grzegorz Goryszewski
<grzegorzof_at_interia.pl> wrote:
> Example:
> If you like to use a sort area size of 2GB for a special operation (e.g
> for the creating of an index on a large table) you could set the values
> as follows:
>
> PGA_AGGREGATE TARGET = 10G
> "_PGA_MAX_SIZE" = 2G
> "_SMM_MAX_SIZE" = 2097152
>
> but what If I need more , I've found some claims that maximum size of
> sort area is hardcoded to 4GB and cant be
> exceeded .
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Sat Nov 12 2011 - 09:45:35 CST