Recursion in Oracle 12

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>