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

Card Puncher Data Processing

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

See Oracle Database - PGA_AGGREGATE_TARGET Parameter

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - (Query) Work Area

The work area is an memory area that is used to perform data operation such as: SORT see sort area memory, HASH JOIN see hash area memory, GROUP BY, BUFFERING, BITMAP MERGE, or BITMAP CREATE...
Card Puncher Data Processing
Oracle Database - Direct (path insert|load) (/*+ APPEND */)

A direct-path insert is also known as: direct load A direct-path insert is a bulk operation which will only bypass redo log generation in three cases : the database is in NOARCHIVELOG mode database...
Card Puncher Data Processing
Oracle Database - HASH AREA memory

hash area is an area of the pga memory which is controled by the sort_area_size parameter if the WORKAREA_SIZE_POLICY is not set to AUTO.
Card Puncher Data Processing
Oracle Database - HASH_AREA_SIZE parameter

HASH AREA SIZE is a parameter which set the hash area size of the pga memory. It will have no effect if WORKAREA_SIZE_POLICY is set to auto.
Card Puncher Data Processing
Oracle Database - How to detect read from the temporary tablespace ?

How to detect read from the temporary tablespace ? Step followed : disable automatic PGA management by the server set the hash area size. and play with the sort area size In this example, the...
Card Puncher Data Processing
Oracle Database - Memory

Oracle have two Memory spaces: SGA memory which is shared by all sessions connected to Oracle Database. See and PGA memory which is allocated for each session. See With SQLPlus
Card Puncher Data Processing
Oracle Database - PGA_AGGREGATE_TARGET Parameter

PGA_AGGREGATE_TARGET is a database initialization parameter and controls the total amount of execution memory that can be allocated by Oracle for the Process global area (PGA) The Total Memory here refers...
Card Puncher Data Processing
Oracle Database - SORT_AREA_SIZE parameter

SORT AREA SIZE is a parameter which set the sort area size of the pga memory. It will have no effect if the WORKAREA_SIZE_POLICY is set to auto. The sort area size control how much memory will be dynamically...
Card Puncher Data Processing
Oracle Database - Sort Area memory

sort area is a area of the pga memory which is controled by the sort_area_size parameter if the WORKAREA_SIZE_POLICY is not set to AUTO. V$ table
Card Puncher Data Processing
Oracle Database - WORK_AREA_SIZE_POLICY

WORK_AREA_SIZE_POLICY is a parameter to manage the pga. It's a new features of Oracle 9i. If WORK_AREA_SIZE_POLICY = AUTO, the hash_area_size and sort_area_size setting are ignored, Oracle will set them...



Share this page:
Follow us:
Task Runner