Oracle教程 2023-03-27

Oracle 视图 V$PGA_TARGET_ADVICE_HISTOGRAM 官方解释，作用，如何使用详细说明

本站中文解释

Oracle视图V$PGA_TARGET_ADVICE_HISTOGRAM用于在新发行的Oracle里面的数据库技术支持的概念。它显示了PGA_AGGREGATE_TARGET，该值可以帮助数据库将PGA内存请求分散和平衡到不同的PGA领域。此视图具有以下列：

EDITION：显示当前会话PGA建议值的指南版本号
PGA_SIZE_FACTOR：在PGA分配概率中所有与计算PGA大小一起使用的值
PGA_TOTAL_MEM：用于确定总PGA内存量的内存比重
MIN_PGA：用于提供最小PGA内存量的最小可能值
TARGET_FOR_ESTIMATE_PCT：用于计算比例值的建议目标值
ESTD_PGA_CACHE_HIT_PERCENTAGE：估计的PGA缓存命中率

使用方法：

1、首先，您需要设置PGA_AGGREGATE_TARGET参数，指定一个总的PGA内存大小以及最小的PGA内存大小：

ALTER SESSION SET PGA_AGGREGATE_TARGET= [Total PGA Memory], MIN_PGA=[Minimum PGA Memory];

2、接下来，可以使用以下查询选择V$PGA_TARGET_ADVICE_HISTOGRAM视图，以查看PGA_AGGREGATE_TARGET参数的当前建议值：

SELECT EDITION, PGA_SIZE_FACTOR, PGA_TOTAL_MEM , MIN_PGA, TARGET_FOR_ESTIMATE_PCT, ESTD_PGA_CACHE_HIT_PERCENTAGE
FROM V$PGA_TARGET_ADVICE_HISTOGRAM;

3、建议值将定义比例分配PGA，如果您指定PGA_AGGREGATE_TARGET和MIN_PGA，则可以看到PGA_SIZE_FACTOR列将提供按比例将可用PGA内存划分到不同的PGA领域。

官方英文解释

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
`CON_ID`	`NUMBER`	The ID of the container to which the data pertains. Possible values include: `0`: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. `1`: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data

本站中文解释

官方英文解释

相关推荐