Oracle Database - Architecture (PGA)

DEFINITION:
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 sharedserver 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 canset open_cursor=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 will 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

SQL> ALTER SYSTEM SET memory_max_target=20480M SCOPE=SPFILE;
SQL> ALTER SYSTEM SET memory_target=19000M SCOPE=SPFILE;
SQL> ALTER SYSTEM SET sga_target=0 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET pga_aggregate_target=0 SCOPE=SPFILE;
RESTART Oracle Database to get the changes reflected