Monday, August 17, 2015

Using PGA_AGGREGATE_LIMIT to Limit PGA Memory

Before 12c, there was no hard limit to restrict PGA memory usage by an Oracle session. It means that if a session keeps growing in memory, it would eventually allocate all available memory which would lead to a system slowness/hang and eventually system crash, I have discussed this scenario here.

Starting 12c, we can use parameter PGA_AGGREGATE_LIMIT so set a limit to the PGA growth for each session. Value of this parameter would apply to each session individually – which means that if value of this parameter is set to 2G, each session cannot take more than 2G memory for its PGA. Default value of this parameter is as follows (copied from here)

By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.
     

Following example shows how it works.
SQL> show parameter pga_aggregate_limit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G

SQL> create user c##testuser identified by testuser;

User created.

SQL> grant connect, create procedure to c##testuser;

Grant succeeded.

-- Connect with this new user and create a procedure to test PGA memory growth

SQL> conn c##testuser/testuser
Connected.
SQL> create or replace procedure test_proc is
  2  begin
  3  test_proc;
  4  end;
  5  /

Procedure created.


Now if we call this procedure, it would call itself recursively infinitely and PGA would start growing(and keep growing) in memory.

SQL> exec test_proc


While execution of above procedure is in progress, let’s monitor the PGA memory
SQL> set time on
14:51:47 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   2.48916531

14:51:52 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   900.42667

14:52:05 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1221.55167

14:52:06 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1694.23917

14:52:07 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1705.92667

14:52:08 SQL>
14:52:10 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1733.73917

14:52:11 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1756.67667

14:52:13 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1874.11417

14:52:17 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1886.05167

14:52:20 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1886.05167

14:52:23 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1886.05167

Now it is not growing any further and eventually session would be killed automatically as you can see bellow error returned to the session executing this procedure.
BEGIN test_proc; END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7792
Session ID: 15 Serial number: 45903


SQL>

Alert log file would report and error similar to the following.
Tue Aug 11 14:52:19 2015
Errors in file C:\APP\SALMQURE\diag\rdbms\salman12\salman12\trace\salman12_ora_7792.trc  (incident=133769) (PDBNAME=CDB$ROOT):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Incident details in: C:\APP\SALMQURE\diag\rdbms\salman12\salman12\incident\incdir_133769\salman12_ora_7792_i133769.trc
Errors in file C:\APP\SALMQURE\diag\rdbms\salman12\salman12\trace\salman12_ora_7792.trc  (incident=133770) (PDBNAME=CDB$ROOT):
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\SALMQURE\diag\rdbms\salman12\salman12\incident\incdir_133770\salman12_ora_7792_i133770.trc
Tue Aug 11 14:52:51 2015
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB
Immediate Kill Session#: 15, Serial#: 45903
Immediate Kill Session: sess: 000007FF7D7CC408  OS pid: 7792




No comments: