PGA aggregate target and PGA aggregate limit parameter in oracle

PGA Aggregate Target and PGA aggregate limit

PGA_AGGREGATE_TARGET is the parameter for control PGA memory allocation.
The actual PGA usage can be as high as three times of the value of PGA_AGGREGATE_TARGET.
It will not terminate the session if usage exceeded.

Excessive PGA usage can lead to memory swapping issue and have impact on the performance.

PGA_AGGREGATE_LIMIT is a hard limit on the PGA memory usage.If this limit is reached or exceeded, Oracle will terminates the sessions.

pga_aggregate_limit by default, the pga_aggregate_limit parameter is which is greatest from following:
1: 2 GB
2: Double the value of pga_aggregate_limit value
3: 3 megabytes times the value of the processes parameter. ( 3 * 300 process = 900 MB)

ORA-04030: out of process memory

Error:

Thu Mar 23 02:18:57 2017
Unable to allocate memory for new incident error in file E:\ORACLE\diag\rdbms\ic\ic\trace\ic_m000_1444.trc:
ORA-04030: out of process memory when trying to allocate 20520 bytes (pga heap,KTI PGA static small pool)
Thu Mar 23 02:18:57 2017
Errors in file E:\ORACLE\diag\rdbms\ic\ic\trace\ic_m000_1444.trc:
ORA-04030: out of process memory when trying to allocate 20520 bytes (pga heap,KTI PGA static small pool)

Check the PGA stats

select * from v$pga_target_advice;

select * from v$pgastat;

show parameter pga

Check the resource utilization in Oracle

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ('sessions', 'processes');

 
calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions
note: max utilization is take from above query no of session

SELECT MAx_utilization_session*(2048576+P1.VALUE+P2.VALUE)/(1024*1024) YOU_NEED_PGA_MB
FROM V$PARAMETER P1, V$PARAMETER P2
WHERE P1.NAME = 'sort_area_size'
AND P2.NAME = 'hash_area_size';

Increase the PGA memory parameter without reboot the database

ALTER SYSTEM SET pga_aggregate_target = 3500M SCOPE=BOTH;

Note: its good to take 3 value as set in pga_Aggregate_target
alter system set pga_aggregate_limit=pga_aggregate_target*3 Scope=both;

 
Check more detail about PGA


-- check for all session
select name, value
from v$statname n, v$sesstat t
where n.statistic# = t.statistic#
and t.sid = ( select sid from v$mystat where rownum = 1 )
and n.name in ( 'session pga memory', 'session pga memory max','session uga memory', 'session uga memory max')

-- check the pga usage by all session
select s.osuser osuser,s.serial# serial,se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
,v$session s
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
and s.sid=se.sid
group by n.name,se.sid,s.osuser,s.serial#
order by 2
;

--find PGA usage for a specific session
SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM
v$sesstat a, v$statname b
WHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic#
AND SID = 80;

Total PGA memory used by process


-- Pga used memory for all process
SELECT ROUND(SUM(pga_used_mem)/(1024*1024),2) PGA_USED_MB FROM v$process;

-- PGA used max memory for all process
select ROUND(SUM(pga_max_mem)/(1024*1024),2) PGA_USED_MB FROM v$process;

-- For memory allocation total by process
SELECT ROUND(SUM(pga_used_mem)/(1024*1024),2) max,
ROUND(SUM(pga_alloc_mem)/(1024*1024),2) alloc,
ROUND(SUM(pga_used_mem)/(1024*1024),2) used,
ROUND(SUM(pga_freeable_mem)/(1024*1024),2) free
FROM V$PROCESS;

-- Check PGA Allocation for each process
SELECT spid, program,
pga_max_mem max,
pga_alloc_mem alloc,
pga_used_mem used,
pga_freeable_mem free
FROM V$PROCESS;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.