PGA

From Oracle FAQ
Jump to: navigation, search

The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.

Auto tuning[edit]

PGA areas can be sized manually by setting parameters like hash_area_size, sort_area_size etc.

To allow Oracle to auto tune the PGA areas, set the WORKAREA_SIZE_POLICY parameter to AUTO and the PGA_AGGREGATE_TARGET to the size of memory that can be used for PGA. This feature was introduced in Oracle 9i.

Oracle 11g allows users to tune both PGA and SGA areas with a single parameter, called MEMORY_TARGET.

Monitor[edit]

PGA usage statistics:

select * from v$pgastat;

Determine a good setting for pga_aggregate_target:

select * from v$pga_target_advice order by pga_target_for_estimate;

Show the maximum PGA usage per process:

select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

Also see[edit]

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #