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 to 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:
Post a Comment