Program Global Area OR Process Global Area (PGA):-
The Program Global Area OR Process Global Area (PGA) is a memory reason that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In Contrast to the SGA, Which is shared by several processes, the PGA is an area that is used by only one process.
PGA has Below Functionality :
- Memory reserved
for each user process connection to an Oracle Database
- Memory Allocated
when a process is created
- Memory
deallocated when the process is terminated
- Each Server
Process having on its own PGA and used by only one process.
Contents of PGA :
The contents of the PGA memory varies, Depending on whether the Instance is running in a dedicated server Or Shared Server configuration. Generally, the PGA memory includes these below components :
- Private SQL Area
: It
contains data such as bind information and run-time memory structures. Each session that issues a SQL statement has a private SQL
area. Each user that submits the same SQL statement has his or her own
Private SQL Area that uses a single shared SQL area. Thus many private SQL
areas can be associated with the same Shared SQL Area.
The Private SQL area of a Cursor is divided into two areas :
- Persistent Area
: Persistent
Area contains bind Information, and it is freed only when the cursor is
closed.
- Run-time Area :
Run-time Area Created as the first step of an execute request. For
INSERT, UPDATE, and DELETE commands, This area is freed after the statement has been executed. For queries, This area is freed only after all rows are fetched OR the query is canceled.
The location of the Private SQL area depends on the type of connections
established for the session. In a dedicated server environment, the
Private SQL Area is located in the PGA of their Server Process. In a shared
server environment, the Private SQL Areas are located in SGA.
The management of the private SQL area is the responsibility of the user process. The number of Private SQL Areas that a user process can allocate is always limited by the OPEN_CURSORS initialization parameter. The default value of this parameter is 50.
- Session Memory: Session Memory consists of memory allocated to hold a session's variables and other information related to the session. For a shared server environment, the session memory is shared and not private.
- SQL Work Area: SQL Work Areas
Used for memory-intensive operations such as Sort, Hash-Join, Bitmap Merge,
Bitmap Create. The Size of a work area can be controlled and tuned. Beginning from Oracle 9i, the size of the work area can be automatically and globally managed. This is enabled by Setting the
WORKAREA_SIZE_POLICY Parameter to Auto, Which is the default value,
and the PGA_AGGREGATE_TARGET initialization Parameter. The
PGA_AGGREGATE_TARGET parameter is set to specify the target aggregate amount of PGA memory available to the instance. This
Parameter is only a target and can be dynamically modified at the instance level. It will accept a number of bytes, kilobytes,
megabytes, or gigabytes. When these parameters are set sizing of work areas becomes automatic and all *_AREA_SIZE.
Prior to oracle 9i, The DBA controlled the maximum size of SQL work area by setting the following parameters:
SORT_AREA_SIZE ,
HASH_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE,
and
CREATE_BITMAP_AREA_SIZE
Setting these parameters can be difficult because the maximum work area size is ideally selected on the basis of the data input size and the total number of the work area active in the system. These two factors vary considerably from one work area to another and from one time to another.
Differences in Memory Allocation Between Dedicated Server and Shared Server :
The content of the PGA memory varies, Depending on whether the instance is running in a dedicated server or shared server configuration. Generally, the PGA memory includes Fallowing components.
| Memory Area | Dedicated Server | Shared Server |
| Nature of Session Memory | Private | Shared |
| Location Of Persistent Area | PGA | SGA |
| Location Of Run-time Area (SELECT) | PGA | SGA |
| Location Of Run-time Area (DML/DDL) | PGA | PGA |
