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.
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
举例说明，如果呈现出buffer busy waits问题，那么你用trace_waits.sql脚本在问题最严重的session中得到跟踪文件。检查这个文件并找到正在等待的文件和数据块号。buffer busy waits的文件和数据块号是p1和p2参数。例如2.3例子：
Example 2.3. Sample Dialog from trace_waits.sql
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.
$ 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中都能找到。然而这是………
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/51726/viewspace-65964/，如需转载，请注明出处，否则将追究法律责任。