如何诊断与IO相关的性能问题

论断与IO相关的性能问题的方法有:
statspack或awr报告中在top 5等待事件中与IO相关的等待事件,对数据库做sql跟踪显示主要的限制是IO等待事件,操作系统工具显示了很高的利用率或存储数据文件的磁盘正在饱和的使用

诊断IO问题的步骤
在数据库性能调整中一个关键的活动就是响应时间的分析,找出在数据库中时间花在哪了.时间对于性能调整是一个最重要的属性.用户是通过他们运行业务所经历的时间来进行感知的.

oracle数据库的响应时间使用以下的计算公式:
Response Time = Service Time + Wait Time

‘Service Time’就是用统计信息中的’CPU used by this session’来计算’Wait Time’就是等待事件的总时间

性能调优访问就是使用象awr和statspakc一样的工具来评估各种组件对整个响应时间的影响且直接对消耗时间最大的组件进行调整.

确定真正意义的IO等待事件
许多工具包括awr和statspack列出了最有效的等待事件.直到oracle9ir2 statspack包含一个叫”top 5 wait events”部分.
当面对所罗列的等待事件有时间很容易首先处理这些等待事件相关的问题而忘记了它们在整个响应时间中的影响.

在这种情况下’service time’即cpu使用率比’wait time’更有效,很有可能调查等待事件不会对响应时间有影响.因此总是应该拿top等待事件中的各等待事件所用的时间来与’cpu used by this session’的值进行比较并直接对最消耗时间的事件进行调整.

从oracle9ir2开始,”top 5 wait events’部分被重命名为”top 5 timed events” “service time’即”cpu used by this session’称作’cpu time’这意味着现在很容易精确地测量等待事件在整个响应时间中的影响并且能正确的对其进行调整.

误解等待事件的影响
下面的两个例子当在调查数据库性能问题时最重要的是查看’wait time’和’servie time’

例子1:在oracle9ir2以前的statspack
下面是statspack报告中”top 5 wait events’信息两个快照之间的间隔是46分钟
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
——————————————– ———— ———— ——-
direct path read 4,232 10,827 52.01
db file scattered read 6,105 6,264 30.09
direct path write 1,992 3,268 15.70
control file parallel write 893 198 .95
db file parallel write 40 131 .63
————————————————————-
基于上面的信息我们可能会立即查看造成’direct path read’和’db file scattered read’等待事件并试图对它们进行调整
.但是这种做法没有考虑’service time’.

下面的’service time’信息来自同一个statspack报告:
Statistic Total per Second per Trans
——————————— —————- ———— ————
CPU used by this session 358,806 130.5 12,372.6

下面对这些数字进行一些简单的计算:
‘Wait Time’ = 10,827/ 0.5201 = 20,817 cs
‘Service Time’ = 358,806 cs
‘Response Time’ = 358,806 + 20,817 = 379,623 cs

所以计算后各个组件占所有响应时间的百分比为:
CPU time = 94.52%
direct path read = 2.85%
db file scattered read = 1.65%
direct path write = 0.86%
control file parallel write = 0.05%
db file parallel write = 0.03%

现在很明显IO相关的等待事件不是真正影响整个响应时间(所有的IO等待事件的时间只占整个响应时间的6%)的原因.后续的调整应该直接对服务时间组件即CPU消耗.

例子2:在oracle10gr2以后的awr报告
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
—————————— ———— ———– —— —— ———-
DB CPU 33,615 82.0
db file sequential read 3,101,013 7,359 2 18.0 User I/O
log file sync 472,958 484 1 1.2 Commit
read by other session 46,134 291 6 .7 User I/O
db file parallel read 91,982 257 3 .6 User I/O

在awr中非常容易看出cpu在整个响应时间中占了很大一部分,因为cpu组件已经包含在’top 5 timed foreground events’中
.在上面的信息中我们可以看到等待事件的总时间占整个响应时间不到20%因此后续的调整应该直接对服务时间组件即cpu消耗进行.

一般处理IO问题的方法
在使用statspack或awr分析数据库响应时间后确定性能是由IO相关的等待事件所造成的,那么对于IO问题可能有以下解决方法,有些方法不受限于特定的等待事件,下面将解释说明每一种方法的概念和基本原理.

通过调整sql来减少数据库的IO请求:
一个数据库没有用户sql它将生成极少或没有IO.所有的IO最终都是通过数据库直接或间接的执行sql语句所产生的.这意味着可以通过控制单个sql语句的IO生成量来限制IO请求.这可以通过调整sql语句的执行计划来减少IO操作.通常的情况是数据库中只有少许的sql语句没有使用最佳的执行计划生成了太多的不必要的物理IO影响了数据库的整个性能.从oracle10g开始,addm可能自动识别对性能影响最大的sql语句然后sql调整指导可对其进行自动调整来减少对IO的消耗

通过调整实例参数来减少数据库的IO请求:
1.使用内存缓存来限制IO
通过使用较大的内存缓存象buffer cache,log buffer,各种排序区来限制IO请求的数量.增加buffer cache到一个合适的大小让
数据库进程执行更多的缓存访问(逻辑IO)来代替物理磁盘的访问(物理IO).在内存中使用大的排序区,可能会减少排序操作不得不使用临时表空间的可能性尽让排序在内存中完成.

2.调整多块IO的大小
单个多块IO操作的大小可以通过实例参数来控制.当有大量IO操作要执行时多块IO执行的速度要比更多的小IO操作要快.例如,传输100M的数据执行每次传输1M数据的操作100次要比执行每次传输100KB数据的操作1000次或每次传输10KB数据的操作10000次要快.在这个限制达到后,不同的大小将不再重要:传输1GB的数据执行100次每次传输10MB(如果操作系统允许的最大IO大 小)与一次传输1GB的数据几乎有同样的效率,这是因为IO服务请求所花的时间主要包括两部分:
IO setup time:对于不同的IO大小所花的时间基本上是恒定的且对于小的IO大小它的值趋于总的服务时间
IO transfer time:随着IO的大小而增加且对于小的IO大小通常小于IO setup time
可能通过db_file_multiblock_read_count参数来调整多块IO的大小

在操作系统层优化IO
这涉及到IO能力的使用比如象异步IO或使用带有高级功能的直接IO(跨过操作系统文件缓存)的文件系统.另一个可能的做法是提高每次传输IO最大大小

通过使用oracle asm(自动存储管理)来平衡数据库的IO
在oracle10g中asm被引入.它是一个文件系统且卷管理器被内建在数据库内核中.它能以并行方式跨过所有可用的磁盘设备来自动 进行负载平衡来阻止热点的产生和最大化性能,即使是使用快速变化的数据模式.它能阻止碎片因为这里从来不会为了回收空间来重新放置数据,数据将是平衡且条带化在所有的磁盘.

使用条带化,raid,san或nas来平衡数据库IO
这种方法依赖于存储技术象striping,raid,存储局域网(SAN)和网络连接存储(NAS),当在存储硬件上还有可用的磁盘吞吐量时来自动跨多个可用的物理磁盘来自动平等数据库IO来避免磁盘竞争和IO瓶颈.

通过手动将数据文件跨不同文件系统,控制器和物理设备来存储来重新分配数据库IO
这个方法用于缺少高级存储技术的情况下,当仍有磁盘吞吐量时再次分配数据库IO不使用单个磁盘或控制器达到饱和状态.它很难做到准确无误因此与之前的方法相比很少使用.

最重要的是记住有一些IO将总是存在于大多数数据库中的.在上述方法都已经考虑之后如果性能仍不能满足你可能考虑:
通过移走旧的数据来减少当前数据库的数据量
使用更多或更快的硬件

数据文件IO相关的等待事件
‘df file sequential read’
这是一个最常见的IO相关的等待事件,在大多数情况下是单块读取索引块或通过索引来访问表数据块但也可看作是对数据文件头块的读取.在早期的oracle版本中也可能是从磁盘中的排序段执行多块读在缓冲区缓存中组成连顺的缓存.

如果这个等待事件占了等待时间中的一大部分那么有以下方法可以进行调整:
从statspack或awr报告中的”SQL ordered by Reads”或v$sql视图中找出物理读取的top sql语句,然后对它们进行调整以减少它们的IO请求
如果索引范围扫描被调用,如果索引是非选择性的那么可能与必须要访问的数据块相比会有更多的数据块被访问.

如果索引分布很分散,那么我们将不得不访问更多的数据块因为每一个数据块中的索引数据很少,在这种情况下重建索引让索引数据存放在少理数据块中可以提高性能.

如果被使用的索引有大量的集族因子,那么为了得到每一个索引块会有更多的数据块要求被访问,可以按特定索引列对数据进行排序并按排序的结果重新创建该表来减小集族因子.例如一个表有a,b,c,d四个列且创建一个索引(b,d),那么我们可以使用
CREATE TABLE new AS SELECT * FROM old ORDER BY b,d语句来重建该表.

使用分区让每一个sql语句使用分区修剪功能来减少要被访问的索引数据块和表数据块.

如果没有执行计划很差的特定sql语句执行不必要的物理IO操作的话那么可能出现了以下情况:
特定数据文件的IO由于存储这些数据文件的磁盘上有过度的活动造成了服务缓慢.在这种情况下可以查看statspack或awr报告中的”File I/O Statistics”部分或v$filestat视图来找到哪些热点磁盘并通过手动移到数据文件到其它的存储上或通过使用条带 化,raid和其它自动执行IO负载平衡的技术来分散IO

从oracle9.2开始可以从v$segment_statistics视图中使用新的段统计信息来找到是哪一个段(表或索引)执行了最多的物理读取.
在找出具体的段之后可以对索引,表进行重建或分区来减少IO请求,如果使用statspack来生成”segment statistics”报告需要修 改收集统计的级别为7.
如果没有使用次优执行计划的sql且从所有磁盘执行请求的时间相似IO分布均匀那么设置一个大缓冲区缓存可能有帮助:
在oracle8i中可以通过逐步增加db_block_buffers的值来检测缓冲区缓存的撞击率直到不能再提高缓冲区缓存的撞击率为止.

在oracle9i中我们可以使用缓冲区缓存指导功能来调整缓冲区缓存的大小

在oracle10g中使用自动共享内存管理(asmm)来让数据库自动根据最近的工作负载来设置最佳的缓冲区缓存的大小

对于热点段可以使用多个缓冲池,将哪些热点索引和表放置在保留缓冲池中.

最后你可以考虑减少最频繁访问段中的数据(通过将旧的不需要的数据从数据库中移出)或将这些访问频繁的段移动到新的快速的磁盘上来减少它们IO请求的时间

‘db file scattered read’
这也是一个常见的等待事件,当数据库从执行多块读从磁盘上将数据块读取到缓冲区缓存中不连续的缓存中.这样的读一次能够读取的数据块个数是由db_file_multiblock_read_count参数值所决定的.这样的情况通常是发生在全表扫描和快速完全索引扫描.

如果这个等待事件占了总等待时间中的一大部分那么有以下方法可以进行调整:
找出哪个sql语句执行了全表扫描或快速完全索引扫描并对它们进行调整来确保这些扫描是必需的且不会造成使用一个次优的执行计划.从oracle9i开始新的v$sql_plan视图能帮助找出这些语句.

对于全表扫描:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation=’TABLE ACCESS’
and p.options=’FULL’
order by p.hash_value, t.piece;

对于快速完全索引扫描:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation=’INDEX’
and p.options=’FULL SCAN’
order by p.hash_value, t.piece;

在oracle8i中可以通过查询v$session_event视图来找出执行多块读取这个等待事件且对它们进行sql跟踪,另外可以查看物理读 取的top sql语句来查看是否它们的执行计划中有没有包含全表扫描或快速完全索引扫描.

在这种情况下当最佳执行计划执行多块读时可以通过设置实例参数db_file_multiblock_read_count来调整多块读的IO大小.因此
db_block_size x db_file_multiblock_read_count=系统的最大IO大小

正如前面所说的,从oracle10gr2开始db_file_multiblock_read_count初始化参数现在是自动调整当这个参数没有被显式设置时 使用缺省值.这个缺省值与能有效执行的最大IO大小有关.这个参数值依赖于平台且对于大多数平台的最大IO大小是1MB.因为这个参数是以数据块为单位的,它能设置成一个等于最大IO大小的值(它的值可以是有效执行最大IO大小的值除以标准块大小)

当使用全表扫描和快速完全索引扫描读取数据块时会将这些数据块放在缓冲区缓存替换列表中的最近最少使用端,有时使用多个缓冲区会有帮助象将段放在保留池中.

当分区修剪能在查询中限制扫描段分区的子集时分区也能被用来减少扫描数据的数量.

最后你可以考虑减少最频繁访问段中的数据(通过将旧的不需要的数据从数据库中移出)或将这些访问频繁的段移动到新的快速的磁盘上来减少它们IO请求的时间

‘db file parallel read’
这个等待事件出现在当oracle以并行读取从多个数据文件中读取数据块到不连续的缓存池时.在恢复操作或当执行缓存预取作为一种优化手段来代替执行多次单块读取是会发生.

如果这个等待事件占了总等待时间中的一大部分,关这个等待事件的优化方法可参考’db file sequential read’事件的优化方法

direct path reads and writes
‘direct path read’
‘direct path write’
‘direct path read(lob)’
‘direct path write(lob)’
这些等待事件当在磁盘和进程pga内存之间执行特定类型的多块IO时会发生.因此跳过了缓冲区缓存.IO可以被同步和异步执行.

它们会在以下情况下出现:
排序IO:当内存排序区已经筋疲力尽且正在使用临时表空间来执行排序操作时.
并行执行(查询和DML)
预取操作(缓冲预取)
直接路径加载操作
LOB段的IO(它们不会被缓存在缓冲区缓存中)

由于这些等待事件的等待时间都被记录(它不检测试执行IO的时间),它们会出现在statspack报告中的”top 5 wait/timed events”中但不能用来评估真实的影响.

调整方法:
当支持异步IO时尽可能的使用异步IO
在oracle8i中最小的IO请求数是通过设置db_file_direct_io_count参数来设置的因此
db_block_size x db_file_direct_io_count=系统的最大IO大小.
税
在oracle8i中这个缺省值是64个数据块.

在oracle9i中,使用bytes为单位的_db_file_direct_io_count来替换.缺省值是1MB但如果系统的max_io_size较小的话会降低这个值.

调整内存排序区来最小化磁盘IO排序操作,在oracle9i及以后的版本中使用自动sql执行内存管理,在8i中要调整各种排序区的大小.

对于lob段,将它们作为操作系统文件存储在文件系统上,缓冲区缓存能提供一些内存缓存.

通过查询v$session_event来识别执行直接IO的会话或通过v$sesstat来识别统计信息:
‘physical reads direct’,’physical reads direct(lob)’,’physical writes direct’,’physical writes direct(lob)’
并调整这些sql语句.

通过使用v$filestat或statspack或awr报告中的”file io statistics”部分来识别存储数据文件的磁盘是否有瓶颈并将其移到其它磁盘上.

控制文件相关的IO等待事件
这些等待事件是在对控制文件的一个或所有副本执行IO时出现,控制文件的访问频率是由日志文件切换和检查点来控制的.因此它只能通过间接地调整这些活动才能受到影响.
‘control file parallel write’
这个等待事件发生在服务器进程正在更新所有控制文件副本时会出现.如果这个等待事件很严重,检查控制文件所有副本的IO路径(控制器,物理磁盘)的瓶颈.可能的解决方法:
减少控制文件的数量来最小化确保在同一时间不会丢失所有控制文件副本.
在你的平台支持异步IO的情况下使用异步IO
移动控制文件副本到很少达到饱和状态的存储中

‘control file sequential read’和’control file single write’
这些等待事件在对单个控制文件执行驶IO时可能会出现.如果这些等待事件很严重找出这些等待事件是出现在哪些控制文件副本上并查看它们的确良IO路径是否已经达到饱和.

下面的查询可以用来找出哪个控制文件正被访问.当出现这些等待事件时可以运行:
select P1 from V$SESSION where EVENT like ‘control file%’ and STATE=’WAITING’;
或
select P1 from V$SESSION_WAIT where EVENT like ‘control file%’ and STATE=’WAITING’;
可能的解决方法:
移动有问题的控制文件副本到很少达到饱和状态的存储中
在你的平台支持异步IO的情况下使用异步IO

重做日志相关的IO等待事件
这里有许多的等待事件发生在重做日志活动期间且它们大多数都是与IO相关的.它们中最重要的两个是’log file sync’和
‘log file parallel write’.oracle前台进程等待’log file sync’而lgwr进行等待’log file parallel write’.

虽然在”top 5 wait/timed events”中经常看到’log file sync’等待事件,为了理解它们首先来看’log file parallel write’:

‘log file parallel write’
当lgwr后台进程从内存日志缓存中复制重做条目到磁盘上的当前重做日志组的成员日志文件中时会等待这个事件.如果支持异步IO的话,异步IO被用来保证以并行方式进写操作否则将会按顺序来对重做日志组中的成员日志文件进行写操作.

然而在这个等待完成之前lgwr进程不得不等到所有成员日志文件的所有IO操作完成.因此因为这个原因IO子系统的写入成员日志文件的速度决定了这个等待的时间长短.

为了减少这个等待事件的等待时间一种文学就是通过数据库来减少生成的重做日志的数量
利用unrecoverable/nologging选项
在保证在同一时间不会丢失所有重做日志成员的前提下减少重做日志组的成员
不要让表空间处于备份模式下超过其必要的时间
使用最小级别的supplemental logging来完成你所要请求完成的功能.例如logminer,logical standby或streams

另一种方法就是调整IO本身:
在存储上放置重做日志组成员因此对于每个成员并行写不会产生竞争
对于重做日志文件不要使用raid-5
对于重做日志文件使用裸设备
对于重做日志文件使用快速磁盘
如果启用归档请单独设置重做存储空间因此这样写当前重做日志组的成员时不会与归档进程读取当前组的成员产生竞争.

‘log file sync’
这个等待事件发生在oracle前台进程中当他们发出一个commit或rollback操作时正等待这个等待事件的应该完成部分,因为这个 等待事件包含了lgwr进程对于这个会话事务从重做日志缓存中复制重做条目到磁盘.所以前台进程正等待’log file sync’而lgwr 进程在这个时间正等待’log file parallel write’

理解是什么延迟’log file sync’是关键是比较’log file sync’和’log file parallel write’的平均等待时间:
如果它们平均等待时间几乎相同,那么重做日志IO是造成这个等待的主要原因
如果’log file parallel wirte’的平均等待时间非常小,那么造成这个等待的主要原因是当发出commit或rollback命令时重做日志机制的其它部分(与IO不相关),有时在重做日志闩锁上存在着闩锁竞争,可以通过’latch free’或’lgwr wait for redo copy’ 等待事件来证实这一点.

‘log file sequential read’和’log file single write’
这两个等待事件是与IO相关的如果在重做日志上存在着IO竞争那么它们会与’log file parallel write’一起出现

‘log file switch(checkpoint incomplete)’ 这个等待事件当检查点活动不能够迅速发生时会出现

‘log switch/archive’ and ‘log file switch(archiving needed)’
这些等待事件当归档启用时归档不能快速完成时会出现
调整这些等待事件的方法与前面所描述的相似.