Oracle Program Global Area

Instance PGA

Each process has its own PGA, the entirety of all PGAs in an instance is referred to as instance PGA.

Initialization parameters related to the PGA set the size of the instance PGA, not the size of the processes' PGA.

Because the memory of a process' PGA is not shared between processes, there is no need to protect its data structures with latches.

Components of a PGA

A PGA consists of

Stack space (session variables, arrays …)
Hash area (for hash joins)
Bitmap merge area (to merge data from scans of multiple bitmap indexes, compare with the BITMAP MERGE plan operation)
User Global Area (UGA) (in dedicated server environments)
SQL work areas, used by group by and order by
Session memory (Logon information)
Private SQL area (Values of bind variables, session specific information for processing etc.)
Temporary LOBs

In shared server environments (as opposed to non-dedicated server environments), the UGA moves into the Large Pool because multiple clients share the same server process.

Queries

PGA statistics

select
   name,
   case when unit = 'bytes' then round(value / power(1024, 2),2) else value end value,
   case unit 
        when 'bytes'   then 'MB'
        when 'percent' then '%'
        else  unit
   end unit,
   con_id
from
   v$pgastat
order by
   name;

Names that are found with this query are

MGA allocated (under PGA)
PGA memory freed back to OS
aggregate PGA auto target
aggregate PGA target parameter
bytes processed
cache hit percentage
extra bytes read/written
global memory bound
max processes count
maximum MGA allocated
maximum PGA allocated
maximum PGA used for auto workareas
maximum PGA used for manual workareas
over allocation count
process count
recompute count (total)
total PGA allocated

Size of PGA and UGA

v$sesstat and v$process allow to query (among many other values) the memory usage of the PGA:

select
    s.sid,
    s.username,
    round(max(case when n.name = 'session uga memory'        then value /1024/1024 end), 1) uga_mb,
    round(max(case when n.name = 'session pga memory'        then value /1024/1024 end), 1) pga_mb,
    round(                       p.pga_used_mem                         /1024/1024     , 1) pga_used_mb,
    round(                       p.pga_alloc_mem                        /1024/1024     , 1) pga_alloc_mb,
    round(                       p.pga_freeable_mem                     /1024/1024     , 1) pga_freeable_mb,
    round(max(case when n.name = 'session uga memory max'    then value /1024/1024 end), 1) uga_mb_max,
    round(max(case when n.name = 'session pga memory max'    then value /1024/1024 end), 1) pga_mb_max,
    round(max(case when n.name = 'workarea memory allocated' then value /1024/1024 end), 1) workarea_mb
from
    v$session  s                                 join
    v$sesstat  t on s.sid        = t.sid         join
    v$statname n on t.statistic# = n.statistic#  join
    v$process  p on s.paddr      = p.addr
where
     s.type = 'USER'
     and s.username is not NULL
-- and n.name in ('session pga memory', 'session pga memory max',    'session uga memory', 'session uga memory max')
group by
   s.sid,
   s.username,
   p.pga_used_mem,
   p.pga_alloc_mem,
   p.pga_freeable_mem
;

Memory chunks

The memory chunks of the PGA are selectable from x$ksmpp (which has the same structure as x$ksmsp).