Oracle 等待事件小结(上)

--查看等待事件分类情况 

SELECT   wait_class#,

           wait_class_id,

           wait_class,

           COUNT ( * ) AS "count"

    FROM   v$event_name

GROUP BY   wait_class#, wait_class_id, wait_class

ORDER BY   wait_class#;

WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS                count

----------- ------------- -------------------- ----------

          0    1893977003 Other                       717

          1    4217450380 Application                  17

          2    3290255840 Configuration                24

          3    4166625743 Administrative               54

          4    3875070507 Concurrency                  32

          5    3386400367 Commit                        2

          6    2723168908 Idle                         94

          7    2000153315 Network                      35

          8    1740759767 User I/O                     45

          9    4108307767 System I/O                   30

         10    2396326234 Scheduler                     7

         11    3871361733 Cluster                      50

         12     644977587 Queueing                      9

常见的空闲事件有:

• 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

一些常见的非空闲等待事件有:

• db file scattered read

• db file sequential read

• buffer busy waits

• free buffer waits

• enqueue

• latch free

• log file parallel write

• log file sync

相关视图

V$SESSION：  

代表数据库活动的开始，视为源起。

V$SESSION_WAIT：  

视图用以实时记录活动SESSION的等待情况，是当前信息。

V$SESSION_WAIT_HISTORY：  

是对V$SESSION_WAIT的简单增强，记录活动SESSION的最近10次等待。

V$SQLTEXT：  

当数据库出现瓶颈时，通常可以从V$SESSION_WAIT找到那些正在等待资源的SESSION，通过SESSION的SID，联合V$SESSION和V$SQLTEXT视图就可以捕获这些SESSION正在执行的SQL语句。

V$ACTIVE_SESSION_HISTORY: 

是ASH的核心，用以记录活动SESSION的历史等待信息，每秒采样一次，这部分内容记录在内存中，期望值是记录一个小时的内容。

WRH#_ACTIVE_SESSION_HISTORY : 

是V$ACTIVE_SESSION_HISTORY在AWR的存储地。

V$ACTIVE_SESSION_HISTORY: 

中的信息会被定期(每小时一次)的刷新到负载库中，并缺省保留一个星期用于分析。

DBA_HIST_ACTIVE_SESS_HISTORY: 

视图是WRH#_ACTIVE_SESSION_HISTORY视图和其他几个视图的联合展现，通常通过这个视图进行历史数据的访问。

V$SYSTEM_EVENT 

由于V$SESSION记录的是动态信息，和SESSION的生命周期相关，而并不记录历史信息，所以ORACLE提供视图V$SYSTEM_EVENT来记录数据库自启动以来所有等待事件的汇总信息。通过这个视图，用户可以迅速获得数据库运行的总体概况。

 

33个常见的等待事件

1>Buffer busy waits

从本质上讲，这个等待事件的产生仅说明了一个会话在等待一个Buffer（数据块），但是导致这个现象的原因却有很多种。

常见的两种是：

当一个会话视图修改一个数据块，但这个数据块正在被另一个会话修改时。

当一个会话需要读取一个数据块，但这个数据块正在被另一个会话读取到内存中时。

Oracle 操作的最小单位是块（Block），即使你要修改一条记录，也需要对这条记录所在的这个数据块做操作。 当你对这个数据块做修改时，其他的会话将被阻止对这个数据块上的数据做修改（即使其他用户修改的不是当前用户修改的数据），但是可以以一致性的方式读取这个数据块（from undo）。当前的用户修改完这个数据块后，将会立即释放掉加在这个数据块上的排他锁，这样另一个会话就可以继续修改它。 修改操作是一个非常短暂的时间，这种加锁的机制我们叫Latch。

 

会话修改数据块的步骤

以排他的方式获得这个数据块（Latch）

修改这个数据块。

释放Latch。

 

Buffer busy waits等待事件常见于数据库中存在的热快的时候，当多个用户频繁地读取或者修改同样的数据块时，这个等待事件就会产生。 如果等待的时间很长，我们在AWR或者statspack 报告中就可以看到。

这个等待事件有三个参数。 查看有几个参数我们可以用以下SQL:

SQL> select name, parameter1, parameter2, parameter3 from v$event_name where name='buffer busy waits';

NAME         PARAMETER1  PARAMETER2  PARAMETER3

--------------------  ----------   ----------  ----------

buffer busy waits    file#      block#     class#

在下面的示例中，查询的方法和这个一样，所以其他事件对参数的查询将不做过多的说明。

File#:：等待访问数据块所在的文件id号。

Blocks： 等待访问的数据块号。

ID： 在10g之前，这个值表示一个等待时间的原因，10g之后则表示等待事件的类别。

一般buffer busy wait<=1%。检查缓冲等待统计部分(v$waitstat),

如果等待是否位于段头（segment header），如果是，可以考虑增加自由列表（free list 对于Oracle 8i DMT）或者增加freelist groups(在很多时候这个调整是立竿见影的，在8.1.6之前，这个freelists参数不能动态修改;在8.1.6及以后版本，动态修改feelists需要设置COMPATIBLE至少为8.1.6). 

如果等待位于undo header，可以增加回滚段（rollback segment）来解决缓冲区的问题。

如果等待位于undo block ，需要检查相关应用，适当减少大规模的一致性读取，或者降低一致性读取的表的数据密度或者增大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 不再常见! 

2>Buffer  latch

内存中数据块的存放位置是记录在一个hash列表（cache buffer chains）当中的。 当一个会话需要访问某个数据块时，它首先要搜索这个hash 列表，从列表中获得数据块的地址，然后通过这个地址去访问需要的数据块，这个列表Oracle会使用一个latch来保护它的完整性。 当一个会话需要访问这个列表时，需要获取一个Latch，只有这样，才能保证这个列表在这个会话的浏览当中不会发生变化。

产生buffer latch的等待事件的主要原因是：

Buffer chains太长，导致会话搜索这个列表花费的时间太长，使其他的会话处于等待状态。

同样的数据块被频繁访问，就是我们通常说的热快问题。

 

产生buffer chains太长，我们可以使用多个buffer pool的方式来创建更多的buffer chains，或者使用参数DB_BLOCK_LRU_LATCHES来增加latch的数量，以便于更多的会话可以获得latch，这两种方法可以同时使用。

 

这个等待事件有两个参数：

Latch addr： 会话申请的latch在SGA中的虚拟地址，通过以下的SQL语句可以根据这个地址找到它对应的Latch名称：

select * from v$latch a,v$latchname b where

addr=latch addr   -- 这里的latch addr 是你从等待事件中看到的值 

and a.latch#=b.latch#;    

chain#： buffer chains hash 列表中的索引值，当这个参数的值等于0xfffffff时，说明当前的会话正在等待一个LRU latch。

 

3>Control file parallel write

当数据库中有多个控制文件的拷贝时，Oracle 需要保证信息同步地写到各个控制文件当中，这是一个并行的物理操作过程，因为称为控制文件并行写，当发生这样的操作时，就会产生control file parallel write等待事件。

控制文件频繁写入的原因很多，比如：

日志切换太过频繁，导致控制文件信息相应地需要频繁更新。

系统I/O 出现瓶颈，导致所有I/O出现等待。

 

当系统出现日志切换过于频繁的情形时，可以考虑适当地增大日志文件的大小来降低日志切换频率。

当系统出现大量的control file parallel write 等待事件时，可以通过比如降低控制文件的拷贝数量，将控制文件的拷贝存放在不同的物理磁盘上的方式来缓解I/O 争用。

 

这个等待事件包含三个参数：

Files： Oracle 要写入的控制文件个数。

Blocks： 写入控制文件的数据块数目。

Requests：写入控制请求的I/O 次数。

4>Control file sequential read

当数据库需要读取控制文件上的信息时，会出现这个等待事件，因为控制文件的信息是顺序写的，所以读取的时候也是顺序的，因此称为控制文件顺序读，它经常发生在以下情况：

备份控制文件

RAC 环境下不同实例之间控制文件的信息共享

读取控制文件的文件头信息

读取控制文件其他信息

 

这个等待事件有三个参数：

File#：要读取信息的控制文件的文件号。

Block#： 读取控制文件信息的起始数据块号。

Blocks：需要读取的控制文件数据块数目。

5>Db file parallel read

这是一个很容易引起误导的等待事件，实际上这个等待事件和并行操作（比如并行查询，并行DML）没有关系。 这个事件发生在数据库恢复的时候，当有一些数据块需要恢复的时候，Oracle会以并行的方式把他们从数据文件中读入到内存中进行恢复操作。

 

这个等待事件包含三个参数：

Files： 操作需要读取的文件个数。

Blocks： 操作需要读取的数据块个数。

Requests：操作需要执行的I/O次数。

6>Db file parallel write

这是一个后台等待事件，它同样和用户的并行操作没有关系，它是由后台进程DBWR产生的，当后台进程DB向磁盘上写入脏数据时，会发生这个等待。

 

DBWR会批量地将脏数据并行地写入到磁盘上相应的数据文件中，在这个批次作业完成之前，DBWR将出现这个等待事件。 如果仅仅是这一个等待事件，对用户的操作并没有太大的影响，当伴随着出现free buffer waits等待事件时，说明此时内存中可用的空间不足，这时候会影响到用户的操作，比如影响到用户将脏数据块读入到内存中。

           

当出现db file parallel write等待事件时，可以通过启用操作系统的异步I/O的方式来缓解这个等待。 当使用异步I/O时，DBWR不在需要一直等到所有数据块全部写入到磁盘上，它只需要等到这个数据写入到一个百分比之后，就可以继续进行后续的操作。

 

这个等待事件有两个参数：

Requests： 操作需要执行的I/O次数。

Timeouts：等待的超时时间。

7>Db file scattered read（数据文件离散读取）

这个等待事件在实际生产库中经常可以看到，这是一个用户操作引起的等待事件，当用户发出每次I/O需要读取多个数据块这样的SQL 操作时，会产生这个等待事件，最常见的两种情况是全表扫描（FTS： Full Table Scan）和索引快速扫描（IFFS： index fast full scan）。

 

这个名称中的scattered( 发散)，可能会导致很多人认为它是以scattered 的方式来读取数据块的，其实恰恰相反，当发生这种等待事件时，SQL的操作都是顺序地读取数据块的，比如FTS或者IFFS方式（如果忽略需要读取的数据块已经存在内存中的情况）。

 

这里的scattered指的是读取的数据块在内存中的存放方式，他们被读取到内存中后，是以分散的方式存在在内存中，而不是连续的。

实际的环境诊断过程中，可以通过v$session_wait视图发现session等待，结合其他视图找到问题的SQL

也可结合v$session_longops动态性能视图来进行诊断，

从Oracle 9i开始，Oracle新增了一个视图v$SQL

 

这个等待事件有三个参数：

File#： 要读取的数据块所在数据文件的文件号。

Block#： 要读取的起始数据块号。

Blocks：需要读取的数据块数目。

从Oracle 9i开始，Oracle新增了一个视图v$SQL_PLAN用于记录当前系统Library Cache 中SQL语句的执行计划

获得全表扫描的对象

Select distinct object_name,object_owner from v$sql_plan p Where p.operation='TABLE ACCESS'and p.options='FULL' and object_owner='SYS'; 

获得全索引扫描的对象

Select distinct object_name,object_owner from v$sql_plan p Where p.operation='INDEX' and p.options='FULL SCAN' and object_owner='SYS'; 

通过V$SQL_PLAN和V$SQLTEXT联合，获得全表扫描的SQL语句 

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; 

8>Db file sequential read（数据文件顺序读取） 

这个等待事件在实际生产库也很常见，当Oracle 需要每次I/O只读取单个数据块这样的操作时，会产生这个等待事件。 最常见的情况有索引的访问（除IFFS外的方式），回滚操作，以ROWID的方式访问表中的数据，重建控制文件，对文件头做DUMP等。

 

这里的sequential也并非指的是Oracle 按顺序的方式来访问数据，和db file scattered read一样，它指的是读取的数据块在内存中是以连续的方式存放的。

 

这个等待事件有三个参数：

File#： 要读取的数据块锁在数据文件的文件号。

Block#： 要读取的起始数据块号。

Blocks：要读取的数据块数目（这里应该等于1(通常)）。

如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，可能没有正确使用驱动表，或者可能索引的使用存在问题，并非索引总是最后的选择

9>Db file single write

这个等待事件通常只发生在一种情况下，就是Oracle 更新数据文件头信息时（比如发生Checkpoint）。

 

当这个等待事件很明显时，需要考虑是不是数据库中的数据文件数量太大，导致Oracle 需要花较长的时间来做所有文件头的更新操作（checkpoint）。

 

这个等待事件有三个参数：

File#: 需要更新的数据块所在的数据文件的文件号。

Block#：需要更新的数据块号。

Blocks：需要更新的数据块数目（通常来说应该等于1）。

10>Direct path read

这个等待事件发生在会话将数据块直接读取到PGA当中而不是SGA中的情况，这些被读取的数据通常是这个会话私有的数据，所以不需要放到SGA作为共享数据，因为这样做没有意义。 这些数据通常是来自与临时段上的数据，比如一个会话中SQL的排序数据，并行执行过程中间产生的数据，以及Hash Join，merge join产生的排序数据，因为这些数据只对当前的会话的SQL操作有意义，所以不需要放到SGA当中。

 

当发生direct path read等待事件时，意味着磁盘上有大量的临时数据产生，比如排序，并行执行等操作。 或者意味着PGA中空闲空间不足。

 

这个等待事件有三个参数：

Descriptor address:       一个指针，指向当前会话正在等待的一个direct read I/O。

First dba: descriptor address 中最旧的一个I/O数据块地址。

Block cnt: descriptor address上下文中涉及的有效的buffer 数量。

补充：Direct path read/write 通常发生在oracle直接读取数据到PGA时

这时，不需要读取SGA，直接跳过路径读取读等待事件的3个参数file#，first block# ，block数量

最常见的是第一种情况。在DSS系统中，存在大量的Direct path read是很正常的，但是在OLTP系统中，通常显著的直接路径读都意味着系统应用存在问题，从而导致大量的磁盘排序读取操作。 

直接路径写通常发生在Oracle直接从PGA写数据到数据文件或临时文件，这个写操作可以绕过SGA。直接路径写等待事件的3个参数分别是：file#（指绝对文件号）、first block#和block数量。

通常发生的情况

下篇http://blogold.chinaunix.net/u1/46911/showart_2503750.html