I previously mentioned that all Sessions are connected to the SGA memory, which is a type of Shared Memory for all Sessions connected to the Instance. However, all of these Sessions are also connected to another memory called the (PGA) Program Global Area, where data and control information for each Session are stored. These details are used solely by the Server Process that serves this Session. The PGA memory is created at the moment the Session is opened, and the information stored in the PGA includes:

• (Session Cursor & Space Stack & Variables)

The amount of PGA memory required by the Session depends on the type of work the Session performs. Starting from version 9i, it became possible to manage this portion of memory automatically.

There are two variables that control this portion of memory:

1. WORKAREA_SIZE_POLICY: This variable should be set to AUTO, which is the default value now. In version 9i, the default value was MANUAL.
2. PGA_AGGREGATE_TARGET: This is the total size of all PGAs used by all Sessions. The default value for this variable is 12% of the size of the SGA memory.

Oracle allocates PGA memory for each Session connected to the Instance. If the size of the PGAs reaches the value of the PGA_AGGREGATE_TARGET variable, Oracle will take memory from PGA Sessions that are no longer needed by those Sessions. This mechanism ensures that any Session that requires memory will have access to it.

However, if the Session needs more PGA and the total of PGAs reaches its maximum value (Target), and there are no unused PGA Sessions that can be allocated to this Session, then there are two possible scenarios:

The first possibility is that the requested memory remains unchanged. In this case, the memory will be allocated to the Session, and the Target will be broken. However, this solution should be avoided as much as possible, indicating that the Target is severely reduced.

The second possibility is that Oracle will refuse to allocate more memory, and instead, the Session will use the temporary space that it uses in the Temporary Tablespace. This will certainly affect performance.

The PGA activity can be monitored by querying V$PGASTAT. Additionally, you can use the V$PGA_TARGET_ADVICE view for further advice.