This is a interesting feature for Oracle DBA’s. Till now there has been no way of limiting total PGA being used by oracle instance. _pga_max_size (hidden parameter) could be used to limit per process memory. SGA_TARGET used to be hard limit for Oracle SGA but PGA_AGGREGATE_TARGET only controlled allocation of PGA memory to each process via oracle algorithm. (I know some folks who thought pga_aggregate_target is hard limit, but that is incorrect)

Due to this many times we end up in a situation when we get paged for High CPU /Low memory utilization and found that high swapping was being caused by excessive PGA usage. Since kswapd (linux) is busy writing to swap, you will also see high CPU utilization.

At this moment, your memory might look like below

$free -m
 total used free shared buffers cached
Mem: 16057 15953 103 0 4 209
-/+ buffers/cache: 15739 317
Swap: 12287 5359 6928

PGA_AGGREGATE_LIMIT is supposed to put a hard limit on the PGA Memory usage. But unlike SGA_TARGET, this doesn’t mean that processes will reduce the memory consumption. Instead this will lead to process/session failure.

As per Oracle docs oracle will terminate sessions in following order

-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.

Note: I don’t see any difference in above conditions 🙂

PGA_AGGREGATE_LIMIT will be set to higher of following values

– 2 GB (default value)
– 200% of the PGA_AGGREGATE_TARGET value
– 3 MB times the value of the PROCESSES parameter.
– Less then 1.2 X RAM – SGA
To simulate this, I did following test using big_table (Code from Asktom)

create table big_table tablespace data_4m
as
select rownum id,
 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 OBJECT_ID, DATA_OBJECT_ID,
 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY,
 GENERATED, SECONDARY
 from all_objects a
 where 1=0
/

alter table big_table nologging;
--Load the table
declare
 l_cnt number;
 l_rows number := &1;
begin
 insert /*+ append */
 into big_table
 select rownum,
 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 OBJECT_ID, DATA_OBJECT_ID,
 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY,
 GENERATED, SECONDARY
 from all_objects a
 where rownum <= &1;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
 loop
 insert /*+ APPEND */ into big_table
 select rownum+l_cnt,
 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 OBJECT_ID, DATA_OBJECT_ID,
 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY,
 GENERATED, SECONDARY
 from big_table
 where rownum <= l_rows-l_cnt;
 l_cnt := l_cnt + sql%rowcount;
 commit;
 end loop;
end;
/ 
--gather stats
exec dbms_stats.gather_table_stats ( ownname => 'AMIT',tabname => 'BIG_TABLE' )

Let’s check the size of table

select owner,table_name,num_rows from dba_tables where table_name='BIG_TABLE';

OWNER		     TABLE_NAME 	    NUM_ROWS
-------------------- -------------------- ----------
AMIT          	     BIG_TABLE		   110000000

PGA_AGGREGATE_LIMIT is set to 2G (default value). This can’t be lowered as 2G is least value allowed for it

SQL> alter system set pga_aggregate_limit=1g;
alter system set pga_aggregate_limit=1g
*
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

Before proceeding with this test PGA_AGGREGATE_TARGET was bumped to 30g so that processes can get more PGA memory

Created a shell file with following code

cat 1.sh
modname=$1
sqlplus -s amit/amit <<EOF
set pages 1000 lines 100
set autot traceonly
exec dbms_application_info.set_module(module_name=>'${modname}',action_name=>'')
 select * from big_table t1,big_table t2 where t1.object_id =t2.object_id;
exit
EOF

This code is executed via for loop with 20 sessions

for ((i=1;i<=20;i++))
do
nohup sh 1.sh PGA_LIMIT${i} &
done

In separate session ran following queries

set pages 1000 lines 200
col event for a30
col module for a30
select s.sid,s.serial#,p.spid,p.pga_used_mem/1024/1024 PGA_MB,s.status,s.event,s.module from v$session s ,v$process p where s.paddr=p.addr and s.module like 'PGA_LIMIT%' and wait_class<>'Idle' order by module
/
select sum(pga_used_mem)/1024/1024 pga_used_mb from v$process;

Output of the query was like this

SQL> @pga

       SID    SERIAL# SPID			   PGA_MB STATUS   EVENT			  MODULE
---------- ---------- ------------------------ ---------- -------- ------------------------------ ------------------------------
       262	  243 20941		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT1
       390	   77 20957		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT10
       154	   11 20938		       130.816335 ACTIVE   direct path write temp	  PGA_LIMIT11
	35	   77 20921		       130.944379 ACTIVE   direct path write temp	  PGA_LIMIT12
       266	   35 20906		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT13
       141	   17 20917		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT14
       263	   59 20945		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT15
	25	   55 20943		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT16
       135	  119 20942		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT17
       383	  217 20923		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT18
       371	  391 20937		       130.816335 ACTIVE   direct path write temp	  PGA_LIMIT19
       382	  163 20955		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT2
	29	   35 20954		       130.816335 ACTIVE   direct path write temp	  PGA_LIMIT20
       144	  103 20947		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT3
       136	   15 20952		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT4
	38	    5 20926		       130.882039 ACTIVE   direct path write temp	  PGA_LIMIT5
       257	  139 20902		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT6
       260	   73 20939		       130.816335 ACTIVE   direct path write temp	  PGA_LIMIT7
       381	   13 20951		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT8
	23	  101 20915		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT9

20 rows selected.

Elapsed: 00:00:00.00

PGA_USED_MB
-----------
 2684.47882

Note that this is more then 2G limit and we still didn’t see failures. Reason is that we have set pga_aggregate_target to 30G . Checking parameter value for pga_aggregate_limit still shows 2G but it seems it has risen to higher value (since pga_aggregate_target is 30G now). At this moment, I reduced the pga_aggregate_target to 500m. Immediately some of the db sessions were automatically cleared. Excerpt from alert log

ALTER SYSTEM SET pga_aggregate_target=500M SCOPE=BOTH;
2013-07-07 11:27:34.015000 +00:00
Errors in file /home/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_20923.trc  (incident=12476):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Errors in file /home/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_20939.trc  (incident=12433):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Checking V$Session confirms same

      SID    SERIAL# SPID			   PGA_MB STATUS   EVENT			  MODULE
---------- ---------- ------------------------ ---------- -------- ------------------------------ ------------------------------
       262	  243 20941		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT1
       390	   77 20957		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT10
       266	   35 20906		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT13
       141	   17 20917		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT14
	25	   55 20943		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT16
       135	  119 20942		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT17
       382	  163 20955		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT2
       144	  103 20947		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT3
       136	   15 20952		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT4
       257	  139 20902		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT6
       381	   13 20951		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT8
	23	  101 20915		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT9

12 rows selected.

PGA_USED_MB
-----------
 1637.30552

Since limit is set to 2G, it has cleared sessions automatically. login_time is not considered for killing session as we can find PGA_LIMIT7, 11,12 missing from the remaining sessions.