Wait Events（2） — 常见的等待事件

常见的等待事件

尽管有许多不同类型的等待事件，其实大部分都是很少见的。DBA 只对其中很少的一部分等待事件感兴趣，其它的等待事件不会发生（由于所使用的 Oracle 的特性），或者很少发生，以至于可以根本不用考虑它们。在不同的环境中，你将见到不同的等待事件，随着所安装的 Oracle 的环境和数据库负载的不同而不同。

例如，如果不使用并行查询，不会发生 PX 等待事件；如果不使用多线程服务机制（或者叫共享服务机制），不会发生 virtual circuit status 等待事件；在只读系统中，log file sync 和 sequeue 等待不会发生。

下面是一些常见的等待事件：

Wait Event	Description
buffer busy waits	The session wants to access a data block that is either 1) currently not in memory, but another process has already issued an I/O request to read the block into memory, or 2) in memory but in an incompatible mode (current versus consistent, for example).
control file parallel write	The session has issued multiple I/O requests in parallel to write blocks to all control files, and is waiting for all of the writes to complete.
control file sequential read	The session is waiting for blocks to be read from a control file.
db file parallel read	The session has issued multiple I/O requests in parallel to read blocks from data files into memory and is waiting for all requests to complete. This may occur during recovery or during regular activity when a session batches many single block I/O requests together and issues them in parallel.
db file parallel write	The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk and is waiting for all requests to complete.
db file scattered read	The session has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache and is waiting for the operation to complete. This typically happens during a full table scan or fast full index scan.
db file sequential read	The session has issued an I/O request to read one block from a data file into the buffer cache and is waiting for the operation to complete. This typically happens during an index lookup or a fetch from a table by ROWID when the required data block is not already in memory.
direct path read, direct path write	The session has issued asynchronous I/O requests that bypass the buffer cache and is waiting for them to complete. These wait events often involve temporary segments.
enqueue	The session is waiting on an enqueue (a lock you can see in v$lock). This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user.
free buffer waits	The session needs a free buffer so it can bring a data block into the buffer cache and is waiting for a buffer that is not dirty to become available. This can occur if DBWR is not writing dirty buffers to disk fast enough.
latch free	The session is waiting for a latch held by another session. (This event does not apply to processes that are spinning while waiting for a latch; when a process is spinning, it is not waiting.)
library cache load lock	The session is waiting for the opportunity to load an object or a piece of an object into the library cache. (Only one process can load an object or a piece of an object at a time.)
library cache pin	The session wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. This happens when you are compiling or parsing a PL/SQL object or a view.
log buffer space	The session is waiting for space in the log buffer. (Space becomes available only after LGWR has written the current contents of the log buffer to disk.) This typically happens when applications generate redo faster than LGWR can write it to disk.
log file parallel write	The session is waiting for blocks to be written to all online redo log members in one group. LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.
log file sequential read	The session is waiting for blocks to be read from the online redo log into memory. This primarily occurs at instance startup and when the ARCH process archives filled online redo logs.
log file switch completion	The session is waiting for a log file switch to complete, typically so more redo can be generated.
log file sync	The session is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction. (A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)
undo segment extension	The session is waiting for an undo segment to be extended or shrunk.
write complete waits	The session is waiting for a requested buffer to be written to disk; the buffer cannot be used while it is being written.

有一些称为“空闲事件（idle events）”的等待事件，当 Oracle 进程无事可做时，或等待某事件的触发时，会处于空闲事件等待中。从优化的角度看，空闲事件没什么用，所以当我们观察等待事件时，可以跳过这些事件。常见的空闲等待事件如下：

Idle Events
client message	PX Deq: Execute Reply
dispatcher timer	PX Deq: Execution Msg
gcs for action	PX Deq: Signal ACK
gcs remote message	PX Deq: Table Q Normal
ges remote message	PX Deque wait
i/o slave wait	PX Idle Wait
jobq slave wait	queue messages
lock manager wait for remote message	rdbms ipc message
null event	slave wait
parallel query dequeue	smon timer
pipe get	SQL*Net message from client
PL/SQL lock timer	SQL*Net message to client
pmon timer	SQL*Net more data from client
PX Deq Credit: need buffer	virtual circuit status
PX Deq Credit: send blkd	wakeup time manager

等待事件不能给我们提供什么

如果一个 Oracle 进程需要执行某工作，但必须等待某事件，那么该进程就处于非空闲等待中；如果一个进程无事可做，它就会处于空闲等待中。那么，如果一个进程有工作要做，并且正在执行中，该进程处于什么状态呢？当进程处于工作状态时，不会产生等待的信息，因为该进程并没有等待什么。

从 Oracle 的等待事件所提供的信息中，我们可以清晰的看到某等待事件产生了多少次，消耗了多少时间。 But we do not see anything about the time periods in which the process requested use of the CPU. 也就是说，等待事件中不能给我们提供如下信息：

• Time spent using the CPU
   
• Time spent waiting for a CPU to become available
   
• Time spent waiting for requested memory to be swapped back in to physical memory
   

我们要记住这一点，否则很容易产生错误的判断。你可能正在优化一个执行很慢的 SELECT 语句，发现该进程并没有处于那些主要的等待事件中。你很容易这样判断：这个 SQL 就是最优的，它确实需要这么长的时间来执行。然而事实上，这个查询可能进行了大量的逻辑读，重写该 SQL 就会降低缓冲区的获取数量。

当 Oracle 需要访问的数据块已经在缓冲区中了，此时会执行逻辑读（而不是物理读），这时候不会产生等待。大量的 CPU 时间会消耗在大量的逻辑读上，从等待事件接口中，我们看不到总共消耗的时间。

Statement parsing and spinning while waiting for a latch to become available are two other examples of activities not accounted for by the wait event interface. An Oracle process uses CPU time while parsing a statement or spinning on a busy latch; since no waiting is involved, the wait event interface does not have anything to report.

最近 Oracle 公司在 Statspack 报告中，提供了一个新功能。从 Oracle9i 开始，Statspack 报告中记录等待事件信息的部分，同时也会列出 CPU 的使用情况，这是非常有用的信息。我们可以很容易地比较出消耗在等待上的时间和消耗在执行上的时间，所以我们就知道从哪里着手优化。然而需要指出的是，Statspack 报告中的 CPU 使用信息不是从等待事件接口中获得的，而是从动态性能视图 v$sysstat 中获得的。

关于参数 timed_statistics

Oracle 可以对很多事件进行计时（包括等待事件）。在 Oracle8i 及以前版本中，时间统计可以精确到 0.01 秒，但默认情况下，时间统计是关闭的。从 Oracle9i Release 2 开始，时间统计默认情况下是打开的，一些计时可以精确到微秒。参数 timed_statistics 用来打开/关闭时间统计。当该参数设置为 FALSE 时，等待事件中的所有时间都会显示为 0。通过如下语句，可以从系统级或会话级打开时间统计：

• ALTER SYSTEM SET timed_statistics = TRUE;

• ALTER SESSION SET timed_statistics = TRUE;

也可以在初始化参数文件中设置：

        timed_statistics = true

事实上，统计时间本身所消耗的时间很少。很多情况下，你从时间统计中获得的信息远远超过了本身所降低的性能。很多年来，许多 DBA 都在他们的生产库中开启了时间统计。所以，从 Oracle9i Release 2 开始，这就是默认功能了。

（翻译自：）