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

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.

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;

Configuration

Documentation / Reference


Powered by ComboStrap