DBA Tips Archive for Oracle

  


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:



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.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 15-Apr-2004 00:00:00 EDT
Page Count: 58448