ORACLE I/O 性能分析浅析

http://hi.baidu.com/tantea/blog/item/dba61bce3b4bd230f9dc6151.html
ORACLE I/O 性能分析浅析
2010-06-25 17:48

ORACLE I/O性能分析步骤：

1，性能分析的几个原则：
a, 检查消息的成功率。（成功率必须>99%，否则测试无意义）
b, 监测日志，不应出现异常。

2，分析CPU负荷。（mpstat可看多CPU情况，top->1可查看cpu情况)
命令：mpstat -P ALL 2 10

3，再查找哪个磁盘I/O吃紧 (iostat -x 1)
命令：iostat -x 1
注意：r/s+w/s，await, svctm, %util 四者之间的关系。（见后面描述）

4，然后查找 ORACLE 系统表 v$filestat 得知表空间的访问频率。
命令：Select phyrds,phywrts,d.name from v$datafile d,v$filestat f where f.file#=d.file# order by d.name


============

下面是实验室的测试结果：

1，看下面输出，其中iowait非常高，说明 oracle 可能存在 I/O 瓶颈。
[oracle@BJ-WAP-SA-1 ~]$ mpstat -P ALL 2 100
Linux 2.6.9-78.ELsmp (BJ-WAP-SA-1) 06/25/2010

09:02:59 AM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
09:03:01 AM all 1.25 0.00 0.94 13.56 0.00 0.00 0.00 84.25 5902.50
09:03:01 AM 0 0.00 0.00 0.50 0.50 0.00 0.00 0.00 99.00 0.00
09:03:01 AM 1 1.00 0.00 1.50 1.00 0.00 0.00 0.00 97.00 4.50
09:03:01 AM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.50
09:03:01 AM 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
09:03:01 AM 4 8.50 0.00 4.00 5.00 0.00 0.00 0.00 82.00 5460.00
09:03:01 AM 5 0.50 0.00 1.50 98.00 0.00 0.00 0.00 0.50 428.00
09:03:01 AM 6 0.00 0.00 0.00 3.50 0.00 0.00 0.00 96.00 0.00
09:03:01 AM 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 9.00

09:03:01 AM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
09:03:03 AM all 0.06 0.00 0.12 12.41 0.06 0.00 0.00 87.34 1333.50
09:03:03 AM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
09:03:03 AM 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 4.50
09:03:03 AM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.50
09:03:03 AM 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
09:03:03 AM 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1014.50
09:03:03 AM 5 0.00 0.00 0.50 99.50 0.00 0.00 0.00 0.00 305.00
09:03:03 AM 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
09:03:03 AM 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 9.00

2，看下面输出，可以得到如下结论：

a, cciss/c0d0p3 的 %util 为90.40%，说明CPU时间均用于排队了。
b, 继续分析：(w/s+r/s)*svctm 表明在1秒内总的服务时间，如果它略等于1秒，说明消息过来比较均速。否则，说明消息非常不均速。
c, 看其 await = 排队时间+svctm，相差较大，说明排队时间较长。

iostat -x 1

avg-cpu: %user %nice %system %iowait %steal %idle
0.75 0.00 0.25 12.50 0.00 86.75

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 194.06 0.00 182.18 0.00 3009.90 0.00 1504.95 16.52 1.25 6.89 5.41 98.61
cciss/c0d0p1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p2
0.00 27.72 0.00 29.70 0.00 459.41 0.00 229.70 15.47 0.33 11.23 10.30 30.59
cciss/c0d0p3
0.00 150.50 0.00 150.50 0.00 2407.92 0.00 1203.96 16.00 0.90 6.02 6.01 90.40
cciss/c0d0p5
0.00 15.84 0.00 1.98 0.00 142.57 0.00 71.29 72.00 0.02 8.00 8.00 1.58
cciss/c0d0p6
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p7
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.12 12.50 0.00 87.38

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 158.00 0.00 157.00 0.00 2528.00 0.00 1264.00 16.10 1.67 10.64 6.37 100.00
cciss/c0d0p1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p2
0.00 41.00 0.00 44.00 0.00 688.00 0.00 344.00 15.64 0.57 12.77 12.73 56.00
cciss/c0d0p3
0.00 117.00 0.00 113.00 0.00 1840.00 0.00 920.00 16.28 1.11 9.81 8.09 91.40
cciss/c0d0p5
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p6
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p7
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00


3， 看下面输出，在三个表空间 undotbs01.dbf，system01.dbf 和 wapgw_data_tablespace.dbf 访问非常频繁。尤以undotbs01.dbf为最繁忙。

SQL> select phyrds,phywrts,d.name from v$datafile d,v$filestat f where f.file#=d.file# order by d.name;

PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
37583 35088 /bases/oradata/wapgw_data_tablespace.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100624_21.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100624_22.dbf
74 22 /opt/oracle/oradata/CMCCWAP/120100624_23.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_00.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_01.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_02.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_03.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_04.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_05.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_06.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_07.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_08.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_09.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_10.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_11.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_12.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_13.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_14.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_15.dbf

PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
64 22 /opt/oracle/oradata/CMCCWAP/120100625_16.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_17.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_18.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_19.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_20.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_21.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_22.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_23.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_04.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_05.dbf
94 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_06.dbf
94 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_07.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_08.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_09.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_10.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_11.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_12.dbf
104 12 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_13.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_14.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_15.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_16.dbf

PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_17.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_18.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_19.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_20.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_21.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_22.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_23.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_00.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_01.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_02.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_03.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_04.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_05.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_06.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_07.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_08.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_09.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_10.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_04.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_05.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_06.dbf

PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_07.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_08.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_09.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_10.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_11.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_12.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_13.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_14.dbf
34 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_15.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_16.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_17.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_18.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_19.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_20.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_21.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_22.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_23.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_00.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_01.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_02.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_03.dbf

PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_04.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_05.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_06.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_07.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_08.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_09.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_10.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_11.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_12.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_13.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_14.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_15.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_16.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_17.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_18.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_19.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_20.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_21.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_22.dbf
104 12 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_23.dbf
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_1.dbf

PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_2.dbf
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_3.dbf
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_4.dbf
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_5.dbf
24 22 /opt/oracle/oradata/CMCCWAP/cwmlite01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/drsys01.dbf
173 22 /opt/oracle/oradata/CMCCWAP/example01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/indx01.dbf
34 22 /opt/oracle/oradata/CMCCWAP/odm01.dbf
10566 451 /opt/oracle/oradata/CMCCWAP/system01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/tools01.dbf
5152 115890 /opt/oracle/oradata/CMCCWAP/undotbs01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/users01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_1.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_2.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_3.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_4.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_5.dbf
32 22 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_1.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_2.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_3.dbf

PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
35 35 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_4.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_5.dbf
28 22 /opt/oracle/oradata/CMCCWAP/xdb01.dbf