Oracle Database - Process global area (PGA) - Work Area

> Database > Oracle Database

1 - About

The PGA memory is a memory allocated for each session.

The PGA (Program or Process Global Area) is a memory area that stores data and control information for a single server process.

It typically contains a:

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.

Advertising

3 - How to know how much PGA memory I am using ?

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;

4 - Configuration

5 - Documentation / Reference