Doing large sort in RAM - sort workarea manipulation
Date: Sat, 12 Nov 2011 11:36:12 +0100
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
-> should be set in minimum of twice the desired work area size. The
default value is 200Mb.
-> normally this parameter is not needed but maybe under certain
-> if set it should be equal to the desired work area size (in kb !)
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 :).
Nie zmieniaj opon, zmien auto!
http://linkint.pl/f2a7c Received on Sat Nov 12 2011 - 04:36:12 CST