Oracle IO问题解析(5)

日期： 2009-04-07 14:57:06

分享到

3 Oracle中的IO问题及其解决思路

对于负载偏重点不同，我们可以简单的将数据库系统分为CPU负载系统（CPU Bound System）和IO负载系统（IO Bound System）。顾名思义，CPU负载系统的资源瓶颈在于CPU，而IO负载系统的瓶颈在于磁盘IO。

我们可以通过操作系统的一些命令来确认一个系统是否是存在IO负载。在UNIX下，可以使用"iostat"或者"sar -d"来看系统的IO情况；在windows下，可以通过系统的性能监视器查看，但由于性能监控器中看到的IO是静态的IO总量信息，并不直观，因此也可以用本站的TopShow工具来查看实时的IO信息。

在UNIX系统下，发现CPU IDEL很低并不一定代表这是一个CPU负载系统。一个IO负载系统在表面上看CPU的IDEL值也可能很低：
oracle@db01:/export/home/oracle> sar -u 1 10
 
HP-UX hkhpdv45 B.11.23 U ia64    10/24/07
 
09:43:05    %usr    %sys    %wio   %idle
09:43:06      43      25      30       1
09:43:07      44      36      19       1
09:43:08      23      27      44       6
09:43:09      12      37      50       1
09:43:10      10      36      51       3
09:43:11      15      34      42       9
09:43:12      18      36      44       3
09:43:13      17      35      46       2
09:43:14      12      32      52       4
09:43:15      12      31      56       1
                                        
Average       21      33      43       3
我们可以注意到，实际上WIO是引起CPU IDEL过低的主要原因。WIO是当一个进程需要运行或已经运行后，因为需要等待IO事件而被阻塞了。事实上CPU是处于IDEL状态（在某些系统中，已经将WIO取消并归为IDEL），真正的原因是系统中存在IO瓶颈。

通过iostat或者sar -d我们可以找出存在IO瓶颈的磁盘设备，如果该磁盘设备是用于Oracle 数据库存储文件的，我们可以判断出是数据库存在IO问题。在windows下，可以通过TopShow来找出哪个进程正在进行大量IO传输，如果是Oracle进程，也可以判断为是数据库存在IO问题。

确认系统存在IO问题后，我们就需要定位到底是什么引起的IO问题，该采取什么措施来解决问题。根据我们前面的介绍，Oracle中存在各种IO，要定位IO，最好的工具是statspack（在10g以后，可以用AWR）。通过statspack report的Top 5 Events，我们可以看到对系统系能影响最大的5个等待event，而不同的IO问题会对应不同Event，所以，我们可以根据这些event采取不同的措施来解决IO问题。下面是一个典型的IO负载系统的Top 5 Event：
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read                        70,575,969     344,200    53.34
db file scattered read                         11,240,748     163,242    25.30
log file sync                                     657,241      36,363     5.64
CPU time                                                       35,290     5.47
log file parallel write                           833,799      20,767     3.22
可以看到，前两个时间“db file sequential read”和“db file scattered read”分别占了总等待时间的53.34%和25.30%，而我们前面提到这两个事件分别是由索引扫面和全表扫面（或快速索引扫面）引起的，因此，能解决索引扫面问题和全表扫面问题就能解决这个系统的IO瓶颈。

IO问题到底对CPU有多大影响呢？我们用以上例子中的数据分析一下。从等待时间统计数据中，我们看到的是时间在总等待时间中所占的比例。而系统的“总响应时间 ”= “等待时间 ”+ “CPU工作时间”（注意，上面Top 5事件中的“CPU Time”不是指CPU的工作时间，而是指CPU的等待时间）。“CPU工作时间”的数据我们可以在“Instance Activities Stats for DB”这一分类统计数据中找到：
Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session                  17,136,868          396.7         15.5
先计算出“总等待时间” = 344,200 * 100% / 53.34% = 645,294s

“总响应时间” = “总等待时间” + “CPU工作时间” = 645,294 + 17,136,868 = 17,782,162s

我们可以算出“CPU工作时间”、“db file sequential read”和“db file scattered read”分别在“总响应时间中所占的比例为：

CPU工作时间 = 17,136,868 / 17,782,162 = 96.4%

“db file sequential read” = 344,200 / 17,782,162 = 1.9%

“db file scattered read” = 163,242 / 17,782,162 = 0.9%

可见，IO事件所引起的等待时间在总响应时间所占比例并不大。因此，我们在做系统优化之前先分析系统是CPU负载系统还是IO负载系统对于我们的优化方向和最终的优化效果起很大的作用。

以下事件是可能由IO问题引起的等待事件，在IO负载系统中，我们要特别关注这些事件：

与数据文件相关的IO事件

'db file sequential read'

'db file scattered read'

'db file parallel read'

'direct path read'

'direct path write'

'direct path read (lob)'

'direct path write (lob)'

与控制文件相关的IO事件

'control file parallel write'

'control file sequential read'

'control file single write'

与Redo日志相关的IO事件

'log file parallel write'

'log file sync'

'log file sequential read'

'log file single write'

'switch logfile command'

'log file switch completion'

'log file switch (clearing log file)'

'log file switch (checkpoint incomplete)'

'log switch/archive'

'log file switch (archiving needed)'

与Buffer Cache相关的IO事件

'db file parallel write'

'db file single write'

'write complete waits'

'free buffer waits'

下面我们就分别介绍如何解决IO问题。

3.1     IO调优的思路及常用手段

通过对statspack或者awr报告的分析，我们可以得知是那些IO相关事件引起的IO问题。针对不同的事件，可以采取不同的分析、处理方法。而有一些通用的方法并不是针对特定的事件的。我们这里先介绍一下这些方法。

3.1.1      通过SQL调优来减少IO请求

一个没有任何用户SQL的数据库几乎不产生任何IO。基本上数据库所有的IO都是直接或间接由用户提交的SQL所导致的。这意味着我们可以通过控制单个SQL产生的IO来降低数据库总的IO请求。而通过SQL调优来降低SQL查询计划中的IO操作次数则是降低SQL产生IO的最好方法。数据库的性能问题通常是由少数几个SQL语句所导致的，它们产生了大量IO导致了整个数据库的性能下降。优化几条问题语句往往就能解决整个数据库的IO性能问题。

从Oracle 10g开始，ADDM能够自动检测出问题语句，同时，再通过查询优化建议器能够自动优化语句并降低它们对IO的消耗。关于ADDM和查询优化建议器可以参考文章《Oracle 10G 新特性——ADDM和查询优化建议器》。

3.1.2      通过调整实例参数来减少IO请求

在这种方法中，主要有两种途径来实现对IO的优化。

使用内存缓存来减少IO

通过一些内存缓存，如Buffer Cache、Log Buffer、Sort Area，可以降低数据库对IO的请求。

当Buffer Cache被增大到一定大小时，绝大多数结果可以直接从缓存中获取到，而无需从磁盘上读取了。而在进行排序操作时，如果Sort Area足够大，排序过程中产生的临时数据可以直接放在内存中，而无需占用临时表空间了。

调整multiblock IO（多数据块IO）的大小

控制Multiblock IO的参数叫DB_FILE_MULTIBLOCK_READ_COUNT，它控制在多数据块读时一次读入数据块的次数。适当增加这个参数大小，能够提高多数据块操作（如全表扫描）的IO效率。例如，读取100M数据，如果每次读取1M一共读取100次的效率就比每次读取100K一共读取1000次更快。但是这个数字达到一定大小后，再增加就作用不大了：每次10M一共读100次来读取1G的数据的效率和单独一次读取1G数据的效率是没有多大区别的。这是因为IO效率受到2个因素的影响：IO建立时间和IO传输时间。

IO建立时间对于不同IO大小来说都是相同的，它决定了对小IO的总的IO时间，增大Multiblock IO大小可以减少IO建立时间；

IO传输时间与IO大小是成正比的，在小IO时，IO传输时间一般比IO建立时间少，但对于大IO操作来说，IO传输时间决定了总的IO时间。因此Multiblock IO大小增大到一定大小时，它对总的IO时间影响就不大了。

3.1.3      在操作系统层面优化IO

如我们前面所介绍的，利用一些操作系统提供的提升IO性能的特性，如文件系统的异步IO、Direct IO等来优化数据库系统的IO性能。另外一种方法就是增加每次传输的最大IO大小的限制（大多数Unix系统中，由参数max_io_size控制）。

3.1.4      通过Oracle ASM实现对IO的负载均衡

ASM（Automatic Storage Manager自动存储管理）是从Oracle 10g开始引入的。它是一个建立在数据库内核中的文件系统和卷管理器。它能自动将IO负载均衡到所有可用的磁盘启动器上去，一避免“热区”。ASM能防止碎片，因此无需重建数据来回收空间。数据被均衡分布到所有硬盘上。

3.1.5      通过条带化、RAID、SAN或者NAS实现对IO的负载均衡

这个方法通过一些成熟的存储技术，如条带化、RAID、SAN和NAS，来将数据库IO分布到多个可用的物理磁盘实现负载均衡，以避免在还存在空闲可用磁盘时出现的磁盘争用和IO瓶颈问题。

关于这几种存储技术，我们文章的前面部分都有做介绍。

3.1.6      通过手工布置数据库文件到不同的文件系统、控制器和物理设备上来重新分布数据库IO

当数据库系统中缺乏以上各种存储技术手段时，我们可以考虑使用这种方式。这样做的目的是使数据库的IO得到均匀分布，从而避免在还有空闲磁盘时出现磁盘争用和IO瓶颈问题。当然这种手工分布IO方法是无法达到以上的自动分布IO的效果的。

3.1.7      其他手段

系统中总会存在一些IO是无法消除或降低的。如果采用以上手段还不能满足IO性能要求的话，可以考虑这两种方法：

将老数据移除你的生产数据库（Housekeep）

采用更多、更快的硬件

上一篇：Oracle IO问题解析(6)

下一篇：Oracle IO问题解析(4)

本类中的所有文章