increase sql_sort_area to 8 GB does not work
Hi every one,
I have the following configuration on Oracle 11.2.0.4, Suse linux 64
pga_aggregate_target = 160G
_pga_max_size = 32G
_smm_max_size = 16777216
workarea_size_policy = AUTO
sort_area_size = 65536 "Default"
------------------
when I run a query which use a huge sort memory, it could not pass 3,7G then it starts to sort on TEMP tablespace .. even that the server has about 300G free ram.
the OS is configured to allow 16G for each process.
what should I do, to let oracle allow sort in PGA Memory till 8G or even more.
regards
SQL> !ulimit -a address space limit (Kibytes) (-M) 863438880 core file size (blocks) (-c) 2 cpu time (seconds) (-t) unlimited data size (Kibytes) (-d) unlimited file size (blocks) (-f) unlimited locks (-x) unlimited locked address space (Kibytes) (-l) 734003200 message queue size (Kibytes) (-q) 800 nice (-e) 0 nofile (-n) 65536 nproc (-u) 15000 pipe buffer size (bytes) (-p) 4096 max memory size (Kibytes) (-m) 899578032 rtprio (-r) 0 socket buffer size (bytes) (-b) 4096 sigpend (-i) 8268091 stack size (Kibytes) (-s) 10240 swap size (Kibytes) (-w) not supported threads (-T) not supported process size (Kibytes) (-v) 863438880