PGA in Oracle

Overview of the Program Global Area:-

The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system.
PGA is a memory heap that contains session-dependent variables required by a dedicated or shared server process.

Private SQL Area

This area contains about a parsed SQL statement and session informations.

When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values,
 -query execution state information, and query execution work areas.
A cursor is a name or handle to a specific private SQL area.
The client process is responsible for managing private SQL areas. Allocation and Deallocation can set open_sursor=200 parameters

A private SQL area is divided into the following areas:

The run-time area:-

-This area contains query execution state information.
-Oracle Database creates the run-time area as the first step of an execute request. the run-time area is freed when the SQL statement is closed.

The persistent area

This area contains bind variable values.
A bind variable value is supplied to a SQL statement at run time when the statement is executed.
The persistent area is freed only when the cursor is closed.
The client process is responsible for managing private SQL areas.
 IMPORTANT:-Do not confuse a private SQL area, which is in the UGA, with the shared SQL area, which stores execution plans in the SGA.

SQL Work Areas

A work area is a private allocation of PGA memory used for memory-intensive operations.
For example, a sort operator uses the sort area to sort a set of rows.hash table join,bitmap merge uses bitmap merge area to merge data and bit map indexes alos.


If the data not fit into pga,the Database input divides..




Practical Example's.
alter system set workarea_size_policy=manual;
After that we ill set size in individual PGA.

sort_area_size                       integer     65536
bitmap_merge_area_size               integer     1048576
create_bitmap_area_size              integer     8388608
hash_area_size                       integer     131072
workarea_size_policy                 string      AUTO

It consume 20% from sga_target,if you want to increase whole pga use below parameters
pga_aggregate_target                 big integer 180M