Re: Doing large sort in RAM - sort workarea manipulation

For special cases it's probably wiser to do:

    alter session set workarea_size_policy = manual then set the sort_area_size.

The "twice the value" approximation is misleading - it dates back to version 5.1 probably, and was never justified then.
The actual "scaling" requirement depends on:

    length of the rows you are sorting - a large number of very small rows will need more memory than a small number of very long rows assuming "rows * length" is the same.

    which sort algorithm you are using version 1 or version 2. Some sort (e.g. analytic, aggregate) still require a v1 sort, which averages three pointers per item sorted compared to v2 which needs only one pointer per item

    whether you are using 32 bit or 64 bit - because pointers double in size.

For example: "create index" is a good example of sorting a large number of short rows. In a case I use for a presentation I do on sorting, I found I needed 510 MB of memory to create an index whose final size was 100MB when running 64 bit Oracle using a v1 sort.

You are correct about a hard limit on the total memory allowed for a single sort operation. The last time I checked (possibly only 10g) it was either 2GB or 4GB (but I can't remember which - but I'll believe your observation of 2GB). Moreover, I think it was limited whether defined by the sort_area_size or indirectly by the _smm_max_size.

The drawback to going parallel (which removes some limitations because every single server you activate could use up to the hard limit for sorting) is that you then introduce a large volume of inter-process messaging before sorting starts.

Since you mention Informatica, you need to consider the full life-cycle of WHY you are sorting. I have found cases in the past where the best strategy for completing an Informatica job is to use a "NOSORT" type of option because most of the time spent turned out to be from moving the results out to Informatica and then back into the database. In such cases, if you sort in the database, the total run time increases by the time it takes to complete the sort; whereas if you have a no-sort operation it's slower, but not the limiting factor in getting the data out to Informatica.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

• Original Message ----- From: "Grzegorz Goryszewski" <grzegorzof_at_interia.pl> To: <oracle-l_at_freelists.org> Sent: Saturday, November 12, 2011 10:36 AM Subject: Doing large sort in RAM - sort workarea manipulation

Hi,
lets say I'm on 10.2.0.3 and trying to figure out how to do large sorts only in RAM (128GB memory server) .
So Oracle said:

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 .

So I thought only two options left .
We can do sort via parallel slaves (not sure how force CBO to do that) the limit for sort for PX operations is bigger than serial once and we have got limit x # of parralel sessions.
And the second idea is do sort via NOSORT :) , looks like with proper index there is a chance
CBO will skip sort operations .
Meantime I've found that sort_area_size is hardlimited as well (probably 2GB).

Could You please share Your experience ? And before start questioning why to do sorts at all, please bare in mind Informatica force us to do so :).
Regards
GregG

--
http://www.freelists.org/webpage/oracle-l