Oracle10g New Feature -- 11. Wait Interface

本章內容同上一章一樣，都是針對資料庫效能。本章更注重對等待事件的分析。

檢視v$session_wait, v$session, v$session_wait_class,  v$system_wait_class, v$session_wait_history幫助使用者取得關於等待事件的各種詳細資訊

[@more@]

Wait Interface

1.       Enhancement in V$SESSION_WAIT

Added 3 columns:

WAIT_CLASS_ID 

WAIT_CLASS#

WAIT_CLASS: the type of the wait that must be either addressed as a valid wait event or dismissed as an idle one

WAIT_TIME  : -2  In Oracle10g,-2 means the platform does not support a fast timing mechanism and TIMED_STATISTICS is not set

 

2.       Enhancement in V$SESSION

    Event wait information is also included in view v$session, like BLOCKING_SESSION_STATUS, BLOCKING_SESSION, SEQ#, EVENT#, EVENT, P1TEXT, P1, P1RAW, P2TEXT, P2, P2RAW, P3TEXT, P3, P3RAW, WAIT_CLASS_ID, WAIT_CLASS#, WAIT_CLASS, WAIT_TIME, SECONDS_IN_WAIT, STATE, SERVICE_NAME.

    So, if a session is waiting for a lock holding by another session, you can issue the following query:

SQL> select BLOCKING_SESSION_STATUS, BLOCKING_SESSION from v$session where sid=216;

BLOCKING_SE BLOCKING_SESSION

----------- ----------------

VALID                    265

3.       How Many Waits?

    We can get more precise wait information by issue the following query:

SQL>select * from v$session_wait_class where sid = 269;

SID SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS    TOTAL_WAITS TIME_WAITED

---- ------- ------------- ----------- ------------- ----------- -----------

 269    1106    4217450380           1 Application           873      261537

 269    1106    3290255840           2 Configuration           4           4

 269    1106    3386400367           5 Commit                  1           0

 269    1106    2723168908           6 Idle                   15      148408

 269    1106    2000153315           7 Network                15           0

 269    1106    1740759767           8 User I/O               26           1

the session has waited 873 times for a total of 261,537 centi-seconds for application-related waits, 15 times in network-related events, and so on.

 

    System wide statistics for wait classes(we also can use this query to get wait_class_id and wait_class list):

SQL>select * from v$system_wait_class;

WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS    TOTAL_WAITS TIME_WAITED

------------- ----------- ------------- ----------- -----------

   1893977003           0 Other                2483       18108

   4217450380           1 Application          1352      386101

   3290255840           2 Configuration          82         230

   3875070507           4 Concurrency            80         395

   3386400367           5 Commit               2625        1925

   2723168908           6 Idle               645527   219397953

   2000153315           7 Network              2125           2

   1740759767           8 User I/O             5085        3006

   4108307767           9 System I/O         127979       18623

 

To show the metric values of wait classes for the most recent 60-second interval

SQL>select * from V$WAITCLASSMETRIC

To show the metric value of wait classes for all intervals in the last one hour:

SQL>select * from V$WAITCLASSMETRIC_HISTORY

 

4.       Session Wait History:

V$SESSION_WAIT_HISTORY automatically maintains the last 10 wait event for active sessions.

SQL>select event, wait_time, wait_count

from v$session_wait_history

where sid = 265

/

EVENT                           WAIT_TIME WAIT_COUNT

------------------------------ ---------- ----------

log file switch completion              2          1

log file switch completion              1          1

log file switch completion              0          1

SQL*Net message from client         49852          1

SQL*Net message to client               0          1

enq: TX - row lock contention          28          1

SQL*Net message from client           131          1

SQL*Net message to client               0          1

log file sync                           2          1

log buffer space                        1          1