PGA monitoring views in oracle

The PGA(program or process global area)is a memory area(ram) that stores data and control information for a single process.
It typically contais a sort_area,hash_area,session_cursor cache.
pga areas can be sized manually by setting parameters like hash_area_size,sort_area_size

Hash_Area_Size
Oracle hash_area_size is simple. The hash_area_size parameter value defaults to 1.5 times sort_area_size
and is used for performing hash joins of Oracle tables. The higher the value for hash_area_size, the higher
the propensity for the CBO to use a hash join.
The value for hash_area_size is quickly the oracle show parameters command
SQL > show parameter hash_area_size;

The hash_area_size is obsolete if you are using pga_aggregate_target,but in oracle 9i with
pga_aggregate_target,a hash area size cannot exceed 5% of the pga area many increase the
hash_area_size with "alter session get hash_area_size" or with a use_hash hint.

Sort_Area_Size
The sort_area_size parameters control the RAM size for dedicated tasks to sort SQL result sets and reduce
expensive disk sorts.
The usage for sort_area_size changed when pga_aggregate_target was introduced.
The sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto, 

unless you are using a old feature such as the MTS (shared servers).
If dedicated server connections are used, the sort_area_size parameter is ignored unless you set
workarea_size_policy=manual.  You can still use sort_area_size at the session level, but there are special
tricks for sorting very large result sets in RAM because of the default governor on sort_area_size which

imposes a limit on the amount of sort area any session may acquire:
SQL > alter session set workarea_size_policy=manual;
SQL > alter session set sort_area_size=nnnn;

Bitmap_Merge_Area_Size
BITMAP_MERGE_AREA_SIZE is relevant only for systems containing bitmap indexes.
It specifies the amount of memory Oracle uses to merge bitmaps retrieved from a range scan of the index.
The default value is 1 MB.
A larger value usually improves performance, because the bitmap segments must be sorted before being
merged into a single bitmap.
SQL > show parameter bitmap_merge_area_size

Create_Bitmap_Area_Size
CREATE_BITMAP_AREA_SIZE is relevant only for systems containing bitmap indexes.
It specifies the amount of memory (in bytes) allocated for bitmap creation.
The default value is 8 MB.
A larger value may speed up index creation.
the number of unique values in a column in relation to the number of rows in the table.
If cardinality is very small, you can set a small value.
SQL > show parameter create_bitmap_area_size


Pga_Aggregate_Target

• PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.
• The default value for PGA_AGGREGATE_TARGET is non zero. Oracle sets it’s value to 20% of the 
• SGA or 10 MB, whichever is greater.
• Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the 
• WORKAREA_SIZE_POLICY parameter to AUTO.
• This means that SQL working areas used by memory-intensive SQL operators such as sort, group-by, 
• hash-join, bitmap merge, and bitmap create will be automatically sized.
• In that case we don’t have to bother about settings of sort_area_size , hash_area_size etc.
• If you set PGA_AGGREGATE_TARGET to 0 then oracle automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. 
• This means that SQL workareas are sized using the *_AREA_SIZE parameters.
• The memory allocated for PGA_AGGREGATE_TARGET has no relation with SGA_TARGET.
• The similarity is both is taken from total memory of the system.
• The minimum value of this parameter is 10 MB and maximum is 4096 GB – 1.

SQL >show parameter pga_aggregate_target

Workarea_Size_Policy
WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the
mode in which working areas are tuned.

AUTO
Work areas used by memory-intensive operators are sized automatically, based on the PGA memory used
by the system, the target PGA memory set in PGA_AGGREGATE_TARGET, and the requirement of
each individual operators

MANUAL
The sizing of work areas is manual and based on the values of the *_AREA_SIZE parameter
corresponding to the operation (for example, a sort uses SORT_AREA_SIZE). Specifying MANUAL
may result in sub-optimal performance and poor PGA memory utilization

SQL > show parameter workarea_size_policy

PGA usage statistics:
SQL > select * from v$pgastat;

Determine a good setting for pga_aggregate_target:
SQL > select * from v$pga_target_advice order by pga_target_for_estimate;

Show the maximum PGA usage per process:
SQL > select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;