等待事件分析

在Oracle 10g中的等待事件有872个，11g中等待事件1116个。 我们可以通过v$event_name 视图来查看等待事件的相关信息。


1.1 查看v$event_name视图的字段结构：


SQL> desc v$event_name
 Name
 ---------------------------
 EVENT#
 EVENT_ID
 NAME
 PARAMETER1
 PARAMETER2
 PARAMETER3
 WAIT_CLASS_ID
 WAIT_CLASS#
 WAIT_CLASS


1.2  查看等待事件总数：
SQL> select count(*) from v$event_name;


  COUNT(*)
----------
      1118


1.3  查看等待事件分类情况：


SQL>   SELECT   wait_class#,
  2             wait_class_id,
  3             wait_class,
  4             COUNT ( * ) AS "count"
  5      FROM   v$event_name
  6  GROUP BY   wait_class#, wait_class_id, wait_class
  7  ORDER BY   wait_class#;


WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS                                                            count
----------- ------------- ---------------------------------------------------------------- ----------
          0    1893977003 Other                                                                   719
          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


WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS                                                            count
----------- ------------- ---------------------------------------------------------------- ----------
         11    3871361733 Cluster                                                                  50
         12     644977587 Queueing                                                                  9


13 rows selected.


1.4  相关的几个视图：


V$SQLTEXT：  当数据库出现瓶颈时，通常可以从V$SESSION_WAIT找到那些正在等待资源的SESSION，通过SESSION的SID，联合V$SESSION和V$SQLTEXT视图就可以捕获这些SESSION正在执行的SQL语句。     （SQL层等待分析）
V$SESSION：  代表数据库活动的开始，视为源起。
V$SESSION_WAIT：  视图用以实时记录活动SESSION的等待情况，是当前信息。  （会话层等待分析）
如： select event,total_waits from v$session_event where sid=### order by total_waits desc;
V$SYSTEM_EVENT 由于V$SESSION记录的是动态信息，和SESSION的生命周期相关，而并不记录历史信息，所以ORACLE提供视图V$SYSTEM_EVENT来记录数据库自启动以来所有等待事件的汇总信息。通过这个视图，用户可以迅速获得数据库运行的总体概况。  （系统层等待分析）
如：  select event,total_waits from v$system_event order by total_waits desc;
V$SESSION_WAIT_HISTORY：  是对V$SESSION_WAIT的简单增强，记录活动SESSION的最近10次等待。
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视图和其他几个视图的联合展现，通常通过这个视图进行历史数据的访问。


1.5 常见的等待事件


1.5.1   db file scattered read


产生原因：
当PGA中无所需数据，数据块以multiblock read的行式被读取到SGA中时。如：FTS(full table scan)，IFFS(index fast full scan)
解决对策：
无需解决、考虑索引、考虑并行


1.5.2   DB File Sequential Read  （单块读等待）


背景知识：
这里的sequential指的是将数据块读入到相连的内存空间中(contiguous memory space)，而不是指所读取的数据块是连续的。
产生原因：
a:  最为常见的是执行计划中包含了INDEX FULL SCAN/UNIQUE SCAN,此时出现”db file sequential read”等待是预料之中的，一般不需要我们去特别关注


b:  当执行计划包含了INDEX RANGE SCAN-(“TABLE ACCESS BY INDEX ROWID”/”DELETE”/”UPDATE”)， 服务进程将按照”访问索引->找到rowid->访问rowid指定的表数据块并执行必要的操作”顺序访问index和table，每次物理 读取都会进入”db file sequential read”等待，且每次读取的都是一个数据块；这种情况下clustering_factor将发挥其作用，需要我们特别去关注，本例中提及的解决方法对 这种情景也有效


c:  Extent boundary,假设一个Extent区间中有33个数据块，而一次”db file scattered read”多块读所读取的块数为8，那么在读取这个区间时经过4次多块读取后，还剩下一个数据块，但是请记住多块读scattered read是不能跨越一个区间的(span an extent)，此时就会单块读取并出现”db file sequential read”。这是一种正常现象，一般不需要额外关注


d:  假设某个区间内有8个数据块，它们可以是块a,b,c,d,e,f,g,h，恰好当前系统中除了d块外的其他数据块都已经被缓存在buffer cache中了，而这时候恰好要访问这个区间中的数据，那么此时就会单块读取d这个数据块，并出现”db file sequential read”等待。注意这种情况不仅于表，也可能发生在索引上。这是一种正常现象，一般不需要额外关注


e:  chained/migrated rows即链式或迁移行，这里我们不介绍链式行的形成原因，chained/migrated rows会造成服务进程在fetch一行记录时需要额外地单块读取，从而出现”db file sequential read”。这种现象需要我们特别去关注，因为大量的链式/迁移行将导致如FULL SCAN等操作极度恶化(以往的经验是一张本来全表扫描只需要30分钟的表，在出现大量链式行后，全表扫描需要数个小时)，同时也会对其他操作造成不那么 明显的性能影响。可以通过监控v$sysstat视图中的”table fetch continued row”操作统计来了解系统中链式/迁移行访问的情况，还可以通过DBA_TBALES视图中的CHAIN_CNT来了解表上的链式/迁移行情况，当然这 要求定期收集表上的统计信息；如果没有定期收集的习惯，那么可以配合@?/rdbms/admin/utlchain脚本和analyze table list chained rows 命令来获取必要的链式行信息


f:  创建Index entry，显然当对表上执行INSERT操作插入数据时，虽然在执行计划中你看不到过多的细节，但实际上我们需要利用索引来快速验证表上的某些约束是否 合理，还需要在索引的叶子块中插入相关的记录，此时也可能出现”db file sequential read”等待事件，当然这还和具体的插入的方式有关系。这是一种正常现象，一般不需要额外关注


1.5.3  Direct Path Read


产生原因：
数据被直接读取到PGA内存中时，发生的等待。如：排序数据由于内存不足，被写到磁盘上（temp表空间数据文件）
，然后重新读取时。
解决办法：
无需解决、增大内存排序区（PGA）、调整操作的并行度、改善磁盘I/O、


1.5.4  Direct Path  write


产生原因：
数据从PGA内存中直接写到磁盘上，发生的等待。 如：排序数据由于内存不足，被写到磁盘上（temp表空间数据文件）




1.5.5  Log File Sync


产生原因：
用户commit(rollback)时，lgwr需要将log buffer的数据写到log file上面，发生的等待。
解决本法：
减少commit的频率（错误的频繁提交）、提高I/O性能、增加lgwr进程个数


1.5.6   buffer busy waits  （热快）


产生原因：
在SGA中读取或修改缓冲区的会话必须首先获取cache buffers chains锁存器，并且遍历这个缓冲区链，直到他发现必需的缓冲区头。然后，他必须以共享模式或独占模式获取一个缓冲区锁或缓冲区头上的pin，这取决于他计划的操作。一旦缓冲区头被钉住，会话就释放cache buffers chains锁存器，并在缓冲区自身上执行计划的操作。如果无法获取一个pin，会话就在buffer busy waits等待事件上等待。这种等待时间不会应用于在会话的私有PGA中执行的读取或写入操作。
解决办法：
1、出现此情况通常可能通过几种方式调整：增大data  buffer；
2、增加freelist，减小pctused；怎样的目的是将一个block上可以使用的空间减少，这样的话：一个block上的数据存放的较少，可以提高应用的访问并发率，减少hot block的产生；
3、增加回滚段数目，增大initrans，考虑使用LMT, 确认是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)；
3、可以建立block较小的表空间，见热点对象移动到此表空间上去；
4、优化应用，优化索引，提高索引的命中率；
◎ Oracle会话正在等待钉住一个缓冲区。必须在读取或修改缓冲区前将它钉住。在任何时刻只有一个进程可以钉住一个缓冲区。
◎ buffer busy waits表明读/读、读/写、写/写争用。
◎ 采取的适当措施取决于P3参数中的原因码。
A、如果等待处于字段头部，应增加自由列表（freelist）的组数，或者增加pctused到pctfree之间的距离。
B、如果等待处于回退段（undo）头部块，可以通过增加回滚段(rollback segment)来解决缓冲区的问题；
C、如果等待处于回退段（undo）非头部块上，就需要降低驱动一致读取的表中的数据密度,或者增大DB_CACHE_SIZE；
D、如果等待处于数据块，可以将数据移到另一数据块以避开这个"热"数据块、增加表中的自由列表或使用LMT表空间；
E、如果等待处于索引块，应该重建索引、分割索引或使用反向键索引。


1.5.7    free buffer waits


产生原因：
server process无法找一个可用的内存空间。
（系统I/O成为瓶颈（或者性能不够）、等待资源 latch争用、SGA太小、SGA太大，dbwr无法快速的把脏数据刷到磁盘上）
解决办法：
优化I/O、增加多个dbwr 进程、增大SGA





个人总结与网络总结
部分参考：http://www.askmaclean.com/archives/db-file-sequential-read-wait-event.html