OWI 性能诊断及调优-常见等待事件

buffer busy waits

The buffer busy waits event occurs when a session wants to access a data block in the buffer cache that is currently in use by some other session. The other session is either reading the same data block into the buffer cache from the datafile, or it is modifying the one in the buffer cache.
buffer busy waits 等待事件发生在当一个会话需要访问的数据当前被其他会话在使用。其他会话或者正在从数据文件中将该数据块读到内存或者这个会话正在修改这个数据块。
In order to guarantee that the reader session has a coherent image of the block with either all of the changes or none of the changes, the session modifying the block marks the block header with a flag letting other sessions know that a change is taking place and to wait until the complete change is applied.
为了保证读的会话同其他全部修改的或没有修改的有一个一致性镜像，这个会话在修改数据块时会在数据库头做个标记让其他会话知道这个块正在被修改，需要等待修改被完成。
The most common buffer classes that encounter buffer busy waits are data blocks, segment header, undo blocks, and undo header。
最常见的发生buffer busy waits等待事件的buffer类是data blocks, segment header, undo blocks, and undo header。

db file scattered read

The db file scattered read event is posted by the session when it issues an I/O request to read multiple data blocks. The blocks read from the datafiles are scattered into the buffer cache. These blocks need not remain contiguous in the buffer cache. The event typically occurs during full table scans or index fast full scans.The initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT determines the maximum number of data blocks to read.
db file scattered read 等待事件发生在当这个会话请求离散读取多个数据块。这些数据块从数据文件里被分散读进buffer cache.这些块不需要在buffer cache中保持连续性。典型的发生场景是全表扫描或者索引快速全扫描。初始化参数 DB_FILE_MULTIBLOCK_READ_COUNT 决定了一次读取块的最大数量。
Waiting on datafile I/O completion is normal in any Oracle database. The presence of this wait event does not necessarily indicate a performance problem. However, if the time spent waiting for multiblock reads is significant compared to other waits, you must investigate the reason for it.
等待数据文件IO完成在任何oracle数据库都是很普遍的，发生这个等待事件不是意味着性能问题。但如果这个时间相对于其他等待事件很显著，那么就需要探究下原因了。
Wait parameters for db file scattered read are described here:
P1 File number to read the blocks from
P2 Starting block number to begin reading
P3 Number of blocks to read
P1：数据文件号
P2：读取开始的块编号
P3：读取块的数量

db file sequential read

The db file sequential read wait event occurs when the process waits for an I/O completion for a sequential read. The name is a bit misleading, suggesting a multiblock operation, but this is a single block read operation. The event gets posted when reading from an index, rollback or undo segments,table access by rowid, rebuilding control files, dumping datafile headers, or the datafile headers.
db file sequential read这个等待事件发生在等一个进程在等待I/O顺序读取数据块的完成。这里是单块读操作，这个事件发生在从索引、回滚段、通过rowid访问表数据（索引回表）,构建控制文件，导出数据文件头，或者读取数据文件头时。
P1 File number to read the data block from
l P2 Starting block number to read
l P3 1 in most cases, but for temporary segments can be more than 1
P1：数据文件号
P2：数据文件开始块号
P3: 大部分场景是1，对于临时段可能超过1

direct path read

The direct path read event occurs when Oracle is reading data blocks directly into the session’s PGA instead of the buffer cache in the SGA. Direct reads may be performed in synchronous I/O or asynchronous I/O mode, depending on the hardware platform and the value of the initialization parameter, DISK_ASYNCH_IO. Direct read I/O is normally used while accessing the temporary segments that reside on the disks. These operations include sorts, parallel queries, and hash joins.
直接路径读等待事件发生在当Oracle读取数据直接进入会话PGA而不是SGA的buffer cache.直接路径读可能发生在异步I/O或者同步I/O模式，取决于硬件平台和初始化参数 DISK_ASYNCH_IO。直接路径读一般用于当访问临时段，这个操作包括排序、并行查询、hash join。
查看异步IO参数：true为开启异步IO
SYS@orcl>show parameter DISK_ASYNCH_IO;

NAME TYPE VALUE

disk_asynch_io boolean TRUE

direct path write

The direct path write wait event is just an opposite operation to that of direct path read. Oracle writes buffers from the session’s PGA to the datafiles. A session can issue multiple write requests and continue processing. The OS handles the I/O operation. If the session needs to know if the I/O operation was completed, it will wait on direct path write event.
直接路径写事件是将buffer从会话PGA直接写到数据文件。一个会话可能发生多个写请求和持续的执行。这OS持有IO操作，如果会话需要知道这个IO操作是否已完成，将发生直接路径写等待事件。
The direct path write operation is normally used when writing to temporary segments, in direct data loads (inserts with APPEND hint, or CTAS), or in parallel DML operations.
直接路径写一般发生在写临时文件段，直接数据加载（以APPEND hint方式写或者CTAS写），或者并行DML操作。
As with the direct path write event, the number of waits and time waited for this event can be misleading when asynchronous I/O is in use.
和直接路径写事件一样，当使用异步IO时这个等待的次数和时间可能不准确。
P1 Absolute file number to write to
P2 Starting block number to write from
P3 Number of blocks to write
P1：正在写的文件编号
P2：写文件的开始块号
P3：写的块数量

db file parallel read

Contrary to what the name suggests, the db file parallel read event is not related to any parallel operation—neither parallel DML nor parallel query. This event occurs during the database recovery operation when database blocks that need changes as a part of recovery are read in parallel from the datafiles. This event also occurs when a process reads multiple noncontiguous single blocks from one or more datafiles.
不同于名字意思，db file parallel read event事件和任何并行操作都不相关（既不是并行DML 也不是并行查询）。此事件发生在数据库恢复操作期间，当需要作为恢复的一部分进行更改的数据库块并行地从数据文件中读取时。当进程从一个或多个数据文件中读取多个不相邻的单个块时，也会发生此事件。
常见的场景：从多个表关联数据插入到新表，如 insert tab_a select b.id,b.name,c.salary from tab_b b,tab_c c where b.id=c.id;
Wait Parameters
Wait parameters for db file parallel read are described here:
P1 Number of files to read from
P2 Total number of blocks to read
P3 Total number of I/O requests (the same as P2 since multiblock read is not used)
Wait Time
No timeouts. The session waits until all of the I/Os are completed.
等待参数:
P1:读取文件的数量
P2:读取数据块的总量
P3：总的IO请求数量（在没有使用多块读时和P2值相同）
等待时间：直到会话将所有IO都读完（从AWR看该等待事件平均等待时间会很大，这不代表IO有问题）

db file parallel write

Contrary to what the name suggests, the db file parallel write event is not related to any parallel DML operation. This event belongs to the DBWR process, as it is the only process that writes the dirty blocks to the datafiles. The blocker is the operating system I/O subsystem. This can also have an impact on the I/O subsystem in that the writes may impact read times of sessions reading from the same disks.
DBWR compiles a set of dirty blocks into a “write batch”. It issues multiple I/O requests to write the write batch to the datafiles and waits on this event until the I/O requests are completed. However, when using asynchronous I/O, DBWR does not wait for the whole batch write to complete, it waits only for a percentage of the batch to complete before pushing the free buffers back onto the LRU chain so that they can be used. It may also issue more write requests.
不同于名字意思，db file parallel write等待事件也是同任何并行DML操作无关。这个等待事件是DBWR进程发布，DBWR进程是将脏块写入到数据文件的唯一进程。阻塞者是IO子系统。这也会对I/O子系统产生影响，因为写操作可能会影响从相同磁盘读取会话的读时间。DBWR将一组脏块汇聚成一起成为一个“write batch”。它发出多个I/O请求，将写批处理写入数据文件，并等待这个事件，直到I/O请求完成。然而，当使用异步I/O时，DBWR不会等待整个批处理完成。它只等待一个百分比的批处理完成，然后将空闲缓冲区推回LRU链，以便它们可以使用。它还可能发出更多的写请求。
Wait Parameters
Wait parameters for db file parallel write are described here:
l P1 Number of files to write to
l P2 Total number of blocks to write
l P3 From Oracle9i Release 9.2 onward, P3 shows the timeout value in centiseconds to wait for the I/O completion; prior to this release, P3 indicates the total number of I/O requests, which is the same as P2 (blocks).
等待参数：
P1: 要写入文件的数量
P2：要写入块的总量
p3：从Oracle 9i开始，P3展示的是等待IO的厘秒（百分秒值），Oracle 9i版本之前是IO请求的总量，同P2相同。

free buffer waits

The free buffer waits event occurs when the session cannot find free buffers in the database buffer cache to read in data blocks or to build a consistent read (CR) image of a data block. This could mean either the database buffer cache is too small, or the dirty blocks in the buffer cache are not getting written to the disk fast enough. The process will signal DBWR to free up dirty buffers but will wait on this event.
free buffer waits等待事件发生在当会话在buffer cache里不能找到空闲块用于读进数据块或者用于构建一致性读镜像。这个意味着buffer cache过小或者脏块写入到磁盘不够快，进程在通知DBWR写脏块时会发生这个等待事件。
Wait parameters for free buffer waits are described here:
P1 File number from which Oracle is reading the block
P2 Block number from the file that Oracle wants to read into a buffer
P3 Not used prior to Oracle Database 10g Release 1; in this release it shows the SET_ID# for the LRU and LRUW lists in the buffer cache
P1：正在读数据块的文件编号
P2：需要读进文件块到buffer的块编号
P3: 10.1版本前没有使用，之后用于展示SET_ID#

latch free

The latch free wait occurs when the process waits to acquire a latch that is currently held by other process. Like enqueue, Oracle uses latches to protect data structures. One process at a time can either modify or inspect the data structure after acquiring the latch. Other processes needing access to the data structure must wait till they acquire the latch. Unlike enqueue, processes requesting latch do not have to wait in a queue. If the request to acquire a latch fails, the process simply waits for a short time and requests the latch again. The short wait time is called “spin”. If the latch is not acquired after one or more spin iterations, the process sleeps for a short time and tries to acquire the latch again, sleeping for successively longer periods until the latch is obtained.
latch free 发生在当进程正在请求的latch(闩）正在被其他进程持有时，oracle使用latches去保护数据结构。进程在获取latch后可以修改或者检查数据结构。其他进程需要访问这个数据结构必须等他们拿到了这个latch。不像队列（enqueue）进程获取latch需要在队列中排队等待。如果请求latch失败，进程简单地等待短暂片刻，然后继续请求latch。这个短暂的等待时间叫spin,如果在一次或多次还没获取到latch后，则进程休眠短暂时间然后再次尝试获取latch，如果还没有获取到则休眠更长时间再次尝试指导获取到latch为止。
Wait Parameters
Wait parameters for latch free are described here:
P1 Address of the latch for which the process is waiting
P2 Number of the latch, same as VLATCHNAME.LATCH#. To find out the latch name waited on, you can use the following SQL statement: select * from vlatchname
where latch# = &p2_value;
P3 Number of tries; a counter showing the number of attempts the process made to acquire the
latch
等待参数：
P1：等待latch的地址
P2：等待latch的编号，同V$LATCHNAME.LATCH#
P3：尝试获取latch的次数

library cache pin

The library cache pin wait event is associated with library cache concurrency. It occurs when the session tries to pin an object in the library cache to modify or examine it. The session must acquire a pin to make sure that the object is not updated by other sessions at the same time. Oracle posts this event when sessions are compiling or parsing PL/SQL procedures and views.
library cache pin等待事件是和library cache并发相关联的。它发生在当会话尝试去获取一个在library cache里的对象为了修改或检查它。这会话必须获取一个pin去确保这个对象没有被其他会话同时修改。Oracle在会话正在编译或者解析PL/SQL 程序和视图时发布这个等待事件。
What actions to take to reduce these waits depend heavily on what blocking scenario is occurring. A common problem scenario is the use of DYNAMIC SQL from within a PL/SQL procedure where the PL/SQL code is recompiled and the DYNAMIC SQL calls something that depends on the calling procedure. If there is general widespread waiting, the shared pool may need tuning. If there is a blocking scenario, the following SQL can be used to show the sessions that are holding and/or requesting pins on the object that are given in P1 in the wait:
select s.sid, kglpnmod “Mode”, kglpnreq “Req”
from x$kglpn p, v$session s
where p.kglpnuse=s.saddr
and kglpnhdl=’&P1RAW’ ;
通过什么方式去降低这些等待取决于正在发生阻塞的场景。一个常见的问题场景是在PL/SQL过程中使用DYNAMIC SQL，在这个过程中PL/SQL代码被重新编译，DYNAMIC SQL调用一些依赖于调用过程的东西。如果这些是很普遍的等待，那么shared pool需要调优。如果这是一个阻塞的场景，下面的SQL可以用于查询哪个会话正在持有或者请求去pin这个对象。
Wait parameters for library cache pin are described here:
P1 Address of the object being examined or loaded
P2 Address of the load lock
P3 Contains the mode plus the namespace (mode indicates which data pieces of the object are
to be loaded; namespace is the object namespace as displayed in V$DB_OBJECT_CACHE
view)
P1：正在检查或加载对象的地址
P2：加载锁的地址
P3：
Wait Time
For the PMON process it is one second; for all others it is three seconds.
等待时间：PMON进程是等1秒，其他进程是等3秒

library cache lock

The library cache lock event is also associated with library cache concurrency. A session must
acquire a library cache lock on an object handle to prevent other sessions from accessing it at the same time, or to maintain a dependency for a long time, or to locate an object in the library cache.
library cache lock事件是和library cache并发相关联的。一个会话必须获取在一个对象句柄上的library cache lock去避免其他会话同时访问这个对象，或者在较长时间维持一个依赖，或者在library cache中定位一个对象。
Wait parameters for library cache lock are described here:
P1 Address of the object being examined or loaded
P2 Address of the load lock
P3 Contains the mode plus the namespace (mode indicates which data pieces of the object are
to be loaded; namespace is the object namespace as displayed in V$DB_OBJECT_CACHE
view)
P1：正在检查或加载对象的地址
P2：加载锁的地址
P3：
Wait Time
For the PMON process it is one second; for all others it is three seconds.
等待时间：PMON进程是等1秒，其他进程是等3秒

log buffer space

The log buffer space wait occurs when the session has to wait for space to become available in the log buffer to write new information. The LGWR process periodically writes to redo log files from the log buffer and makes those log buffers available for reuse. This wait indicates that the application is generating redo information faster than LGWR process can write it to the redo files. Either the log buffer is too small, or redo log files are on disks with I/O contention.
log buffer space 等待事件发生在当会话需要等待可用日志缓存区以写新的信息。LGWR进程周期性地将log buffer数据写到日志文件以便让这些log buffer可以重用。这个等待事件意味着应用生成redo信息比LGWR进程将redo写到日志文件的速度要快。这个等待事件的原因可能是log buffer太小或者redo log files在磁盘发生了IO争用。
Wait Parameters
Wait parameters are not used for log buffer space.
Wait Time
Normally one second, but five seconds if the session has to wait for a log file switch to complete.
等待参数在log buffer space事件中未使用。
等待时间：一般１秒，但是如果这个会话不得等待一个日志文件切换完成时是５秒

log file parallel write

The log file parallel write wait occurs when the session waits for LGWR process to write redo from log buffer to all the log members of the redo log group. This event is typically posted by LGWR process. The LGWR process writes to the active log file members in parallel only if the
asynchronous I/O is in use. Otherwise, it writes to each active log file member sequentially.
日志文件并行写
log file parallel write等待事件发生在当会话正在等LGWR将redo信息从log buffer写到日志文件中。该事件通常由LGWR进程发布。LGWR进程仅仅在使用异步IO时可以并行写到活跃日志文件成员。否则的话只能顺序写到活跃日志成员。
The waits on this event usually indicate slow disk devices or contention where the redo logs are
located.
这个等待事件一般意味着日志所在磁盘缓慢或者发生了争用。
Wait Parameters
Wait parameters for log parallel write are described here:
P1 Number of log files to write to
P2 Number of OS blocks to write to
P3 Number of I/O requests
Wait Time
Actual elapsed time it takes to complete all I/Os. Although the log files are written to in parallel, the write is not complete till the last I/O operation is complete.
等待参数：
P1：要写的日志文件的数量
P2：要写的操作系统块数量
P3：请求的IO数量
等待时间：写所有IO的持续时间，纵使日志文件是以并行方式写的，但直到最后一个IO完成才算结束。

log file sequential read

The log file sequential read wait occurs when the process waits for blocks to be read from the online redo logs files. The ARCH process encounters this wait while reading from the redo log files.
log file sequential read事件发生在进程从联机日志文件读取块的时候。ARCH进程在读取重做日志文件时遇到这种等待。
Wait Parameters
Wait parameters for log file sequential read are described here:
P1 Relative sequence number of the redo log file within the redo log group
P2 Block number to start reading from
P3 Number of OS blocks to read starting from P2 value
Wait Time
Actual elapsed time it takes to complete the I/O request to read.
等待参数：
P1: 重做日志组中重做日志文件的相对序列号
P2: 开始读取的块号
P3: 从P2值开始读取的操作系统块数
等待时间：完成读取I/O请求所需的实际运行时间。

log file switch (archiving needed)

The log file switch wait indicates that the ARCH process is not keeping up with LGWR process writing to redo log files. When operating the database in archive log mode, the LGWR process cannot overwrite or switch to the redo log file until the ARCH process has archived it by copying it to the archived log file destination. A failed write to the archive log file destination may stop the archiving process. Such an error will be reported in the alert log file.
日志文件切换（需要归档）等待事件发生意味着归档进程跟不上LGWR写日志文件的速度。当数据库处于归档模式时，LGWR进程只有等归档进程已经将日志文件归档后才能将这个在线日志文件进行覆写。对归档日志文件目的地的写入失败可能会停止归档进程，如此会在alert日志中有记录报错。
Wait Parameters
Wait parameters are not used for log file switch (archiving needed).
Wait Time
One second
等待参数：未使用
等待时间：1秒

log file switch (checkpoint incomplete)

The log file switch wait indicates that the process is waiting for the log file switch to complete, but the log file switch is not possible because the checkpoint process for that log file has not completed.
You may see this event when the redo log files are sized too small.
日志文件切换 (检查点未完成)意味着进程正在等日志文件切换完成，但是日志文件还不能切换因为检查点进程还没有完成。
在日志文件设置太小的情况下，你可能会看到这个等待事件。
日志文件切换等待(检查点未完成)
Wait Parameters
Wait parameters are not used for log file switch (checkpoint incomplete).
Wait Time
One second
等待参数：未使用
等待时间：1秒

log file switch completion

This wait event occurs when the process is waiting for log file switch to complete.
Wait Parameters
Wait parameters are not used for log file switch completion.
Wait Time
One second
日志文件切换完成等待事件发生在等进程正在等待日志文件切换完成。
等待参数：未使用
等待时间：1秒

log file sync

When a user session completes a transaction, either by a commit or a rollback, the session’s redo information must be written to the redo logs by LGWR process before the session can continue processing. The process waits on this event while LGWR process completes the I/O to the redo log file.
当一个用户会话完成一个事务，通过提交或者回滚，在会话继续操作前，会话的redo信息必须通过LGWR进程写到redo 文件里。当LGWR进程完成IO到日志文件时，这个进程等待log file sync这个事件。
If a session continues to wait on the same buffer#, the SEQ# column of VSESSION_WAIT view should increment every second. If not, then the local session has a problem with wait event timeouts. 如果一个会话继续在同一个buffer#上等待，VSESSION_WAIT视图的SEQ#列应该每秒钟增加一次。如果不是，则本地会话有一个等待事件超时的问题。
If the SEQ# column is incrementing, the blocking process is the LGWR process. Check to see what LGWR process is waiting on because it may be stuck.
如果SEQ#列在递增，阻塞进程为LGWR进程。检查一下LGWR进程正在等待什么，因为它可能被卡住了。
Tune LGWR process to get good throughput to disk; for example, do not put redo logs on RAID-5 disk arrays. If there are lots of short-duration transactions see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each COMMIT has to have it confirmed that the relevant REDO is written to disk. Although commits can be piggybacked by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.
优化LGWR进程以获得良好的磁盘吞吐量，例如，不要将redo logs文件放在raid 5磁盘组，如果有大量的短事务，看下是否可以将事务合并一起以减少不同的COMMIT操作。每个COMMIT都必须确认相关的重做被写入磁盘。尽管这些提交量可以被Oracle支撑，通过批事务降低提交的总量也是有一个非常好的效果。
Wait Parameters
Wait parameters for log file sync are described here:
P1 The number of the buffer in the log buffer that needs to be synchronized
P2 Not used
P3 Not used
Wait Time
One second
P1：日志缓冲区中需要同步的buffer的数量
p2：未使用
P3：未使用