Each user session contains its own PGA taken from the operating system free memory pool. As sessions have the need to use PGA memory, the Oracle database will allocate the session memory from the PGA until the total allocation of memory is equal to the pga_aggregate_target value set within the database. As sessions require additional memory from the PGA, Oracle will pull the memory from other sessions that no longer need the memory.
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ')
|| TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'),'HH24:MI:SS') LOGON,
SID,
v$session.SERIAL#,
v$process.SPID,
ROUND(v$process.pga_used_mem / (1024 * 1024), 2) PGA_MB_USED,
v$session.USERNAME,
STATUS,
OSUSER,
MACHINE,
v$session.PROGRAM,
MODULE
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
--AND status = 'ACTIVE'
--AND module = 'ECR'
--AND v$session.sid = 452
--AND v$session.username = 'MR54'
--AND v$process.spid = 5698
ORDER BY pga_used_mem DESC;
To find the total PGA memory used by processes
SELECT ROUND(SUM(pga_used_mem) / (1024 * 1024), 2) PGA_USED_MB
FROM v$process;
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 = 596;
To calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions and run:
SELECT &MAX_CONNECTED_SESSIONS * (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';
V$PGASTAT provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.
To query the contents of the current PGA settings within Oracle 11g, issue a request against the v$pgastat dynamic performance view as shown:
SELECT * FROM V$PGASTAT;
NAME VALUE UNIT
----------------------------------------------------------------
aggregate PGA target parameter 163577856 bytes
aggregate PGA auto target 80363520 bytes
global memory bound 32714752 bytes
total PGA inusen 74283008 bytes
total PGA allocated 156244992 bytes
maximum PGA allocated 240105472 bytes
total freeable PGA memory 9240576 bytes
process count 30
max processes count 41
PGA memory freed back to OS 273874944 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 7929856 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 270336 bytes
over allocation count 0
bytes processed 581040128 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 717
19 rows selected.
To change PGA memory parameter
ALTER SYSTEM SET pga_aggregate_target = 3500M SCOPE=BOTH;
