A couple days ago, I was asked for a tuning exercise for a client. The instance and SQL statement were tuned very well, however a SQL statement was failing at runtime with ORA-4030.
PGA setting and maximum pga setting on instance (_pga_max_size) were tuned well, after investigation, I found that it is ulimit setting for Oracle OS user which prevents database session to get enough memory (PGA) to execute the query.
In my situation, ulimit was set to the followings :
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000
---- Change data to unlimited
ulimit -d unlimited
ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000
After restarting the listener to create a new session with the new ulimit settings, the issue was fixed.
No comments:
Post a Comment