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 find the server is whether standby (slave) or primary(master) in Postgresql replication ?

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

7 Steps to configure BDR replication in postgresql

Postgres Streaming Replication Configuration

vacuumlo - removing large objects orphans from a database PostgreSQL