详细解读 STATSPACK 报告3

最新推荐文章于 2024-05-16 20:28:16 发布

cmkcnufwy33419835

最新推荐文章于 2024-05-16 20:28:16 发布

阅读量82

点赞数

文章标签：数据库

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

700 1.4 4,398,132.4 0.0 100.0 0

800 1.6 4,398,132.4 0.0 100.0 0

900 1.8 4,398,132.4 0.0 100.0 0

1,000 2.0 4,398,132.4 0.0 100.0 0

1,500 3.0 4,398,132.4 0.0 100.0 0

2,000 4.0 4,398,132.4 0.0 100.0 0

3,000 6.0 4,398,132.4 0.0 100.0 0

4,000 8.0 4,398,132.4 0.0 100.0 0

14、队列的统计信息

关于Enqueue，我们在等待事件里面已经作了比较详尽的描述，这里只是对等待事件的一个展开描述，分项的含义请参考在等待事件的说明。

Enqueue activity for DB: ORA92 Instance: ora92 Snaps: 13 -14

-> Enqueue stats gathered prior to 9i should not be compared with 9i data

-> ordered by Wait Time desc, Waits desc

Avg Wt Wait

Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)

-- ------------ ------------ ----------- ----------- ------------- ------------

TX 81,127 81,263 0 674 134.32 91

SQ 2,032 2,032 0 53 .25 0

HW 107 107 0 1 2.00 0

15、回滚段统计信息

从9i开始，回滚段一般都是自动管理的，一般情况下，这里我们不需要太重点关注。

在这里，主要关注pct waits，如果出现比较多的pct waits，那就需要增加回滚段的数量或者增大回滚段的空间。另外，观察一下各个回滚段使用的情况，比较理想的是各个回滚段上Avg Active比较均衡。

在oracle 9i之前，回滚段时手工管理的，可以通过指定optimal值来设定一个回滚段收缩的值，如果不设定，默认也应当为initial+(minextents-1)*next extents ，这个指定的结果，就是限制了回滚段不能无限制的增长，当超过optimal的设定值后，在适当的时候，oracle会shrinks到optimal大小。但是9i之后，undo一般都设置为auto模式，在这种模式下，我们无法指定optimal值，好像也没有默认值，所以无法shrinks，回滚段就会无限制的增长，一直到表空间利用率达到为100％，如果表空间设置为自动扩展的方式，这种情况下，就更糟糕，undo将无限制的增长。在这里，我们也可以看到，shrinks的值为0，也就是说，从来就没收缩过。

Rollback Segment Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14

->A high value for "Pct Waits" suggests more rollback segments may be required

->RBS stats may not be accurate between begin and end snaps when using Auto Undo

managment, as RBS may be dynamically created and dropped as needed

Trans Table Pct Undo Bytes

RBS No Gets Waits Written Wraps Shrinks Extends

------ -------------- ------- --------------- -------- -------- --------

0 4.0 0.00 0 0 0 0

1 67,197.0 0.00 52,642,136 7 0 7

2 16,647.0 0.00 12,321,446 2 0 2

3 9,179.0 0.00 7,032,792 8 0 8

4 8,004.0 0.00 6,735,562 3 0 2

5 7,748.0 0.00 6,428,610 3 0 0

6 7,848.0 0.00 5,847,978 6 0 3

7 7,825.0 0.00 6,115,490 6 0 6

8 7,878.0 0.00 6,782,332 5 0 4

9 8,119.0 0.00 7,708,258 8 0 5

10 7,634.0 0.00 5,493,894 5 0 6

11 7,645.0 0.00 6,633,562 1 0 0

12 7,624.0 0.00 4,911,454 5 0 5

13 7,514.0 0.00 6,006,992 0 0 0

14 7,656.0 0.00 5,171,854 6 0 6

-------------------------------------------------------------

Rollback Segment Storage for DB: ORA92 Instance: ora92 Snaps: 13 -14

->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size

------ --------------- --------------- --------------- ---------------

0 385,024 59,380 385,024

1 176,283,648 6,272,394 176,283,648

2 92,397,568 3,103,770 92,397,568

3 54,648,832 1,317,711 116,514,816

4 47,308,800 1,769,136 520,216,576

5 38,920,192 3,437,158 92,397,568

6 34,725,888 3,251,782 93,446,144

7 64,086,016 2,344,243 100,786,176

8 41,017,344 3,139,717 130,146,304

9 41,017,344 1,793,144 113,369,088

10 57,794,560 1,822,037 109,174,784

11 36,823,040 838,860 36,823,040

12 50,454,528 719,518 50,454,528

13 52,551,680 429,400 52,551,680

14 45,211,648 967,324 45,211,648

-------------------------------------------------------------

Undo Segment Summary for DB: ORA92 Instance: ora92 Snaps: 13 -14

-> Undo segment block stats:

-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed

-> eS - expired Stolen, eR - expired Released, eU - expired reUsed

Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/

TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU

---- -------------- ---------- -------- ---------- -------- ------ -------------

1 13,853 ########## 56 1 0 0 0/0/0/0/0/0

-------------------------------------------------------------

Undo Segment Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14

-> ordered by Time desc

Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/

End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU

------------ ------------ -------- ------- -------- ------- ------ -------------

14-Jul 00:28 13,853 ######## 56 1 0 0 0/0/0/0/0/0

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

转载于:http://blog.itpub.net/22782597/viewspace-619434/

cmkcnufwy33419835

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
详细解读 STATSPACK 报告3

11、Buffer Pool等待情况统计这里的buffer等待往往带来data block的比较大的等待。这部分等待的情况在前面等待事件中已经作过描述。Buffer wait Statistics for DB: ORA...
复制链接

扫一扫