Oracle Wait Interface解释

Oracle Wait Interface

       Oracle等待事件的种类包括：I/O, locks, latches, enqueues, background process activities, network latencies, memory,等。可以通过V$SYSTEM_WAIT_CLASS查询到。

       使用之前必须设置TIMED_STATISTICS=TRUE。

 

OWI的关键组件

视图

·V$EVENT_NAME：包含定义的所有等待事件；

·V$SESSION_WAIT：显示每个会话当前正在等待的时间和资源的详细信息，这是一个实时视图；

在10G中，v$session_wait已经合并到了v$session 视图中，虽然仍包含v$session_wait。

·V$SESSION_EVENT：显示当前连接的会话的等待事件的聚集；

·V$SYSTEM_EVENT：显示所有会话遇到的所有等待事件的聚集；

       Oracle 10G新增的关键组件包括：

•          V$SESTEM_WAIT_CLASS

•          V$SESSION_WAIT_CLASS

•          V$SESSION_WAIT_HISTORY

•          V$EVENT_HISTOGRAM

•          V$ACTIVE_SESSION_HISTORY

跟踪

       ·Trace event 10046—扩充的SQL跟踪；

       ·如果无法交互性的监控事件，可以通过纪录等待事件到跟踪文件进行诊断性能问题；

       ·可以在实例（Event=“10046 tace name contex forever, level 8”， 永远不要这么做）/会话级别（Alter session set events ‘10046 trace name context forever, level 8’）启用。

       ·运行要跟踪的SQL；

       ·Alter session set events ‘10046 trace name context off’；

 

       ???以下过程在可能没有。

       ·也可以使用dbms_support.start_trace进行跟踪；

       ·跟踪其他会话：Exec dbms_support.start_trace_in_session(sid=>xxx, serial#=>xxx, waits=>true, binds=>true);

       ·结束跟踪：stop_trace_in_session；

 

       ·跟踪文件的位置在user_dump_dest 目录下；

·可以使用tracefile_identifer命名跟踪文件名，Alter session set tracefile_identifier= ‘Tracefilesql1’；

·然后使用tkprof工具进行格式化输出，最近Metalink上有一个新的工具TRCA，更加适合分析跟踪文件。

      

OWI的限制

       ·没有CPU 统计；

       ·非端到端的可见性；

       ·没有历史数据；

       ·由于当前计算机速度的飞增，一些计算可能不精确；

 

常见的等待事件

       ·Buffer busy waits：10g中更改为read by other session；参数中P1代表块所在的文件号，P2代表实际的块号，P3在9i中代表reason for the wait，10g中代表v$waitstat中的CLASS列；等待时间为1s或100cs。

       ·Control file parallel write：参数中P1代表块驻留的绝对文件号，P2代表总块数，P3代表I/O请求数量；等待时间为完成所有I/O请求的实际延迟。

       ·Db file parallel read：当一个进程从一个/多个数据文件读取多个不连续的块，或数据库恢复时有些数据库块需要恢复时会发生这种事件；参数P1代表需要读取的文件数，P2读取的总块数，P3代表I/O请求的总数量；等待时间为完成所有I/O请求的实际延迟。

       ·Db file parallel write：当DBWR以批模式些藏块到数据文件时会发生；参数中P1代表需要写入的文件数，P2代表要写的总块数，P3（9.2+）以百分之一秒为单位代表超时的值；等待时间：无超时。

       ·Db file scattered read：通常在全表扫描时发生；参数P1代表开始读取的块的文件号，P2代表开始读取的块号，P3代表读取的块数；等待时间：无超时。

       ·Db file sequential read：当从索引，回滚段，表（ROWID），数据文件头，一些临时段读取时会发生该事件；参数P1代表开始读取的块的文件号，P2代表开始读取的块号，P3通常为1，但是临时段则大于1；等待时间：无超时。

       ·Db file single write：当Oracle更新数据文件头时发生该事件，通常在检查点期间。如果数据库有大量数据文件可能会注意到这种情况；参数P1代表写入的文件号，P2代表开始写入的块，P3通常为1；等待时间：无超时。

       ·Direct path read：当oracle直接读取数据库块到会话PGA而不是SGA的缓冲缓存时时发生，直接路径读取通常在访问临时段时使用；参数P1读取的所在的绝对文件号，P2代表开始读取的块号，P3则为读取的块数；等待时间：无超时。

       ·Direct path write：与Direct path read相反，Oracle从PGA写缓冲到数据文件，通常用来写到临时段，直接数据加载或并行DML操作。

       ·Enqueue：是一种Oracle用来串行化访问数据库资源的共享内存结构，进程为了得到该enqueue锁必须等待在队列中。用来串行访问资源的各种enqueue包括：ST，空间管理；SQ，序列号；TX，事务；参数P1代表正在等待的进程请求的enqueue名和模式，P2代表请求的锁的资源标识符ID1，P3代表请求的锁的资源标识符ID2；等待时间：依赖于enqueue名，Oracle最多可以等待三秒，或直到enqueue资源可用。

       ·Free buffer waits：当会话无法在数据库缓冲缓存中找到空闲缓冲读入块或者建立一致性读时将发生该事件。这会唤醒DBWR释放脏缓冲；参数中P1代表文件号，P2代表需要读入到缓存中的块号，P3（9i未使用），10G显示缓冲缓存中LRU列表的id；等待时间：Oracle最多等待1s，然后重试。

       ·Latch free：当进程等待一个当前被其他进程占用的latch时会发生这种事件。进程要得到latch不必等待在队列中。最常见的latch包括：cache buffer chains, library cache和shared pool；参数P1代表latch的地址，P2代表latch号（v$latchname.latch#），P3代表尝试的次数；等待时间：指数增长，10G中latch由其自身的等待事件。

       ·Library cache pin / library cache lock：当会话试图将一个对象“钉“在库缓存中以更改或测试它时会发生该事件，必须得到一个钉确保对象不会被改变；P1代表库对象地址，P2代表加载锁的地址，P3为模式+名字空间（来自v$db_object_cache）；等待时间：PMON为1S，其他进程为3S。

       ·Log buffer space：当进程必须等待日志缓冲中的空间可用时发生；未使用参数；等待时间：1S，如果必须等待日志切换则为5S。

       ·Log file parallel write：会话等待LGWR写日志缓冲块到重做组成员时发生；参数P1代表要写入的日志文件数，P2代表要写入的OS块数，P3代表I/O请求数量；等待时间：实际的延迟。

       ·Log file sequential read：当进程等待块从在线重做日志文件读取时会发生该事件；参数P1代表重做日志文件的相对序列号，P2代表开始读取的块号，P3代表读取的OS块数；等待时间：实际的延迟。

       ·Log file switch (archiving needed)：指示ARCH 跟不上LGWR。

       ·Log file switch (checkpoint incomplete)：文件归档前检查点必须完成，指示重做日志文件可能太小。

       ·Log file sync：参数P1代表需要同步的日志缓冲中的缓冲的数量；等待时间：1S。

       ·SQL*Net message from/to client：如果很大，可能指示网络有问题；P1：显示网络驱动器的类型，P2代表字节数，P3未使用。等待时间：实际的延迟。

       ·跟踪CPU和其他统计：V$SESSTAT / V$SYSSTAT，其中包含了

–         CPU used by this session

–         CPU used when call started

–         Recursive CPU usage

–         Parse time CPU

–         Session logical reads

–         Physical reads

–         Physical writes

 

等待事件：根本原因分析

       ·需要收集等待事件统计历史；

       ·Trace 10046，会有很大的负载但是可以得到最小粒度的性能数据；

       ·Statspack，不能得到会话级别的数据；

       ·使用BEFORE LOGOFF TRIGGER收集历史数据—低负载（如果会话被KILL或挂起则没有任何数据）。建立表并保持大约7天的数据，可以归档这些数据进行长期比较。将等待事件和产生该事件的SQL语句（V$SQLTEXT）保存起来。

 

推断常用等待事件：找到以及修复？

诊断IO相关的等待事件

       ·在任何系统中I/O操作都是最慢的活动；

       ·与I/O相关的最常见的事件：

–         Db file scattered read

–         Direct path read

–         Direct path write

–         Log file parallel write

–         Db file parallel write

–         Controlfile parallel write

–         Db file sequential read

 

·Db file sequential read

Oracle进程想要的块当前不在SGA中，查看V$SESSION_EVENT的TIME_WAITED和AVERAGE_WAIT列，从系统范围的事件来看时，这通常是TOP 5。

         大量的等待时间通常是由于应用程序问题，当前应该小于1cs。

         如果db files sequential reads的对象是索引，应用程序可能执行了大量的索引读。考虑使用全表扫描是否合适？？？检查聚簇因子；检查两个初始化参数（optimizer_index_cost_adj和optimizer_index_caching）的设置。

如果db files sequential reads的对象是表—记住索引读后的rowid访问是顺序的。检查V$SYSTEM_EVENT的average_wait，其为TOP 5并不指示系统有问题，如果没有出现在top 5中，则表明其他等待有问题，需要解决。如果AVGERAGE_WAIT特别大—检查磁盘热点，10G使用ASM平衡负载。

       ·db file scattered read

         Oracle会话使用db_file_multiblock_read_count从磁盘读取块到SGA，多块I/O请求通常与全表扫描和索引快速全扫描相关。过高的该事件值通常是应用程序的问题，需要使用更多的单块读（索引扫描）代替多块读。

         如果全表扫描时恰当的，考虑实施多块读减少等待时间。如果应用程序开始高效运行，突然开始产生db file scattered read waits，查看是否有索引被删除或无效。

         可以使数据库倾向于全表扫描的优化器参数包括：db_file_multiblock_read_count，hash_area_size，optimizer_index_cost_adj；通常表很久未分析也会导致该等待事件增加，检查表的last_analyzed；如果表有大量链接行也会产生该问题。

       ·Direct path read waits

  通常是磁盘排序太多，检查V$STATNAME，v$sesstat；决定内存排序的参数包括：sort_area_size，pga_aggregate_target；目标是尽可能的减少排序，使用UNION ALL代替UNION，哈希连接代替排序接合，嵌套循环代替哈希连接。

·Direct path write waits

直接写来自SORT, CTAS, HASH, INDEX, 以及运行在并行模式的sqlldr；直接路径写的调整方法和直接路径读一样；首先调整对直接路径读和写影响最大SQL语句。

       ·Db file parallel write

         该事件属于DBWR进程。该事件如果很大意味着I/O问题，没有该事件的用户会话可能显示‘free buffer wait’，‘write complete wait’；检查系统是否支持asynch_io（HP仅在RAW上支持），如果是则使用它，否则考虑使用多DBWRs。

       ·Log file parallel write

         该事件属于LGWR。这是一个系统范围的事件，用户会话可能指示‘log file sync’；查看v$system_event的time_waited和average_wait列，如果average is > 1cs，则系统可能正在经历较低的吞吐量；该事件的修复同Db file parallel write；不幸的是不能使用多个LGWRs；检查日志文件所在的位置是否有冲突，对某些操作使用NOLOGGING。

       ·Control file parallel write

       该事件通常是大量日志切换的征兆。增加日志文件的大小。

 

诊断锁相关的等待事件

       ·锁与闩的区别是：闩的唯一目的是探测排他访问内存结构，闩保护内存对象。锁的目的是：1.当资源在兼容模式时，允许多个进程共享资源；2.当资源在不兼容模式时强制排它访问。锁保护数据库对象，一共有6种模式：null, row share, row exclusive, share, row share exclusive以及exclusive.

       ·Latch Free wait 

         查看V$SYSTEM_EVENT表的TOTAL_WAITS列，闩可以通过V$LATCH监控。在高并发的系统中，闩冲突时很常见的，应该被预计。5种最常见的闩等待为：shared pool, library cache, cache buffers chains, cache buffers lru chain, row cache objects。

共享池和库缓存闩等待通常是由于大量的硬解析，在使用文本常量的应用程序中这是很频繁的；可以通过使用绑定变量，设置CURSER_SHARING为FORCE解决；通过查看V$SQLAREA.PARSE_CALLS识别SQL语句。

         缓存缓冲链闩等待是由于低效的SQL语句引起的，当应用程序打开多个并行会话执行相同的语句查询相同的结果集时可以得到该冲突；另一个原因是热块在缓存缓冲中。可以查看V$SESSION_WAIT的P1RAW 列检查是否有热块在缓冲缓存中；可以通过EXP/IMP带热块的表，增加PCTFREE的值进行扩展；考虑减小具有许多热块的表的块（9i+）。

         缓存缓冲LRU链闩等待是由于有大量的缓存缓冲活动。重复扫描大量低选择性的索引或者执行全表扫描的语句通常是主要原因；只能通过调整SQL。

         行缓存对象闩保护数据字典，唯一方法是增加共享池的尺寸。

       ·enqueue wait

         enqueue是应用于数据库资源的锁。它们由应用程序请求初始化，查询V$ENQUEUE_STAT查看各种信息。

         最常见的enqueue等待是TX，模式号为6。是一种行级锁，通过V$LOCK确定阻塞者。另一种模式号为4，通常是主键强制或等待数据块中的一个ITL槽；查看V$SEGMENT_STATISTICS决定ITL等待的值，统计名为‘ITL waits’。通过使用较高的INITTRANS和PCTFREE重建对象修复该问题。

         对于唯一键强制问题，会在多个用户同时向表插入相同的键时发生；方法是找出为什么应用程序允许用户同时尝试插入重复的键。

         另一种常见的enqueue是等待ST Enqueue，每个数据库只有一个ST锁，方法是更改UET$和FET$请求该锁。使用本地管理表空间修复，使用TEMPFILE重建所有临时表空间修复，如果不能从字典管理表空间更改，增加所有高速增长的段的next extent分区尺寸，同时允许预分配分区。

         另一种常见Enqueue是等待TM Enqueue，模式号为3。未索引外键是这种锁冲突的主要原因。如果应用程序显示使用LOCK TABLE语句也会发生这种锁，见V$SQLAREA。

       ·Buffer busy waits

         通常是由于多个会话尝试读取块到内存中或者尝试将块钉在内存中时会发生。可以通过尝试降低级别或并行性，或增加对象的FREELISTS/FREELIST GROUPS；也可以尝试重建表使用较小的块或增加PCTFREE的值减少表中的行数。

         如果主要等待是段头，检查NEXT分区的大小并确保PCTFREE和PCTUSED之间的gap不会太大；如果主要等待是撤销段头，可能是由于有太多的小的回滚段，考虑使用系统管理撤销。

诊断延迟相关的等待事件

       ·Log file sync

         当会话等待LGWR写出缓冲时会发生这种情况，主要有三个原因：高提交率—应用程序相关，I/O子系统太慢—考虑RAW，RAID 0，光纤通道，过大的日志缓冲。同时参数PROCESSES过大可能也会造成该事件等待增加。

       ·Log buffer space

         如果由于空间不足或LGWR进程不够快，会话等待拷贝重做条目到日志缓冲时会发生这种情况。如果是日志缓冲太小，则考虑增加它；如果是I/O子系统太慢—考虑使用NOLOGGING或升级硬件。

       ·Free buffer

         当DBWR正在从SGA写出藏块时会发生该等待。

         主要原因有：编写得很差的SQL，DBWRs不够，较慢的I/O，延迟块清除，较小的缓冲缓存。

         延迟的块清除：第一个扫描刚被加载的表的进程将会进行该工作，全表扫描刚加载的表可以最小化该问题，或者分析这些数据也可以解决。

         缓冲缓存太小通常不是问题，在增加它之前考虑增加DBWR。

       ·Write complete

         是前台进程等待DBWR写出块的征兆。检查db file parallel writes事件的情况。

       ·Log file switch completion

         当重做日志太小并且事务产生大量的重做日志条目时会发生这种事件。可以通过创建更大的日志文件或可以通过创建更多的重组日志组完成。

 

       还有一些10g新增的等待事件和RAC环境下的等待事件。

 

Dumps和Traces

       ·主要是ORA-0600和核心ORA-7445事件；

       ·作为一个DBA，必须熟悉ORADEBUG，可以通过oradebug dumplist和oradebug help得到帮助，该工具没有文档。

       ·遇到块中断时需要dump数据文件和块：

      alter system dump datafile block ;

       ·控制文件也可以被dump，当跟踪恢复相关的问题和SCN同步问题时可能会有用。

         alter session set events ‘immediate trace name controlf level 10’;

         或：

         oradebug sitmypid

         oradebug ulimit

  oradebug dump controlf 10

       ·共享池有问题时通常需要DUMP堆。

         alter sessions set events ‘immediate trace name heapdump level ’;

         或：

         oradebug sitmypid

         oradebug ulimit

         oradebug dump heapdump

       ·库缓存DUMP会给出库缓存中关于对象的详细信息。

         alter sessions set events ‘immediate trace name library_cache  level 10’;

         或

         oradebug sitmypid

         oradebug ulimit

         oradebug dump library_cache 10

       ·当诊断内存中断或死锁错误时可以使用DUMP进程状态.

         alter sessions set events ‘immediate trace name processstate level ’;

         oradebug sitmypid

         oradebug ulimit

         oradebug dump processstate

       ·当诊断数据库挂起条件时可以DUMP系统状态。

         alter sessions set events ‘immediate trace name systemstate level ’;

         或：

         oradebug sitmypid

         oradebug ulimit

         oradebug dump systemstate

 

转载务必注明来源于本博客。