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


Next Topic »
(Oracle Database - Startup & Shutdown Options)
|