PGA usage by sessions

Summary 

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;

To 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 = 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;