`V$PGA_TARGET_ADVICE_HISTOGRAM`

predicts how statistics displayed by the `V$SQL_WORKAREA_HISTOGRAM`

dynamic view would be impacted if the value of the `PGA_AGGREGATE_TARGET`

parameter is changed. This 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 when the `STATISTICS_LEVEL`

initialization parameter is set to `BASIC`

. Base statistics for this view are reset at instance startup or 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 |

`LOW_OPTIMAL_SIZE` |
`NUMBER` |
Lower bound for the optimal memory requirement of work areas included in this row (in bytes) |

`HIGH_OPTIMAL_SIZE` |
`NUMBER` |
Upper bound for the optimal memory requirement of work areas included in this row (in bytes) |

`ESTD_OPTIMAL_EXECUTIONS` |
`NUMBER` |
Number of work areas with an optimal memory requirement comprised between `LOW_OPTIMAL_SIZE` and `HIGH_OPTIMAL_SIZE` which are predicted to run optimal given a value of `PGA_AGGREGATE_TARGET` equal to `PGA_TARGET_FOR_ESTIMATE` |

`ESTD_ONEPASS_EXECUTIONS` |
`NUMBER` |
Number of work areas with an optimal memory requirement comprised between `LOW_OPTIMAL_SIZE` and `HIGH_OPTIMAL_SIZE` which are predicted to run one-pass given a value of `PGA_AGGREGATE_TARGET` equal to `PGA_TARGET_FOR_ESTIMATE` |

`ESTD_MULTIPASSES_EXECUTIONS` |
`NUMBER` |
Number of work areas with an optimal memory requirement comprised between `LOW_OPTIMAL_SIZE` and `HIGH_OPTIMAL_SIZE` which are predicted to run multi-pass given a value of `PGA_AGGREGATE_TARGET` equal to `PGA_TARGET_FOR_ESTIMATE` |

`ESTD_TOTAL_EXECUTIONS` |
`NUMBER` |
Sum of `ESTD_OPTIMAL_EXECUTIONS` , `ESTD_ONEPASS_EXECUTIONS` , and `ESTD_MULTIPASSES_EXECUTIONS` |

`IGNORED_WORKAREAS_COUNT` |
`NUMBER` |
Number of work areas with optimal memory requirement between `LOW_OPTIMAL_SIZE` and `HIGH_OPTIMAL_SIZE` ignored in the advice generation due to memory and CPU constraints |

See Also:

Oracle Database Performance Tuning Guide for information on tuning the`PGA_AGGREGATE_TARGET`

initialization parameter using the PGA advice views