v$pgastat和v$pga_target_advice学习

一、v$pgastat存储着pga使用情况的一些信息，当pga_aggregate_target参数设置为非0时（即pga在自动管理模式下）会自动收集pga的实用信息，在实力已启动后就开始收集的。

V$PGASTAT provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.

Column	Datatype	Description
NAME	VARCHAR2(64)	Name of the statistic (see Table 7-2)
VALUE	NUMBER	Statistic value
UNIT	VARCHAR2(12)	Unit for the value (microseconds, bytes, or percent)

Table 7-2 V$PGASTAT Statistics

Statistic Name	Description
aggregate PGA target parameter	Current value of the PGA_AGGREGATE_TARGET initialization parameter. If this parameter is not set, then its value is 0 and automatic management of PGA memory is disabled.
aggregate PGA auto target	Amount of PGA memory the Oracle Database can use for work areas running in automatic mode. This amount is dynamically derived from the value of the PGA_AGGREGATE_TARGET initialization parameter and the current work area workload, and continuously adjusted by the Oracle Database. If this value is small compared to the value of PGA_AGGREGATE_TARGET, then a large amount of PGA memory is used by other components of the system (for example, PL/SQL or Java memory) and little is left for work areas. The DBA must ensure that enough PGA memory is left for work areas running in automatic mode.
global memory bound	Maximum size of a work area executed in automatic mode. This value is continuously adjusted by the Oracle Database to reflect the current state of the work area workload. The global memory bound generally decreases when the number of active work areas is increasing in the system. If the value of the global bound decreases below 1 MB, then the value of PGA_AGGREGATE_TARGET should be increased.
total PGA allocated	Current amount of PGA memory allocated by the instance. The Oracle Database attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value.
total PGA used	Indicates how much PGA memory is currently consumed by work areas. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).
total PGA used for auto workareas	Indicates how much PGA memory is currently consumed by work areas running under the automatic memory management mode. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).
total PGA used for manual workareas	Indicates how much PGA memory is currently consumed by work areas running under the manual memory management mode. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).
over allocation count	This statistic is cumulative since instance startup. Over allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small. When this happens, the Oracle Database cannot honor the value of PGA_AGGREGATE_TARGET and extra PGA memory needs to be allocated. If over allocation occurs, then increase the value of PGA_AGGREGATE_TARGET using the information provided by the V$PGA_TARGET_ADVICE view.
bytes processed	Number of bytes processed by memory intensive SQL operators, cumulated since instance startup.
extra bytes read/written	Number of bytes processed during extra passes of the input data, cumulated since instance startup. When a work area cannot run optimal, one or more of these extra passes is performed.
cache hit percentage	A metric computed by the Oracle Database to reflect the performance of the PGA memory component, cumulative since instance startup. A value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory. When a work area cannot run optimal, one or more extra passes is performed over the input data. This will reduce the cache hit percentage in proportion to the size of the input data and the number of extra passes performed.

1、aggregate PGA target parameter 表示当前pga_aggregate_target的值，如果pga_aggregate_target为空，则此参数值为0，表示未启用pga的自动调整。

2、aggregate PGA auto target ：在自动管理模式下，可用于工作区的总的PGA内存数。这个数值是动态的，和PGA_AGGREGATE_TARGET 的值以及当前工作区的负载有关，Oracle会动态调整它。这个值相对于PGA_AGGREGATE_TARGET来说很小。其他很大一部分的PGA内存都被用于系统的其他组件（如PLSQL和Java的内存）。DBA必须保证在自动模式下有足够的PGA内存用于工作区。

3、global memory bound:自动模式下可用的工作区的最大大小。Oracle根据当前工作区的负载动态调整这个值。当系统中活动的工作区数量增加时，global memory bound一般会下降。如果global bound 降到低于1M，则要考虑增加PGA_AGGREGATE_TARGET了。

4、total pga allocate：当前实例分配的总的PGA内存大小。Oracle会试图保持这个值在PGA_AGGREGATE_TARGET以内。然而，当工作区负载增加得非常快或者PGA_AGGREGATE_TARGET被设置得很小时，这个值也许会在一段时间内超过PGA_AGGREGATE_TARGET。

5、total pga used：当前被工作区消耗得PGA内存。这个数值也可以用于计算有多少PGA内存被其他组件（如PLSQL或Java）所消耗。

6、total pga used for auto workarea：自动模式下，当前多少PGA内存被工作区所消耗。这个数值也可以用于计算有多少PGA内存被其他组件（如PLSQL或Java）所消耗。

7、total pga used for manul workarea：手动模式下，当前多少PGA内存被工作区所消耗。这个数值也可以用于计算有多少PGA内存被其他组件（如PLSQL或Java）所消耗。

8、over allocation count：这个数值是自从实例启动后累加的。当PGA_AGGREGATE_TARGET设置非常小或工作区负载增长很快时，会超额分配PGA内存（分配的值大于PGA_AGGREGATE_TARGET）。这种情况发生时，Oracle不能限制PGA内存小于PGA_AGGREGATE_TARGET，只能分配实际需要的PGA内存。此时，建议通过建议器视图V$PGA_TARGET_ADVICE来增加PGA_AGGREGATE_TARGET的大小。

9、bytes processed：自从实例启动后，被内存SQL操作处理的字节数。

10、cache hit percentage：Oracle计算出来的一个与PGA内存组件性能相关的数据，是自从实例启动后累加的。如果这个值是100%，则表示实例启动后，所有系统使用到的工作区都分配了最佳的PGA内存。

当工作区无法在最佳状态下运行，就需要进行额外的数据输入处理，这将会降低cache hit percentage。

二、v$pga_target_advice

这个视图是可以显示PGA优化建议器的估算预测结果，它显示了在各种PGA_AGGREGATE_TARGET值时，V$PGASTAT可能会显示的PGA性能统计数据。选取所用来预测的PGA_AGGREGATE_TARGET值是当前PGA_AGGREGATE_TARGET左右的的值。而估算出的统计值是根据实例启动后的负载模拟出来的。

只有当建议器打开（隐含参数_smm_advice_enabled为TRUE），并且参数STATISTICS_LEVEL值不是BASIC时，视图中才会有内容。实例重启后，所有预测数据都会被重写。

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.

1、 PGA_TARGET_FOR_ESTIMATE：用于预测的PGA_AGGREGATE_TARGET值。

2、PGA_TARGET_FACTOR：预测的PGA_AGGREGATE_TARGET与当前PGA_AGGREGATE_TARGET的比。

3、ADVICE_STATUS：建议器状态（ON或OFF）

4、BYTES_PROCESSED：预测的被所有工作区处理的字节数。

5、ESTD_EXTRA_BYTES_RW：当PGA_AGGREGATE_TARGET设置为预测值时，需要额外读写的字节数。

6、ESTD_PGA_CACHE_HIT_PERCENTAGE：当PGA_AGGREGATE_TARGET设置为预测值时，缓存命中率。这个值等于BYTES_PROCESSED / (BYTES_PROCESSED + ESTD_EXTRA_BYTES_RW)

7、ESTD_OVERALLOC_COUNT：当设置PGA_AGGREGATE_TARGET为当前预测值是pga大小超出pga_aggregate_target大小的次数。如果为非0则表示当前预测的pga的大小太小，不能使用当前预测的值来作为pga_aggregate_target的大小。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23655288/viewspace-718456/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23655288/viewspace-718456/