Dynamic Performance (V$) Views, 133 of 238

V$PGASTAT

V$PGASTAT provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.

Column Datatype Description

Column	Datatype	Description
`NAME`	`VARCHAR2(64)`	Name of the statistic: `aggregate PGA target parameter` - Current value of the `PGA_AGGREGATE_TARGET` initialization parameter. If this parameter is not set, then its value is `0` and automatic management of PGA memory is disabled. `aggregate PGA auto target` - Amount of PGA memory Oracle can use for work areas running in automatic mode. This amount is dynamically derived from the value of the `PGA_AGGREGATE_TARGET` initialization parameter and the current work area workload, and continuously adjusted by Oracle. If this value is small compared to the value of `PGA_AGGREGATE_TARGET`, then a large amount of PGA memory is used by other components of the system (for example, PL/SQL or Java memory) and little is left for work areas. The DBA must ensure that enough PGA memory is left for work areas running in automatic mode. `global memory bound` - Maximum size of a work area executed in automatic mode. This value is continuously adjusted by Oracle to reflect the current state of the work area workload. The global memory bound generally decreases when the number of active work areas is increasing in the system. If the value of the global bound decreases below 1 MB, then the value of `PGA_AGGREGATE_TARGET` should be increased. `total PGA allocated` - Current amount of PGA memory allocated by the instance. Oracle attempts to keep this number below the value of the `PGA_AGGREGATE_TARGET` initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when `PGA_AGGREGATE_TARGET` is set to a small value. `total PGA used` - Indicates how much PGA memory is currently consumed by work areas. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java). `total PGA used for auto workareas` - Indicates how much PGA memory is currently consumed by work areas running under the automatic memory management mode. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java). `total PGA used for manual workareas` - Indicates how much PGA memory is currently consumed by work areas running under the manual memory management mode. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java). `over allocation count` - This statistic is cumulative since instance startup. Over allocating PGA memory can happen if the value of `PGA_AGGREGATE_TARGET` is too small. When this happens, Oracle cannot honor the value of `PGA_AGGREGATE_TARGET` and extra PGA memory needs to be allocated. If over allocation occurs, then increase the value of `PGA_AGGREGATE_TARGET` using the information provided by the `V$PGA_TARGET_ADVICE` view. `bytes processed` - Number of bytes processed by memory intensive SQL operators, cumulated since instance startup. `extra bytes read/written` - Number of bytes processed during extra passes of the input data, cumulated since instance startup. When a work area cannot run optimal, one or more of these extra passes is performed. `cache hit percentage` - A metric computed by Oracle to reflect the performance of the PGA memory component, cumulative since instance startup. A value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory. When a work area cannot run optimal, one or more extra passes is performed over the input data. This will reduce the cache hit percentage in proportion to the size of the input data and the number of extra passes performed.
`VALUE`	`NUMBER`	Statistic value
`UNITS`	`VARCHAR2(12)`	Unit for the value (microseconds, bytes, or percent)

NAME

VARCHAR2(64)

Name of the statistic:

aggregate PGA target parameter - Current value of the PGA_AGGREGATE_TARGET initialization parameter. If this parameter is not set, then its value is 0 and automatic management of PGA memory is disabled.
aggregate PGA auto target - Amount of PGA memory Oracle can use for work areas running in automatic mode. This amount is dynamically derived from the value of the PGA_AGGREGATE_TARGET initialization parameter and the current work area workload, and continuously adjusted by Oracle.

If this value is small compared to the value of PGA_AGGREGATE_TARGET, then a large amount of PGA memory is used by other components of the system (for example, PL/SQL or Java memory) and little is left for work areas. The DBA must ensure that enough PGA memory is left for work areas running in automatic mode.
global memory bound - Maximum size of a work area executed in automatic mode. This value is continuously adjusted by Oracle to reflect the current state of the work area workload. The global memory bound generally decreases when the number of active work areas is increasing in the system.

If the value of the global bound decreases below 1 MB, then the value of PGA_AGGREGATE_TARGET should be increased.
total PGA allocated - Current amount of PGA memory allocated by the instance. Oracle attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value.
total PGA used - Indicates how much PGA memory is currently consumed by work areas. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).
total PGA used for auto workareas - Indicates how much PGA memory is currently consumed by work areas running under the automatic memory management mode. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).
total PGA used for manual workareas - Indicates how much PGA memory is currently consumed by work areas running under the manual memory management mode. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).
over allocation count - This statistic is cumulative since instance startup. Over allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small. When this happens, Oracle cannot honor the value of PGA_AGGREGATE_TARGET and extra PGA memory needs to be allocated.

If over allocation occurs, then increase the value of PGA_AGGREGATE_TARGET using the information provided by the V$PGA_TARGET_ADVICE view.
bytes processed - Number of bytes processed by memory intensive SQL operators, cumulated since instance startup.
extra bytes read/written - Number of bytes processed during extra passes of the input data, cumulated since instance startup. When a work area cannot run optimal, one or more of these extra passes is performed.
cache hit percentage - A metric computed by Oracle to reflect the performance of the PGA memory component, cumulative since instance startup. A value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory.

When a work area cannot run optimal, one or more extra passes is performed over the input data. This will reduce the cache hit percentage in proportion to the size of the input data and the number of extra passes performed.

VALUE

NUMBER

Statistic value

UNITS

VARCHAR2(12)

Unit for the value (microseconds, bytes, or percent)