D 7,680 1.5 952,800 0.83 20,755,944
D 8,192 1.6 1,016,320 0.81 20,331,009
D 8,704 1.7 1,079,840 0.80 19,949,127
D 9,216 1.8 1,143,360 0.78 19,563,065
D 9,728 1.9 1,206,880 0.77 19,226,351
D 10,240 2.0 1,270,400 0.76 18,948,622
11、Buffer Pool等待情况统计
这里的buffer等待往往带来data block的比较大的等待。这部分等待的情况在前面等待事件中已经作过描述。
Buffer wait Statistics for DB: ORA92 Instance: ora92 Snaps: 13 -14 -> ordered by wait time desc, waits desc
Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- data block 3,086 0 0 undo block 196 0 0 undo header 87 0 0 1st level bmb 3 0 0 2nd level bmb 1 0 0
12、PGA统计信息
这一部分主要展现的是PGA使用的情况，我们可以根据具体的情况通过设置参数PGA_AGGREGATE_TARGET来调整PGA的值。
在这里，设置的pga_aggregate_target=500M，并发数大概为270。而且数据库设置为DEDICATED模式，在这种情况下，PGA要求有更大的空间，因为在PGA下需要存放stack space，user serssion data，cursor state信息。
通过下面的两个信息，我们可以看到当前的设置下，PGA Cache Hit达到了100％，所有的操作都是内存中完成的。
PGA Aggr Target Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14 -> B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------- 100.0 2,421 0 %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M)
Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 500 354 216.3 0.0 .0 .0 .0 25,600
E 500 355 214.4 0.0 .0 .0 .0 25,600
PGA Aggr Target Histogram for DB: ORA92 Instance: ora92 Snaps: 13 -14 -> Optimal Executions are purely in-memory operations Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 8K 16K 246,058 246,058 0 0
16K 32K 104 104 0 0
32K 64K 1 1 0 0
64K 128K 3 3 0 0
128K 256K 2 2 0 0
256K 512K 2 2 0 0
512K 1024K 1 1 0 0
2M 4M 4 4 0 0
13、PGA调整的Advisory
PGA_AGGREGATE_TARGET参数的调整建议。
我们可以看到，在advisory中，当PGA_AGGREGATE_TARGET达到500M时，再增大PGA_AGGREGATE_TARGET，基本已经起不到提升性能的作用了。
PGA Memory Advisory for DB: ORA92 Instance: ora92 End Snap: 14 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
63 0.1 4,398,132.4 17,448.1 100.0 34,943
125 0.3 4,398,132.4 4,267.6 100.0 47
250 0.5 4,398,132.4 435.8 100.0 0
375 0.8 4,398,132.4 382.9 100.0 0
500 1.0 4,398,132.4 0.0 100.0 0
600 1.2 4,398,132.4 0.0 100.0 0