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