We had an outage, where pga eating up all the memory crashing the vcs. We temporarily solved the issue by moving to shared server and reducing the pga size.
Came across some of the suggestion to restrict pga,
1. Private_sga in MTS
2. 10261 event
10261, 00000, "Limit the size of the PGA heap"
// *Cause: the limit is one kilobyte times the level of the event. If the
// pga grows bigger than this signal an internal error.
to set a session to 100mb
alter system set events '10261 trace name context forever, level 100000'; |
useful note is found in
http://dioncho.wordpress.com/2010/06/14/rapid-pga-size-increase/
for setting multiple events in spfile, you can use below command
ALTER SYSTEM SET event='10261 trace name context forever, level 15000000','600 trace name heapdump, level 0x20000001' COMMENT='pga issue' SCOPE=SPFILE;
for resetting use,
2: |
ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ; |
Note: Have tried setting 10261 event in shared server db, but process didn't crash once reached the limit.
3. DBMS_SESSION.FREE_UNUSED_USER_MEMORY
convert this post to pdf.