Oracle Database - Process global area (PGA)

The PGA memory which is allocated for each session.

The PGA (Program or Process Global Area) is a memory area that stores data and control information for a single process. For example, it typically contains a:

• sort area,
• hash area,
• session cursor cache,
• etc…

Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA).

Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.

CREATE OR REPLACE PROCEDURE show_pga_memory ( context_in   IN   VARCHAR2 DEFAULT NULL )
IS
   l_memory   NUMBER;
BEGIN
    SELECT st.VALUE
       INTO l_memory
      FROM SYS.v_$session se, SYS.v_$sesstat st, SYS.v_$statname nm
    WHERE se.audsid = USERENV ('SESSIONID')
        AND st.statistic# = nm.statistic#
        AND se.SID = st.SID
        AND nm.NAME = 'session pga memory';
 
   DBMS_OUTPUT.put_line (   CASE
                                            WHEN context_in IS NULL
                                                  THEN NULL
                                            ELSE context_in || ' - '
                                        END
                                    || 'PGA memory used in session = '
                                    || TO_CHAR (l_memory)
                                   );
END show_pga_memory;

• Ora Faq - PGA