Database Administration (MOSC)

MOSC Banner

increase sql_sort_area to 8 GB does not work

edited Jul 4, 2016 4:37AM in Database Administration (MOSC) 7 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center