本章内容同上一章一样,都是针对数据库性能。本章更注重对等待事件的分析。
视图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:
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;
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-778841/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/207/viewspace-778841/