Oracle DBA 应知应会 -- 定位IO性能问题的一些方法

如何确定你的系统的主要问题是IO问题，并进一步定位IO问题的根本原因是解决Oracle IO性能问题的关键。IO性能不佳，可能是多方面的问题，进行优化的第一步工作就是确定关键的性能瓶颈。
影响Oracle数据库IO性能的问题覆盖面十分广，根据笔者多年从事Oracle数据库管理的经验，以下几个方面是影响数据库IO性能的主要问题：
l 存储性能瓶颈：控制器不足、CACHE偏小，CACHE设置不合理、IO通道容量不足等
l 磁盘性能瓶颈：磁盘数量过少、使用了速度比较低的磁盘等
l 使用了不合理的RAID模式
l 在使用RAID的情况下，存在IO热点，多个热点文件使用同一个磁盘
l 异步IO配置不正确
l 数据库各种缓冲区设置不合理，缓冲命中率过低
l PGA的各种缓存设置过小（对于9i使用自动管理模式情况下，PGA设置过小），导致大量临时表空间操作
l REDO LOG存在性能瓶颈
l REDO BUFFER设置不合理
l 存在热点数据
l 表空间碎片严重
l 表和索引的存储参数不合理
l 行迁移比较严重
l 存在大量大表扫描的SQL
l SQL执行选择了不好的执行计划
当系统出现IO问题的时候,数据库管理员最大的挑战是如何尽快找到问题的最根本的原因,IO问题的调整是十分复杂的,在没有找到根本原因前进行调整往往无法达到最终的优化目标。需要注意的是IO问题往往和大型的sql语句有关.如果某个系统突然发生IO性能问题.第一步需要排除一切IO以外的问题。
确定IO性能瓶颈的存在,并定位存在IO性能瓶颈的设备是解决IO性能问题的第一步.使用操作系统的监控工具可以实时监控IO的情况。第一种方法是使用vmstat工具,使用该工具,可以查看b列的值,如果该值比较大,那么说明等待IO的进程比较多,IO可能存在问题。如下列：
$vmstat 1 10
procs memory
r b w avm free
2 12 0 14572705 92752
2 12 0 14572705 93548
2 12 0 14572705 92910
2 12 0 14572705 93467
2 12 0 14572705 93546
2 12 0 14572705 93864
2 12 0 14572705 94557
2 12 0 14572705 93952
2 12 0 14572705 94017
2 12 0 14572705 93047

如果上述命令发现b比较大，那么说明可能存在IO等待的现象，通过sar命令或者iostat命令可以进一步确认。如果sar命令监控的时候发现wio比较大(比如超过40，这个值是个经验值，根据不同的系统，这个值可以调整),那么基本上可以确定存在IO性能瓶颈。如下：
$sar 1 10
HP-UX cuyn16 B.11.11 U 9000/800 11/01/05
15:01:44 %usr %sys %wio %idle
15:01:45 16 3 57 24
15:01:46 15 2 59 24
15:01:47 21 3 57 19
15:01:48 20 2 63 16
15:01:49 17 2 67 14
15:01:50 12 1 75 12
15:01:51 16 2 75 7
15:01:52 10 1 84 5
15:01:53 18 2 79 1
15:01:54 25 3 65 6
如果发现wio值长时间高于40那么说明IO等待比较严重。此时可以通过sar-d命令来监控，到底哪些IO设备存在性能问题。如果发现某个设备的繁忙度长时间超过90%,那就说明该设备比较繁忙,如果该设备明avserv比较大或者比其它设备高很多.那么就说明该设备存在性能问题。

比如下面的例子：
15:02:01 device %busy avque r+w/s blks/s avwait avserv
Average c0t0d0 2.00 0.50 6 27 3.62 6.03
Average c3t8d0 1.10 0.50 4 16 3.23 5.69
Average c55t0d5 99.40 0.50 18 73 5.41 54.50
Average c55t1d0 4.20 0.50 5 15 5.39 8.49
Average c55t1d1 79.52 0.76 24 810 9.09 81.99
Average c55t10d7 68.33 0.52 23 2909 5.60 72.40
Average c55t11d0 31.07 1.14 25 1630 10.95 28.05
Average c55t11d2 16.98 0.51 22 3075 5.24 13.39
Average c55t11d3 71.83 2.59 26 1643 42.18 82.78
Average c55t11d5 76.12 0.50 23 3012 5.58 76.47
Average c55t11d6 30.57 1.02 26 1637 10.85 30.59
Average c55t12d0 21.48 0.50 20 2826 5.12 19.55
Average c55t12d1 80.72 2.74 29 1880 42.78 84.38
Average c55t12d3 70.03 0.52 23 2887 5.83 66.85
Average c55t12d4 23.58 1.74 27 1758 16.11 25.72
Average c55t14d0 80.62 0.50 66 975 4.83 12.38
Average c56t14d2 5.89 0.50 6 75 5.67 10.38
Average c55t11d1 80.22 0.50 22 1547 4.85 75.33
Average c55t11d4 24.08 0.50 26 1829 5.04 11.90
Average c55t11d7 82.72 0.50 23 1586 5.27 76.46
Average c55t12d2 24.18 0.50 18 1304 4.79 16.26
Average c55t12d5 76.02 0.50 20 1403 4.91 74.11
Average c55t14d1 100.00 54.57 104 6630 1315.98 71.54
Average c55t13d1 77.72 0.55 19 297 5.79 80.19
Average c55t1d4 0.30 0.50 1 10 7.02 1.50
Average c55t2d0 0.10 0.50 0 3 3.59 6.43
Average c55t6d2 0.30 0.50 0 6 5.17 7.09
Average c55t0d7 0.70 0.50 0 2 1.33 69.67
Average c55t12d6 0.10 0.50 0 0 0.98 9.41
Average c55t13d0 0.50 0.50 0 4 7.46 19.99

从上面的数据可以看出,部分设备(比如c55t14d1)的等待队列比较长,并且AVWAIT+AVSERV的时间比较长.说明该设备存在性能瓶颈.而大部分逻辑卷的AVWAIT+AVSERV还比较正常,这说明存储系统并不存在普遍性的性能瓶颈,性能瓶颈主要集中在热点盘组上.

通过oracle的statspack工具也可以检查系统的IO问题,如果系统的性能不佳,并且比时从报告中看到的seqnencial read等待事件是系统等待事件中排在前几位的事件,占系统总等待时间的比例比较高,那么系统很可能存在lO性能问题。可以通过检查文件lO情况来进一步确认并找出存在性能问题的设备。方法是通过检查文件IO中的平均读响应时间,如果某个文件的平均读响应时间超过20毫秒,那么说明该文件所属的文件系统可能存在性能问题。应该注意的是,20毫秒是一个相对的参数,在不同的应用环境下可能选择不同的参考参数.通过比对操作系统的情况,数据库管理员应该很快就能确定你所管理的系统的平均读响的时间和操作系统lO情况的对应关系。
检查读平均响应时间的时候还要注意几个问题。笫一个问题是在报告时间区域内的IO量,如果某个文件在报告时间区间内的IO数量很小,那么此平均响应时间可能缺乏代表性,可以通过检查存放在相同设备上的其它文件来进一步确认。另外一种情况是平均每次读的数据量比较多(每次读的块数比较多),那么略高的平均读响应时间也是正常的。下面的数据就是从IO存在问题的数据库上取下的Statspack数据,可以看出
Top 5 Timed Events

Event Waits Time (s) Ela Time   
-------------------------------------------- ------------ ----------- --------   
db file sequential read 661,802 45,404 60.79   
SQL*Net more data from dblink 3,180,371 7,894 10.57   
CPU time 5,077 6.80   
db file scattered read 56,959 3,846 5.15   
buffer busy waits 42,376 2,541 3.40   
-------------------------------------------------------------   
Db file sequential read是等待事件的第一位事件,并且占整个等待事件的60%以上.这是一个典型的IO存在性能瓶颈的例子,通过Statspack报告文件IO性能分析数据中,可以进一步检查到底哪些文件出现了问题:   
INDEX_SPACE_OTHER /dev/vg10xp/rls_2g_vol05   
9,171 2 52.2 1.0 7,911   
/dev/vg10xp/rls_2g_vol06   
8,016 2 22.8 1.0 8,292   
/dev/vg10xp/rls_2g_vol07   
7,567 2 9.8 1.0 8,058   
/dev/vg10xp/rls_2g_vol08   
5,456 1 46.7 1.0 6,180   
/dev/vg10xp/rls_2g_vol09   
5,925 2 57.3 1.0 6,265   
/dev/vg10xp/rls_2g_vol10   
10,426 3 147.7 1.0 6,867   
/dev/vg10xp/rls_2g_vol11   
6,071 2 130.0 1.0 5,638   
/dev/vg10xp/rls_2g_vol12   
1,738 0 213.9 1.0 3   
/dev/vg10xp/rls_2g_vol13   
3,334 1 114.4 1.0 3,596   
/dev/vg10xp/rls_2g_vol14   
10,758 3 213.6 1.0 13,657   
/dev/vg10xp/rls_2g_vol15   
15,624 4 226.9 1.0 15,736   
/dev/vg10xp/rls_2g_vol16   
14,421 4 206.2 1.0 17,136   
/dev/vg10xp/rls_2g_vol17   
13,677 4 229.9 1.0 13,819   
/dev/vg10xp/rls_2g_vol18   
10,554 3 212.6 1.0 11,828   
/dev/vg10xp/rls_2g_vol19   
7,510 2 200.6 1.0 9,965   
/dev/vg10xp/rls_2g_vol20   
6,134 2 198.3 1.0 7,783   
/dev/vg10xp/rls_2g_vol21   
5,753 2 217.6 1.0 839   
/dev/vg10xp/rls_2g_vol22   
4,908 1 202.3 1.0 575   
/dev/vg10xp/rls_2g_vol23   
16,341 4 236.4 1.0 6,470   
可以看出/dev/vg10xp上的部分文件的平均读响应时间超过了200毫秒,存在严重的性能问题.通过验证,/dev/vg10xp是c55t14d1上的逻辑卷.