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

本站中文解释

Oracle视图V$PGA_TARGET_ADVICE是可以用来分析查看根据你给出的PGA_TARGET_FACTOR,接着系统给出推荐的PGA设置来改善系统效率的视图。其中PGA_TARGET_FACTOR由用户设定,表明当前连接要占用PGA内存的比例;推荐的PGA_TARGET设置应该使系统最大程度的提高,操作的系统效率。

用户可以使用V$PGA_TARGET_ADVICE视图来侦测和调整PGA内存的设置,来改善Oracle数据库的性能。例如,执行以下查询来检查当前的PGA_TARGET的值:

SELECT pga_target_for_estimate, pga_target_factor, estd_pga_target_cache_miss_percentage FROM v$pga_target_advice;

执行这段代码后,会得到当前PING_TARGET的值,当前的PGA_TARGET_FACTOR,以及推荐的PGA_TARGET_CACHE_MISS_PERCENTAGE。

官方英文解释

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.

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

See Also:

  • “V$PGASTAT”

  • “PGA_AGGREGATE_TARGET”

  • “STATISTICS_LEVEL”

  • Oracle Database
    Performance Tuning Guide
    for information on tuning the PGA_AGGREGATE_TARGET initialization parameter using the PGA advice views


数据运维技术 » Oracle 视图 V$PGA_TARGET_ADVICE 官方解释,作用,如何使用详细说明