Oracle常见等待事件说明及处理

frankfan126

Oracle常见等待事件说明

Oracle的等待事件是衡量Oracle运行状况的重要依据及指标。等待事件的概念是在Oracle7.0.1.2中引入的，大致有100个等待事件。在Oracle 8.0中这个数目增加到了大约150个，在Oracle8i中大约有200个事件,在Oracle9i中大约有360个等待事件。主要有两种类别的等待事件，即空闲(idle)等待事件和非空闲(non-idle)等待事件。
 
　　空闲事件指Oracle正等待某种工作,在诊断和优化数据库的时候,我们不用过多注意这部分事件。
 
　　常见的空闲事件有:
 
　　• dispatcher timer
 
　　• lock element cleanup
 
　　• Null event
 
　　• parallel query dequeue wait
 
　　• parallel query idle wait - Slaves
 
　　• pipe get
 
　　• PL/SQL lock timer
 
　　• pmon timer- pmon
 
　　• rdbms ipc message
 
　　• slave wait
 
　　• smon timer
 
　　• SQL*Net break/reset to client
 
　　• SQL*Net message from client
 
　　• SQL*Net message to client
 
　　• SQL*Net more data to client
 
　　• virtual circuit status
 
　　• client message
 
　　非空闲等待事件专门针对Oracle的活动,指数据库任务或应用运行过程中发生的等待，这些等待事件是我们在调整数据库的时候应该关注与研究的。
 
　　一些常见的非空闲等待事件有:
 
　　• db file scattered read
 
　　• db file sequential read
 
　　• buffer busy waits
 
　　• free buffer waits
 
　　• enqueue
 
　　• latch free
 
　　• log file parallel write
 
　　• log file sync
 
　　1. db file scattered read-DB 文件分散读取
 
　　这种情况通常显示与全表扫描相关的等待。当数据库进行全表扫时，基于性能的考虑，数据会分散(scattered)读入Buffer Cache。如果这个等待事件比较显著，可能说明对于某些全表扫描的表，没有创建索引或者没有创建合适的索引，我们可能需要检查这些数据表已确定是否进行了正确的设置。
 
　　然而这个等待事件不一定意味着性能低下，在某些条件下Oracle 会主动使用全表扫描来替换索引扫描以提高性能，这和访问的数据量有关，在CBO 下Oracle 会进行更为智能的选择，在RBO 下Oracle 更倾向于使用索引。
 
　　因为全表扫描被置于LRU(Least Recently Used，最近最少适用)列表的冷端(cold end)，对于频繁访问的较小的数据表，可以选择把他们Cache 到内存中，以避免反复读取。
 
　　当这个等待事件比较显著时，可以结合v$session_longops 动态性能视图来进行诊断，该视图中记录了长时间(运行时间超过6 秒的)运行的事物，可能很多是全表扫描操作(不管怎样，这部分信息都是值得我们注意的)。
2. db file sequential read-DB 文件顺序读取。
 
　　这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，可能没有正确的使用驱动表;或者可能说明不加选择地进行索引。
 
　　在大多数情况下我们说，通过索引可以更为快速的获取记录，所以对于一个编码规范、调整良好的数据库，这个等待很大是很正常的。但是在很多情况下，使用索引并不是最佳的选择，比如读取较大表中大量的数据，全表扫描可能会明显快于索引扫描，所以在开发中我们就应该注意，对于这样的查询应该进行避免使用索引扫描。
 
　　3. Free Buffer-释放缓冲区
 
　　这个等待事件表明系统正在等待内存中的可用空间，这说明当前Buffer 中已经没有Free 的内存空间。如果应用设计良好，SQL 书写规范，充分绑定变量，那这种等待可能说明Buffer Cache 设置的偏小，你可能需要增大DB_BUFFER_CACHE。
 
　　Free Buffer 等待可能说明DBWR 的写出速度不够，或者磁盘存在严重的竞争，可以需要考虑增加检查点、使用更多的DBWR 进程，或者增加物理磁盘的数量,分散负载，平衡IO。
 
　　4. Buffer Busy-缓冲区忙
 
　　该等待事件表示正在等待一个以unshareable方式使用的缓冲区，或者表示当前正在被读入buffer cache。一般来说Buffer Busy Wait不应大于1%。检查缓冲等待统计部分(或V$WAITSTAT)，看一下等待是否位于段头(Segment Header)。如果是，可以考虑增加自由列表(freelist，对于Oracle8i DMT)或者增加freelist groups(在很多时候这个调整是立竿见影的，在8.1.6之前，这个freelists参数不能动态修改;在8.1.6及以后版本，动态修改feelists需要设置COMPATIBLE至少为8.1.6).
 
　　如果这一等待位于undo header，可以通过增加回滚段(rollback segment)来解决缓冲区的问题。如果等待位于undo block上，我们可能需要检查相关应用，适当减少大规模的一致性读取，或者降低一致性读取(consistent read)的表中的数据密度或者增大DB_CACHE_SIZE。
 
　　如果等待处于data block，可以考虑将频繁并发访问的表或数据移到另一数据块或者进行更大范围的分布(可以增加pctfree值 ，扩大数据分布，减少竞争)，以避开这个"热点"数据块，或者可以考虑增加表中的自由列表或使用本地化管理的表空间(Locally Managed Tablespaces)。
 
　　如果等待处于索引块，应该考虑重建索引、分割索引或使用反向键索引。为了防止与数据块相关的缓冲忙等待，也可以使用较小的块:在这种情况下，单个块中的记录就较少，所以这个块就不是那么"繁忙";或者可以设置更大的pctfree,使数据扩大物理分布，减少记录间的热点竞争。
 
　　在执行DML (insert/update/ delete)时，Oracle向数据块中写入信息，对于多事务并发访问的数据表，关于ITL的竞争和等待可能出现，为了减少这个等待，可以增加initrans，使用多个ITL槽。在Oracle9i 中，引入了一个新概念:ASSM(Segment Space Management Auto)。通过这个新特性Oracle 使用位图来管理空间使用。
 
　　ASSM 结合LMT 彻底改变了Oracle 的存储机制，位图freelist 能够减轻缓冲区忙等待(buffer busy wait)，这个问题在Oracle9i 以前的版本里曾是一个严重的问题。
 
　　Oracle 宣称ASSM 显著地提高了DML 并发操作的性能，因为(同一个)位图的不同部分可以被同时使用，这样就消除了寻找剩余空间的串行化。根据Oracle 的测试结果，使用位图freelist 会消除所有分段头部(对资源)的争夺，还能获得超快的并发插入操作。在Oracle9i 之中，Buffer Busy wait 不再常见!
 5. latch free-latch 释放

　　latch是一种低级排队机制，用于保护SGA中共享内存结构。latch就像是一种快速地被获取和释放的内存锁。用于防止共享内存结构被多个用户同时访问。如果latch不可用，就会记录latch释放失败(latch free miss )。有两种与闩有关的类型:

　　■ 立刻。

　　■ 可以等待。

　　假如一个进程试图在立刻模式下获得闩，而该闩已经被另外一个进程所持有，如果该闩不能立可用的话，那么该进程就不会为获得该闩而等待。它将继续执行另一个操作。

　　大多数latch问题都与以下操作相关:

　　没有很好的是用绑定变量(library cache latch)、重作生成问题(redo allocation latch)、缓冲存储竞争问题(cache buffers LRU chain)，以及buffer cache中的存在"热点"块(cache buffers chain)。

　　通常我们说，如果想设计一个失败的系统，不考虑绑定变量，这一个条件就够了，对于异构性强的系统，不使用绑定变量的后果是极其严重的。

　　另外也有一些latch等待与bug有关，应当关注Metalink相关bug的公布及补丁的发布。当latch miss ratios大于0.5%时，就应当研究这一问题。

　　Oracle的latch机制是竞争，其处理类似于网络里的CSMA/CD，所有用户进程争夺latch， 对于愿意等待类型(willing-to-wait)的latch,如果一个进程在第一次尝试中没有获得latch,那么它会等待并且再尝试一次,如果经过_spin_count次争夺不能获得latch, 然后该进程转入睡眠状态，持续一段指定长度的时间，然后再次醒来，按顺序重复以前的步骤.在8i/9i中默认值是_spin_count=2000。

　　如果SQL语句不能调整，在8.1.6版本以上，Oracle提供了一个新的初始化参数: CURSOR_SHARING可以通过设置CURSOR_SHARING = force 在服务器端强制绑定变量。设置该参数可能会带来一定的副作用，对于Java的程序，有相关的bug，具体应用应该关注Metalink的bug公告。

　　7. Log Buffer Space-日志缓冲空间

　　当你将日志缓冲(log buffer)产生重做日志的速度比LGWR 的写出速度快，或者是当日志切换(log switch)太慢时，就会发生这种等待。这个等待出现时，通常表明redo log buffer 过小，为解决这个问题，可以考虑增大日志文件的大小，或者增加日志缓冲器的大小。

　　另外一个可能的原因是磁盘I/O 存在瓶颈，可以考虑使用写入速度更快的磁盘。在允许的条件下设置可以考虑使用裸设备来存放日志文件，提高写入效率。在一般的系统中，最低的标准是，不要把日志文件和数据文件存放在一起，因为通常日志文件只写不读，分离存放可以获得性能提升。
8. Log File Switch-日志文件切换
 
　　当这个等待出现时，表示所有的提交(commit)的请求都需要等待"日志文件切换"的完成。
 
　　Log file Switch 主要包含两个子事件:
 
　　log file switch (archiving needed)
 
　　log file switch (checkpoint incomplete)
 
　　log file switch (archiving needed)
 
　　这个等待事件出现时通常是因为日志组循环写满以后，第一个日志归档尚未完成，出现该等待。出现该等待，可能表示io 存在问题。解决办法:
 
　　可以考虑增大日志文件和增加日志组
 
　　移动归档文件到快速磁盘
 
　　调整log_archive_max_processes .
 
　　log file switch (checkpoint incomplete)-日志切换(检查点未完成)
 
　　当你的日志组都写完以后，LGWR 试图写第一个log file，如果这时数据库没有完成写出记录在第一个log file 中的dirty 块时(例如第一个检查点未完成)，该等待事件出现。
 
　　该等待事件通常表示你的DBWR 写出速度太慢或者IO 存在问题。
 
　　为解决该问题，你可能需要考虑增加额外的DBWR 或者增加你的日志组或日志文件大小。
 
　　9. log file sync-日志文件同步
 
　　当一个用户提交或回滚数据时，LGWR 将会话期的重做由日志缓冲器写入到重做日志中。日志文件同步过程必须等待这一过程成功完成。为了减少这种等待事件，可以尝试一次提交更多的记录(频繁的提交会带来更多的系统开销)。将重做日志置于较快的磁盘上，或者交替使用不同物理磁盘上的重做日志，以降低归档对LGWR的影响。
 
　　对于软RAID，一般来说不要使用RAID 5，RAID5 对于频繁写入得系统会带来较大的性能损失，可以考虑使用文件系统直接输入/输出，或者使用裸设备(raw device)，这样可以获得写入的性能提高。
 
　　10. log file single write该事件仅与写日志文件头块相关，通常发生在增加新的组成员和增进序列号时。
 
　　头块写单个进行，因为头块的部分信息是文件号，每个文件不同。更新日志文件头这个操作在后台完成，一般很少出现等待，无需太多关注。
Rodo Log性能调整目标：
 
在能够影响Oracle性能的诸多因素中，Redo Log相关的因素从某种程度上可以说是最为重要同时也是最值得关注的。因为在一个OLTP系统中Oracle通过各种技术以及优良的设计，尽量做到将大部分操作在内存中完成，以便最大程度的提升性能。因此在Oracle的诸多后台进程以及用户进程的大部分操作都是内存操作，而且这些操作会通过延迟写入技术尽可能的将磁盘I/O操作滞后。但是在这些操作中却有某些例外，其中最明显的就是针对Redo Log的操作。
 
在Oracle中针对Redo Log的操作主要由LGWR进程完成，这个进程可以说是Oracle所有后台进程中最繁忙的进程，而且这个进程可能要频繁的进行I/O操作，这是因为Oracle出于数据安全的考虑必须保证联机在线重做日志可靠的写入日志文件，以便在发生崩溃时能够有效恢复数据，而真正的数据可能会等一些时间延迟写入数据文件。这种特点在Oracle的各个后台进程中显得有些独树一帜。另外LGWR全局唯一，即一个实例只能有一个活动的LGWR进程，由于要进行频繁的I/O操作可想而知是很容易造成LGWR进程竞争的。由于LGWR在Oracle实例结构设计中的特殊地位，一旦出现LGWR性能瓶颈，那么对整个系统的性能影响将会是极为严重的，同时对数据安全也是一个潜在的威胁。
 
因此作为Oracle日常的数据库管理，我们要给与这部分相当的关注，尽早发现问题，尽早作出调整。调整的目标就是要做到Log_Buffer大小适中（不要过大，也不能太小），要满足用户进程的使用需要，每当系统负载有一个明显的增加时，就应该考虑调整它的大小。比如因为业务拓展当前系统固定用户数量从1万人猛增到3万人，那么就应该对Log_Buffer大小给与关注。另外就是要做到日志文件的大小适中，日志组的日志文件数量合适，不能影响LGWR写日志文件的性能，不能造成日志文件间的写入竞争，不能在日志切换归档发生时引发磁盘竞争等等。
 
二、           监控与问题排查：
 
        在进行Redo Log问题监控时，主要关注两个方面：日志缓冲区空间使用的等待情况和日志缓冲区数据槽的分配情况。通过这两方面的监控并配合一些问题排查手段，通常可以发现大量问题。
 
    (1)、日志缓冲区空间使用的等待情况：
 
        可以通过查询v$session_wait来监控日志缓冲区空间使用的等待情况，通过如下SQL语句进行查询：
 
    select sid,event,seconds_in_wait,state
 
from v$session_wait
 
where event='log buffer space%';
 
以上的查询中可以通过观察seconds_in_wait的数值来分析问题，这个数值可以显示如下问题：日志切换缓慢引发的等待、LGWR写入缓慢引发的等待、日志文件写入引起的磁盘竞争引发的等待。
 
这些等待的发生可能是由于如下问题引起的：
 
1、日志文件写入时存在磁盘竞争：
 
这种情况多见于日志切换发生时，由于日志文件组的规划不当，或者存放日志文件的磁盘写入速度缓慢，或者是因为磁盘RADI类型不当都会引发这个问题，如果怀疑村在这些情况，可以通过如下语句进行监控：
 
select event,total_waits,time_waited,average_wait
 
from v$system_event
 
where event like 'log file switch completion%';
 
可以通过观察total_waits,time_waited,average_wait数值来分析问题，如果这些值过高（注意何谓“过高”，不同系统考量标准不一样，要具体分析），那么说明存在以上问题。此时可以通过如下措施解决：
 
.将同一日志文件组的各个成员分配到不同的磁盘上，进而减少日志写入以及日志切换和日志归档时引发的竞争；
 
.将日志文件尽可能存放在快速的磁盘上；
 
.要合理选择RADI类型对磁盘进行条带化，通常不要选择RADI5来作为日志文件磁盘的RADI类型，通常推荐使用RADI10；
 
.可以增加REDO LOG文件大小，来延缓日志切换，下面是一个增加日志文件大小的方法；
 
假如原来有3个小的redo log file, 下面是UNIX环境下的一个例子:
第一步: 往数据库添加三个大的redo logfile
SVRMGRL>ALTER DATABASE ADD LOGFILE GROUP 4
('/opt/oradata/app/redo04.log',
'/ora_bak/oradata2/redolog/redo04.log') size 16M reuse;
 

SVRMGRL>ALTER DATABASE ADD LOGFILE GROUP 5
('/opt/oradata/app/redo05.log',
'/ora_bak/oradata2/redolog/redo05.log') size 16M reuse;

SVRMGRL>ALTER DATABASE ADD LOGFILE GROUP 6
('/opt/oradata/app/redo06.log',
'/ora_bak/oradata2/redolog/redo06.log') size 16M reuse;
 
第二步: 手工地做log switch, 使新建的redo logfile起作用.
SVRMGRL>alter system switch logfile;
 
此操作可以执行一到几次, 使旧的redo logfile成invalid状态.
第三步: 删除原来旧的redo logfile.
SVRMGRL>alter database drop logfile group 1;
 
SVRMGRL>alter database drop logfile group 2;
 
SVRMGRL>alter database drop logfile group 3;
 
2、检查点发生时DBWR进程没有完成数据写入引发等待：
 
当日志文件完成一个循环周期后再一次来到原来某个日志文件准备进行重新使用时，发现该日文件对应的数据还没有写入相应的数据文件中，此时LGWR必须等待DBWR完成写入，从而引发等待。
 
如果怀疑存在这个问题可以通过如下查询来进行监控：
 
select event,total_waits,time_waited,average_wait
 
from v$system_event
 
where event like 'log file switch (check%';
 
通过total_waits,time_waited,average_wait这些数值的大小来判断分析问题，如果还不能确定，那么可以查看一下Oracle的alert.log文件看一下相关时间内是否存在“checkpoint not complete”。如果存在那么证明日志文件的操作性能被DBWR进程所拖累。此时可以通过如下措施解决：
 
.检查存放数据文件的磁盘是否存在I/O瓶颈（如：是否存在读写竞争、是否存在物理损坏、是否存在RADI类型不符等）；
 
.合理规划调整日志文件组日志文件的数量和大小；
 
.合理设置FAST_START_MTTR_TARGET参数，以便设置一个合适的数值来控制检查点的发生；
 
.可以考虑增加DBWR进程的数量，Oracle最多可以有10个DBWR进程；
 
.如果条件允许，可以开启异步I/O；
 
3、由于日志归档引发的等待：
 
       当归档发生时，归档日志进程不能快速的进行日志归档，从而导致了LGWR的等待。如果怀由此问题可以通过如下语句来监控：
 
    select event,total_waits,time_waited,average_wait
 
from v$system_event
 
where event like 'log file switch (arch%';
 
        同样通过total_waits,time_waited,average_wait这些数值来进行问题分析，如果出现由于归档日志写入缓慢引发的性能问题，可以采用如下办法：
 
            .确定存放归档日志的磁盘空间没有被写满，如果出现这种情况，那么要对归档日志进行有限度的删除，或者将这些归档日志移走如存放到磁带库上，或者分配更大的存储空间；
 
            .增加日志文件组，从而为归档多留出一些时间；
 
            .增加多个归档进程，Oracle最多允许10个归档进程存在，在归档发生时如果LGWR进程发现归档进程ARCH出现不足时，会自动产生新的归档进程，因此如果系统负载有明显增加预先分配足够的归档进程可以提高性能，可以使用alter system命令通过更改LOG_ARCHIVE_MAX_PROCESSES参数来改变归档进程数目；
 
（2）、日志缓冲区数据槽的分配情况引发的等待：
 
         可以通过如下的语句来监控日志缓冲区数据槽的分配情况的百分比：
 
       select r.value "retries",e.value "entries",r.value/e.value*100 "percentage"
 
from v$sysstat t,v$sysstat e
 
where r.name='redo buffer allocation retries'
 
and e.name='redo entries';
 
      这个百分比值不能高于1%，如果这个数值频繁增长，那么一定出现了Log_Buffer内存空间不足，从而使得新产生的redo log entries不能写入Log_Buffer中从而造成等待，这个等待是由于LGWR性能不佳写日志文件过慢造成的，通常来说LGWR写入速度都是非常快速的可以保证新产生的redo log entries内存空间使用的需要，即使在高负载情况下也不会出现太大问题，因而上面的问题通常发生机率较小，但是如果一旦发生，那么很有可能是由于日志文件磁盘I/O规划出现问题，或者日志文件磁盘出现物理损坏，因此在出现这种情况引发的性能问题时，主要应该进行日志文件磁盘I/O规划以及日志文件磁盘是否出现物理损伤方面的排查，同时也可能综合应用如Oracle的alert.log等相关文件进行综合分析。