Oracle 12 has a new parameter called PGA_AGGREGATE_LIMIT:
SQL> conn system/manager
Connected.
SQL> l
1 select value from v$parameter
2* where name = 'pga_aggregate_limit'
SQL> /
VALUE
--------------------
2147483648
SQL>
You cannot set it below 2 gigabytes:
SQL> alter system
2 set pga_aggregate_limit = 1g;
alter system
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because
specified value is invalid
ORA-00093: pga_aggregate_limit must be between 2048M
and 100000G
SQL>
In
earlier versions of Oracle, if you let recursive code get out of
control, it could use up all the memory on the underlying server. I
decided to try this out on Oracle 12:
SQL> create or replace procedure recursion is
2 begin
3 recursion();
4 end;
5 /
Procedure created.
SQL> exec recursion();
While
this was running, I found I could still use the machine so I took a
look in the alert log and saw the following errors at the end:
Thu Feb 12 17:43:34 2015
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_2796.trc (incident=19267):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Incident details in: C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\incident\incdir_19267\orcl1_ora_2796_i19267.trc
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_2796.trc (incident=19268):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Incident details in: C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\incident\incdir_19268\orcl1_ora_2796_i19268.trc
When I looked in V$SESSION_EVENT, I saw that there had been a brief wait on the acknowledge over PGA limit event:
SQL> l
1 select event, time_waited/100
2 from v$session_event
3* where sid = 11
SQL> /
EVENT TIME_WAITED/100
------------------------------ ---------------
acknowledge over PGA limit 7.05
Disk file operations I/O 0
log buffer space 0
SQL*Net message to client 0
SQL*Net message from client 116.29
SQL*Net break/reset to client 0
6 rows selected.
SQL>
After this, the session was killed:
SQL> select status from v$session where sid = 11;
STATUS
--------
KILLED
SQL>
… and there was a message to this effect in the alert log:
Thu Feb 12 17:52:59 2015
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\incident\incdir_19267\orcl1_ora_2796_i19267.trc:
ORA-00028: your session has been killed
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
The only problem I could see was that no error message was returned to the session running the recursion.
As an extra test, I used SYS to run the recursive procedure:
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 13 09:02:36 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> exec system.recursion();
This wrote more serious messages to the alert log:
Fri Feb 13 09:04:39 2015
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.
The session remained ACTIVE and V$SESSION_EVENT did not know what it was waiting for:
SQL> select sid from v$session
2 where username = 'SYS';
SID
----------
237
SQL> select status from v$session
2 where sid = 237;
STATUS
--------
ACTIVE
SQL> select event, time_waited/100
2 from v$session_event
3 where sid = 237
4 /
EVENT TIME_WAITED/100
---------------------------------------- ---------------
Disk file operations I/O 0
db file sequential read .09
SQL*Net message to client 0
SQL*Net message from client 101.92
SQL>
