Dynamic Performance (V$) Views, 131 of 238

V$PGA_TARGET_ADVICE

V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty if PGA_AGGREGATE_TARGET is not set. In addition, the content of this view is not updated if the STATISTICS_LEVEL parameter is set to BASIC . Base statistics for this view are reset at instance startup and when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.

Column	Datatype	Description
`PGA_TARGET_FOR_ESTIMATE`	`NUMBER`	Value of `PGA_AGGREGATE_TARGET` for this prediction (in bytes)
`PGA_TARGET_FACTOR`	`NUMBER`	`PGA_TARGET_FOR_ESTIMATE` / the current value of the `PGA_AGGREGATE_TARGET` parameter
`ADVICE_STATUS`	`VARCHAR2(3)`	Indicates whether the advice is enabled (`ON`) or disabled (`OFF`) depending on the value of the `STATISTICS_LEVEL` parameter
`BYTES_PROCESSED`	`NUMBER`	Total bytes processed by all the work areas considered by this advice (in bytes)
`ESTD_EXTRA_BYTES_RW`	`NUMBER`	Estimated number of extra bytes which would be read or written if `PGA_AGGREGATE_TARGET` was set to the value of the `PGA_TARGET_FOR_ESTIMATE` column. This number is derived from the estimated number and size of work areas which would run in one-pass (or multi-pass) for that value of `PGA_AGGREGATE_TARGET`.
`ESTD_PGA_CACHE_HIT_PERCENTAGE`	`NUMBER`	Estimated value of the cache hit percentage statistic when `PGA_AGGREGATE_TARGET` equals `PGA_TARGET_FOR_ESTIMATE`. This column is derived from the above two columns and is equal to `BYTES_PROCESSED` / (`BYTES_PROCESSED` + `ESTD_EXTRA_BYTES_RW`)
`ESTD_OVERALLOC_COUNT`	`NUMBER`	Estimated number of PGA memory over-allocations if the value of `PGA_AGGREGATE_TARGET` is set to `PGA_TARGET_FOR_ESTIMATE`. A nonzero value means that `PGA_TARGET_FOR_ESTIMATE` is not large enough to run the work area workload. Hence, the DBA should not set `PGA_AGGREGATE_TARGET` to `PGA_TARGET_FOR_ESTIMATE` since Oracle will not be able to honor that target.

See Also:

Oracle9i Database Performance Tuning Guide and Reference for information on tuning the PGA_AGGREGATE_TARGET initialization parameter using the PGA advice views