V$PGA_TARGET_ADVICE

Oracle 11gR1

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_TIME	NUMBER	Time (in seconds) required to process the 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.

Note:

1.

SELECT * FROM V$PGA_TARGET_ADVICE;

PGA_TARGET_ FOR_ESTIMATE	PGA_TARGET _FACTOR	ADVICE_ STATUS	BYTES_PROCESSED	ESTD_EXTRA_ BYTES_RW	ESTD_PGA_ CACHE_HIT_ PERCENTAGE	ESTD_OVERALLOC _COUNT
402653184	0.125	ON	31193845513216	11037966096384	74	412433
805306368	0.25	ON	31193845513216	10057414220800	76	317206
1610612736	0.5	ON	31193845513216	1315541655552	96	9
2415919104	0.75	ON	31193845513216	1142952473600	96	0
3221225472	1	ON	31193845513216	1122061247488	97	0
3865469952	1.2	ON	31193845513216	1088901567488	97	0
4509715456	1.4	ON	31193845513216	1088637995008	97	0
5153959936	1.6	ON	31193845513216	1088353426432	97	0
5798205440	1.8	ON	31193845513216	1088353426432	97	0
6442450944	2	ON	31193845513216	1088353426432	97	0
9663676416	3	ON	31193845513216	1088353426432	97	0
12884901888	4	ON	31193845513216	1088353426432	97	0
19327352832	6	ON	31193845513216	1088353426432	97	0
25769803776	8	ON	31193845513216	1088353426432	97	0

Oracle dynamic performance views