W/A MB processed的含义及算法

霍俊路

你好：
在Oracle 11g r2的awr报告中，在Load Profile中，有一项内容为W/A MB processed。关于这项内容的含义是什么？如何使用Oracle的系统视图来获取该项的值？
下面的语句我们可以查询出该段时间内的redo size的值。对于W/A MB processed的获取，请指教一下。
SELECT e.VALUE - b.VALUE FROM dba_hist_sysstat b, dba_hist_sysstat e WHERE b.dbid = e.dbid AND b.instance_number = e.instance_number AND b.dbid = &dbid AND b.instance_number = &inst_num AND b.snap_id = &bid AND e.snap_id = &eid AND b.stat_name = e.stat_name AND b.stat_name ='redo size';

Maclean Liu(刘相兵

W/A MB processed ==>W/A (WorkArea) PGA工作区中处理的数据量



the PGA estimates; a value of 1 indicates the current PGA size. The 'PGA Target Est(MB)' value of this row will show your current PGA size: 128MB in this example. Other columns you will be interested in are 'Estd Extra W/A MB Read/ Written to Disk ' and 'Estd PGA Overalloc Count'.
When you go down or up the advisory section from the row with 'Size Factr' = 1.0, you get estimates for Disk usage - column 'Estd Extra W/A MB Read/ Written to Disk ' - for bigger or smaller settings of PGA_AGGREGATE_TARGET. The less Disk usage figure in this column, usually the better. A lower value means less work areas have to be spilled to disk, enhancing performance of the Oracle instance.

Your first goal is to have such a setting of PGA_AGGREGATE_TARGET, that number in the column 'Estd Extra W/A MB Read/Written to Disk ' does not substantially reduce any more. In the example output this happens at value 99,935.0. In other words, further increases of PGA_AGGREGATE_TARGET won't give any substantial benefit and will only waste memory. The row corresponding to this value shows a 'Size Factr' column vale of 4.0, indicating that the current PGA size should be increased 4 times (to 512MB) to reach this goal.

霍俊路

Maclean Liu(刘相兵 发表于 2013-2-19 10:43
W/A MB processed ==>W/A (WorkArea) PGA工作区中处理的数据量

非常感谢您的指导。对于这个问题我已经理解了。对于PGA,我一直都想深入的了解一下。
在concept中介绍，PGA分为2个部分，work area和private area。对于private area，包括persistent area和
runtime area。我想请问这2部分（persistent area和runtime area）能否通过某些方法(如 系统视图，跟踪事件等)获取里面的内容。我想细化的了解一下，该部分的主要作用。