详细解读 STATSPACK 报告

1 0 40.0 1.0 1 0 0 0.0

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

File IO Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14 ->ordered by Tablespace, File

Tablespace Filename

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

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

Av Av Av Av Buffer Av Buf

Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)

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

ICD_DXH_DAT /dev/rlv_data001

377 0 9.4 1.0 1,640 2 321 0.2

/dev/rlv_data002

327 0 9.0 1.0 1,630 2 169 0.0

/dev/rlv_data003

313 0 10.0 1.0 1,718 2 87 0.0

/dev/rlv_data004

357 0 9.9 1.0 1,661 2 86 0.0

/dev/rlv_data005

400 0 11.5 1.0 1,627 2 128 0.2

/dev/rlv_data006

389 0 8.5 1.0 6,700 8 126 0.0

/dev/rlv_data007

409 0 9.6 1.0 1,569 2 159 0.7

8、Buffer Pool统计信息

这里将buffer poll细分，列举default、keep、recycle三种类型的buffer的详细情况。在这份报告中，我们的系统中只使用Default size的buffer pool。这里的3个waits统计，其实在前面的等待时间中已经包含，所以可以参考前面的描述。关于命中率也已经在前面讨论。 所以，其实这段信息不需要怎么关注。

Buffer Pool Statistics for DB: ORA92 Instance: ora92 Snaps: 13 -14 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Free Write Buffer

Number of Cache Buffer Physical Physical Buffer Complete Busy

P Buffers

Hit % Gets Reads Writes Waits Waits Waits --- ---------- ----- ----------- ----------- ---------- ------- -------- ------

D 635,200 99.9 19,556,815 26,990 88,450 0 0 3,368

9、实例的恢复情况统计信息

这部分主要是关于实例的恢复的一些统计信息，也不需要怎么关注。

Instance Recovery Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14 -> B: Begin snapshot, E: End snapshot

Targt Estd Log File Log Ckpt Log Ckpt

MTTR MTTR Recovery Actual Target Size Timeout Interval

(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks

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

B 300 70 54311 452198 450720 450720 1224858 E 300 69 53127 452947 450720 450720 1472619

10、Buffer Pool调整的Advisory

这是oracle的对buffer pool的大小的调整建议。从advisory的数据看，当然buffer是越大，物理读更小，随着buffer的增大，对物理读的性能改进越来越小。当前buffer 设置为5,120M，物理读因子=1。我们可以看到，buffer pool在3G之前的扩大，对物理读的改善非常明显，之后，这种改善的程度越来越低。

Buffer Pool Advisory for DB: ORA92 Instance: ora92 End Snap: 14 -> Only rows with estimated physical reads >0 are displayed

-> ordered by Block Size, Buffers For Estimate (default block size first)

Size for Size Buffers for Est Physical Estimated

P Estimate (M) Factr Estimate Read Factor Physical Reads

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

D 512 .1 63,520 9.85 245,880,558

D 1,024 .2 127,040 5.41 134,932,093

D 1,536 .3 190,560 3.38 84,471,707

D 2,048 .4 254,080 2.41 60,240,471

D 2,560 .5 317,600 1.86 46,399,611

D 3,072 .6 381,120 1.54 38,365,243

D 3,584 .7 444,640 1.32 33,017,978

D 4,096 .8 508,160 1.18 29,353,901

D 4,608 .9 571,680 1.07 26,763,133

D 5,120 1.0 635,200 1.00 24,962,078

D 5,632 1.1 698,720 0.95 23,661,399

D 6,144 1.2 762,240 0.91 22,672,122

D 6,656 1.3 825,760 0.88 21,902,502

D 7,168 1.4 889,280 0.85 21,277,585