About PGA_AGGREGATE_TARGET parameter

In order to make you understand About PGA_AGGREGATE_TARGET parameter let's have a look at parameter *_AREA_SIZE.

SQL> SHOW PARAMETER _AREA_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
workarea_size_policy string AUTO

Here we see the parameter workarea_size_policy is set to AUTO because we have set non-zero value to pga_aggregate_target.

SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 525M

Now we try to set pga_aggregate_target to a zero value.

SQL> ALTER SYSTEM SET pga_aggregate_target=0;
ALTER SYSTEM SET pga_aggregate_target=0
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

Whenever we try to set to as scope=spfile it is not set because 0 is not valid value.
So, I set it to zero in the pfile.

SQL> CREATE PFILE='/export/home/oracle/pfile.ora' FROM SPFILE;
File created.

SQL> !vi /export/home/oracle/pfile.ora
*.pga_aggregate_target=0

Now I have started database with this pfile.

SQL> STARTUP FORCE PFILE='/export/home/oracle/pfile.ora';
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size 2021216 bytes
Variable Size 218106016 bytes
Database Buffers 1426063360 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.

Now have a look at the values. We will see that workarea_size_policy parameter is set to MANUAL.

SQL> SHOW PARAMETER _AREA_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
workarea_size_policy string MANUAL
SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0

Now let me say about 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.

• Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory.

• 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.