Re: Doing large sort in RAM - sort workarea manipulation
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 12 Nov 2011 12:05:58 -0000
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@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
