DB12c - PGA_AGGREGATE_LIMIT

On Oracle Database 12c features, PGA_AGGREGATE_LIMIT initialization parameter is value, that limits PGA memory usage. If  total PGA memory usage is over PGA_AGGREGATE_LIMIT value. The sessions or processes that are consuming the most untunable PGA memory will be terminated.

SQL> show parameter PGA_AGGREGATE_LIMIT
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G (default)

As my test today, I ran SQL for parallel and got ora error message. 

ORA-03113: end-of-file on communication channel
Process ID: 6825
Session ID: 30 Serial number: 447

After getting message, I checked in alert log and knew my session used PGA over 2G.

--- In alert log file ---
Fri Jul 19 12:41:31 2013
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB
Immediate Kill Session#: 30, Serial#: 447
Immediate Kill Session: sess: 0xc13a6650  OS pid: 6825

However, It still showed some messages.

Fri Jul 19 12:42:02 2013
PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA
memory are not eligible to receive ORA-4036 interrupts.  Further occurrences
of this condition will be written to the trace file of the CKPT process.
Fri Jul 19 12:43:31 2013
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_2844.trc  (incident=108284):
ORA-00445: background process "J001" did not start after 120 seconds
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_108284/orcl_cjq0_2844_i108284.trc
Fri Jul 19 12:44:08 2013
Dumping diagnostic data in directory=[cdmp_20130719124407], requested by (instance=1, osid=2844 (CJQ0)), summary=[incident=108284].
Fri Jul 19 12:44:14 2013
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing
 ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 4194304 bytes. Both ASH size and the total number of emergency flushes
 since instance startup can be monitored by running the following query:
 select total_size,awr_flush_emergency_count from v$ash_info;
Fri Jul 19 12:44:16 2013
kkjcre1p: unable to spawn jobq slave process
Fri Jul 19 12:44:16 2013
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_2844.trc:
Fri Jul 19 12:44:21 2013
Sweep [inc][108284]: completed
Sweep [inc2][108284]: completed

What was it? job queue processes was not started, because PGA memory usage was still over the PGA_AGGREGATE_LIMIT.

Read on Oracle® Database Performance Tuning Guide.

• Calls for sessions that are consuming the most untunable PGA memory are aborted.
  
• If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then the sessions and processes that are consuming the most untunable PGA memory are terminated.
  

I checked more on Document, SYS processes and background processes other than job queue processes will not be subjected to any of the actions described in this section. Instead, if they are using the most untunable memory, they will periodically write a brief summary of their PGA usage to a trace file.

For example, When PGA memory usage was over the PGA_AGGREGATE_LIMIT, my session was aborted.... then PGA memory usage was still over the PGA_AGGREGATE_LIMIT. So, background process "J001" did not start. If checked in orcl_cjq0_2844.trc file.

*** 2013-07-19 12:41:38.079
Process diagnostic dump for J001, OS id=6978
-------------------------------------------------------------------------------
*** 2013-07-19 12:41:39.596
os thread scheduling delay history: (sampling every 1.000000 secs)
  0.000000 secs at [ 12:41:38 ]
    NOTE: scheduling delay has not been sampled for 0.928008 secs
  0.000000 secs from [ 12:41:34 - 12:41:39 ], 5 sec avg
  0.000421 secs from [ 12:40:40 - 12:41:39 ], 1 min avg
*** 2013-07-19 12:41:40.218
  0.000083 secs from [ 12:36:41 - 12:41:40 ], 5 min avg
*** 2013-07-19 12:41:56.748
loadavg : 71.63 31.79 12.51
Memory (Avail / Total) = 100.71M / 3960.24M
Swap (Avail / Total) = 1817.50M /  2960.00M
skgpgcmdout: read() for cmd /bin/ps -elf | /bin/egrep 'PID | 6978' | /bin/grep -v grep timed out after 13.980 seconds
*** 2013-07-19 12:42:12.393
Stack:
skgpgcmdout: read() for cmd /usr/bin/gdb --batch -quiet -x /tmp/stack6jjrbG /proc/6978/exe 6978 < /dev/null 2>&1 timed out after 11.430 seconds

I think this parameter, DBAs should learn about it. When they use Oracle 12c database and user sessions are terminated. DBAs should have the good answer for them. However, It might affect other sessions, even through session that consumed the most untunable PGA memory was terminated. If the total PGA memory usage is still over the limit.

Read More:
http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10328.htm
http://docs.oracle.com/cd/E16655_01/server.121/e15857/tune_pga.htm#TGDBA95346