Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

PGA / UGA Memory Overview

by Jeff Hunter, Sr. Database Administrator

Contents

1. PGA Memory
2. UGA Memory
3. PGA / UGA Memory Query

PGA Memory

The Program Global Area (PGA) is a memory region that contains data and control information for a single process (server or background). The PGA is made up of the following:

• Stack Space

  A PGA always contains a stack space, which is memory allocated to hold a session's variables, arrays, and other information.

• Session Information - (UGA)

  A PGA in an instance running without the multi-threaded server (named Shared Server in Oracle9i) requires additional memory for the user's session, such as private SQL areas and other information. If the instance is running the multi-threaded server, this extra memory is not in the PGA, but is instead allocated in the SGA (the Shared Pool).

• Shared SQL Areas

  Shared SQL areas are always in shared memory areas of the SGA (not the PGA), with or without the multi-threaded server.

• Non-shared and Writable

  The PGA is a non-shared memory area to which a process can write. One PGA is allocated for each server process; the PGA is exclusive to a server process and is read and written only by Oracle code acting on behalf of that process.

UGA Memory

The UGA, or User Global Area, is allocated in the PGA for each session connected to Oracle in a dedicated server environment. The PGA is memory allocated at the client to hold a stack which contains all of the session's variables, etc. In a Shared Server environment, Oracle allocates this memory in the Shapred Pool (the shared pool is contained in the SGA), for all sessions. This helps to reduce the PGA (client) memory footprint of Oracle, but will increase the SGA (shared pool) requirements.

PGA / UGA Memory Query

Thanks to Robert Pang, Robert.Pang@oracle.com, from Oracle Corporation for providing the following query. This query can be used to display both the PGA and UGA memory requirements for all user sessions, including background processes:

PGA / UGA Memory Query

SET LINESIZE 145 SET PAGESIZE 9999 COLUMN sid FORMAT 999 HEADING 'SID' COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory' COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max' COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory' COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX' SELECT s.sid sid , lpad(s.username,12) oracle_username , lpad(s.osuser,9) os_username , s.program session_program , lpad(s.machine,8) session_machine , (select ss.value from v$sesstat ss, v$statname sn where ss.sid = s.sid and sn.statistic# = ss.statistic# and sn.name = 'session pga memory') session_pga_memory , (select ss.value from v$sesstat ss, v$statname sn where ss.sid = s.sid and sn.statistic# = ss.statistic# and sn.name = 'session pga memory max') session_pga_memory_max , (select ss.value from v$sesstat ss, v$statname sn where ss.sid = s.sid and sn.statistic# = ss.statistic# and sn.name = 'session uga memory') session_uga_memory , (select ss.value from v$sesstat ss, v$statname sn where ss.sid = s.sid and sn.statistic# = ss.statistic# and sn.name = 'session uga memory max') session_uga_memory_max FROM v$session s ORDER BY session_pga_memory DESC /

SID Oracle User O/S User Session Program Machine PGA Memory PGA Memory Max UGA Memory UGA Memory MAX ---- ------------ --------- ------------------ -------- -------------- -------------- -------------- -------------- 3 oracle oracle@alex (LGWR) alex 5,526,516 5,526,516 77,956 77,956 9 oracle oracle@alex (ARC0) alex 4,500,080 4,500,080 77,956 77,956 10 oracle oracle@alex (ARC1) alex 4,500,080 4,500,080 77,956 77,956 11 SYSTEM oracle sqlplus@alex (TNS alex 3,403,988 4,780,244 77,956 1,400,476 2 oracle oracle@alex (DBW0) alex 2,488,624 2,488,624 77,956 77,956 4 oracle oracle@alex (CKPT) alex 1,359,484 1,359,484 77,956 77,956 17 SCOTT oracle sqlplus@alex (TNS alex 577,512 708,584 339,812 339,812 5 oracle oracle@alex (SMON) alex 499,704 499,704 77,956 77,956 8 oracle oracle@alex (QMN0) alex 475,596 541,132 274,348 274,348 19 SCOTT oracle sqlplus@alex (TNS alex 454,964 454,964 143,420 143,420 6 oracle oracle@alex (RECO) alex 237,024 237,024 77,956 77,956 1 oracle oracle@alex (PMON) alex 228,512 228,512 77,956 77,956 7 oracle oracle@alex (CJQ0) alex 228,512 228,512 77,956 77,956 13 rows selected.