性能诊断与调优之V$--V$SESSION_WAIT_HISTORY

V$SESSION_WAIT_HISTORY View

Historical information has been lacking from the Oracle wait events views. Low-level historical data simply doesn’t exist, and DBAs have to rely on high-level data from V$SESSION_EVENT, V$SYSTEM_EVENT 

and the Statspack utility for performance diagnosis. This view retains the 10 most recent wait events for each connected session. This is not a complete history, but it is a step in the right direction.

V$SESSION_WAIT_HISTORY从10g开始为每个session保留了等待事件的历史信息，其对应的字段如下:

SQL>   desc V$SESSION_WAIT_HISTORY;Name       Type         Nullable Default Comments ---------- ------------ -------- ------- -------- SID        NUMBER       Y                         SEQ#       NUMBER       Y                         EVENT#     NUMBER       Y                         EVENT      VARCHAR2(64) Y                         P1TEXT     VARCHAR2(64) Y                         P1         NUMBER       Y                         P2TEXT     VARCHAR2(64) Y                         P2         NUMBER       Y                         P3TEXT     VARCHAR2(64) Y                         P3         NUMBER       Y                         WAIT_TIME  NUMBER       Y                         WAIT_COUNT NUMBER       Y    

How to Use V$SESSION_WAIT_HISTORY View

As you noticed, most of the columns in this view are identical to the ones in V$SESSION_WAIT except for the SEQ#, EVENT#, and WAIT_COUNT columns. The value in column SEQ# indicates the order in which the session encountered the wait events. The most recent event will have a value of 1, while the oldest event will have a value of 10.The column EVENT# identifies the event in the instance. Please note that in Oracle Database 10g Release 1 there is no EVENT_ID in this view. The WAIT_TIME column shows the session wait time for the event. A value of zero means the session was waiting for the event to complete when this information was captured. A value greater than zero denotes the session’s last wait time. The WAIT_COUNT column shows the number of times the session waited for this event.However, we failed to notice any value other than 1 in this column in Oracle Database 10g Release 1. This view comes in handy when you don’t want to keep querying V$SESSION_WAIT view in quick successions to see what events get posted by the session but would like to quickly get a peek at what

events the session has posted recently. The view refreshes the information when the session posts the next event. The oldest event is removed, and the newest one is displayed with a SEQ# of 1. You can view the history of wait events for a particular session using the following query:

V$SESSION_WAIT_HISTORY与V$SESSION_WAIT 视图相比，除了SEQ#,EVENT#,WAIT_COUNT这三列相同外，其他列都是相同的。

SEQ#列表示SESSION按顺序遇到的等待事件，最近的等待事件为1，最远的为10。

WAIT_TIME 列表示这个等待事件在会话中的等待时间。=0：表示正在等待事件结束；>0最后的等待时间。

WAIT_CONT列表示等待的次数。

当开始下一个等待事件时，该 视图中的信息将被刷新，记录最远时间的事件将会移出，同时最新的等待事件为1，其他向 后移一个顺序号。

可以使用SID进行如下SESSION的查询：

SQL>  SELECT sid, seq#, event, p1, p2, p3, wait_time FROM V$SESSION_WAIT_HISTORY WHERE SID=127;        SID       SEQ# EVENT                                                                    P1         P2         P3  WAIT_TIME---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------       127          1 SQL*Net message from client                                      1413697536          1          0          0       127          2 SQL*Net message from client                                      1413697536          1          0          0       127          3 SQL*Net message to client                                        1413697536          1          0          0       127          4 Streams AQ: waiting for messages in the queue                          8808  874862364          5        500       127          5 SQL*Net message from client                                      1413697536          1          0          0       127          6 SQL*Net message to client                                        1413697536          1          0          0       127          7 Streams AQ: waiting for messages in the queue                          8808  874862364          5        500       127          8 SQL*Net message from client                                      1413697536          1          0          0       127          9 SQL*Net message to client                                        1413697536          1          0          0       127         10 Streams AQ: waiting for messages in the queue                          8808  874862364          5        501 10 rows selected

This information is good, but 10 slots are too few for an active session because events roll off too quickly. We hope Oracle will provide more slots in future releases. However, as we said earlier, this is the first step in the right direction in providing online historical data for performance diagnosis. The V$ACTIVE_SESSION_HISTORY view in Oracle Database 10g Release 1 also offers historical performance data, which we will discuss in Chapter 9. The information is based on 1-second sampling intervals versus the most recent 10 waits, as in the V$SESSION_WAIT_HISTORY view. We prefer the V$ACTIVE_SESSION_HISTORY because it gives more historical data.

V$ACTIVE_SESSION_HISTORY视图也提供了历史数据的查询，但都只有历史的10条信息。