Database Reference
In-Depth Information
NAME VALUE UNIT
------------------------------ ----------- -----
aggregate PGA target parameter 1073741824 bytes
aggregate PGA auto target
910411776
bytes
SQL> execute pga_pkg.
allocate(500000)
SQL> SELECT name, value, unit
2 FROM v$pgastat
3 WHERE name LIKE 'aggregate PGA %';
NAME VALUE UNIT
------------------------------ ----------- -----
aggregate PGA target parameter 1073741824 bytes
aggregate PGA auto target
375754752
bytes
SQL> execute dbms_session.
reset_package
;
SQL> SELECT name, value, unit
2 FROM v$pgastat
3 WHERE name LIKE 'aggregate PGA %';
NAME VALUE UNIT
------------------------------ ----------- -----
aggregate PGA target parameter 1073741824 bytes
aggregate PGA auto target
910411776
bytes
The
pga_aggregate_target
initialization parameter is dynamic and can be changed only at the instance level.
In a 12.1 multitenant environment, it can't be set at the PDB level.
pga_aggregate_limit
The
pga_aggregate_limit
initialization parameter is new in version 12.1. It sets a hard limit to the amount of PGA
a database instance can use. The parameter is useful because, as described in the previous section, the value set
through the
pga_aggregate_target
initialization parameter is just a target, not a hard limit. In version 12.1, you can
now also specify a hard limit should the need arise.
The default value of the
pga_aggregate_limit
initialization parameter is set to the greater of the following values:
2GB
•
pga_aggregate_target
initialization parameter
•
Twice the value of the
processes
initialization parameter
•
3MB times the value of the
As a result, a limit is imposed by default. To avoid the limit, the parameter has to be set to 0. Setting the parameter
to a value lower than the default, except for 0, isn't possible. The following error is raised when you attempt to set a limit
lower than the default:
SQL> ALTER SYSTEM SET pga_aggregate_limit = 1G;
ALTER SYSTEM SET pga_aggregate_limit = 1G
*
ERROR at line 1: