Doing large sort in RAM - sort workarea manipulation
Date: Sat, 12 Nov 2011 11:36:12 +0100
Message-ID: <4EBE4C1C.5090101_at_interia.pl>
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:
1. PGA_AGGREGATE TARGET
-> should be set to five times the desired work area size
2. _PGA_MAX_SIZE
-> should be set in minimum of twice the desired work area size. The
default value is 200Mb.
3. _SMM_MAX_SIZE
-> normally this parameter is not needed but maybe under certain
circumstances
-> if set it should be equal to the desired work area size (in kb !)
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
Nie zmieniaj opon, zmien auto!
http://linkint.pl/f2a7c
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Nov 12 2011 - 04:36:12 CST