2.2.4 Wait Parameters

The wait statistics are very useful because they tell you which sessions have been

waiting, and which types of resources they have been waiting for. They may have

been waiting for latches, database blocks, enqueue locks, or other resource types.

Knowing which type can direct your tuning efforts. But the wait parameters are

even more valuable than the wait statistics. They can tell you exactly which

resource—which latch, which database block, or which enqueue lock—is being

waited for. The wait statistics merely put you in the right neighborhood, but the

wait parameters can focus your attention on the right spot.

The wait statisticd 是很重要的，因为它们告诉你哪个session正在等待，等待那种资源。他们可能在等待latches，database blocks ,enqueue locks 或者其他资源。知道哪个资源直接影响你调试结果。但是 wait parameters 比 wait statistics更有价值。他们能正确的告诉你那种资源――那种锁，哪种 databsae block,哪种enqueue lock －正在等待。wait statistics只不过能使你靠近答案，而wait parameters使你得到答案。

Unfortunately, the wait parameters are hard to catch. They can be seen fleetingly

in V$SESSION_WAIT . This view shows the wait parameters for the current or

most recent wait for each session, as well as the duration of the wait, if known.

However, querying V$SESSION_WAIT takes a long time relative to the length of

most waits. If you query this view twice in quick succession and look at the SEQ#

column, which is incremented for each distinct wait, it is not uncommon to notice

that many event waits have been missed in each active session between the two

queries. It is also rather expensive to query V$SESSION_WAIT repeatedly in

quick succession, and so it is of limited usefulness for watching wait parameters.

不幸的是wait parameters很难等到，他们短暂的停留在v$ssion_wait中。这个试图显示当前或者最近活动的session的wait parameters ，就是在等待期间。然而对于大多数等待本身来讲，查询v$session_wait占用了很长的时间。如果你快速成功的查询这个试图两次，看看seq#列，这个列对于不同的等待来说是增加的.很容易注意到，在两次查询直接有些等待事件错过了。重复的查询v$session_wait也是相当昂贵的，所以查看wait parameters是很有限的。

Fortunately, the wait parameters can also be seen in trace files produced by the

new DBMS_SUPPORT package, or by the underlying event 10046. This trace is

the same as that produced by the SQL_TRACE facility but also includes a line for

each wait, including the wait parameters.

幸运的是wait parameters能在新包DBMS_SUPPORT产生的跟踪文件中找到，或者10046事件。

这个跟踪文件和SQL_TRACE生成的文件是一样的，都包括等待事件，和wait parameters。

For example, if there appears to be a problem with buffer busy waits, then you

can enable this trace for a while in the most heavily affected sessions with the

APT script trace_waits.sql . It is then just a matter of extracting the buffer busy

wait lines from the trace files, and examining the wait parameters to find the file

and block numbers of the blocks being waited for. In the case of buffer busy

waits, the file and block numbers are parameters p1 and p2. This is illustrated in

Example 2.3.

举例说明，如果呈现出buffer busy waits问题，那么你用trace_waits.sql脚本在问题最严重的session中得到跟踪文件。检查这个文件并找到正在等待的文件和数据块号。buffer busy waits的文件和数据块号是p1和p2参数。例如2.3例子：

Example 2.3. Sample Dialog from trace_waits.sql

SQL> @trace_waits

the top N sessions affected by waits for a particular resource.

Select sessions waiting for: buffer busy waits

Number of sessions to trace: 5

Seconds to leave tracing on: 900

Tracing ... Please wait ...

PL/SQL procedure successfully completed.

SQL> exit

$ cd udump

$ grep 'buffer busy waits' ora_*.trc |

> sed -e 's/.*p1=/ file /' -e 's/ p2=/ block /' -e 's/ p3.*//' |

> sort |

> uniq -c |

> sort -nr |

> head -5

42 file 2 block 1036

12 file 24 block 3

10 file 2 block 1252

7 file 2 block 112

6 file 7 block 5122

$

The meaning of the wait parameters for each type of wait event is visible in

V$EVENT_NAME and is documented in an appendix to the Oracle8i Reference

guide. However, this is a particularly weak section of the Oracle documentation.

Much of the information is enigmatic, out-of-date, or inaccurate. Because the

wait parameters are so vital to advanced performance tuning, this book explains

the meaning of the wait parameters for each wait event discussed.

每个等待事件的wait parameters的含义在v$event_name 中都有说明，并且在Oracle 8i reference中都能找到。然而这是………