Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

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.

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 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 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 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 
  • 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 specifies the policy for sizing work areas. This parameter controls the
mode in which working areas are tuned.

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

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;


Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

PostgreSQL pgBadger

7 Steps to configure BDR replication in postgresql

Postgres Database Patch