v$system_event:
v$system_event 提供了自实例启动后各个等待事件的概括。
SQL> desc v$system_event
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENT VARCHAR2(64) 等待事件的名称
TOTAL_WAITS NUMBER 等待事件总数
TOTAL_TIMEOUTS NUMBER 超时事件总数
TIME_WAITED NUMBER 等待事件的总时间(百分之一秒)
AVERAGE_WAIT NUMBER 事件等待的平均时间(百分之一秒)
TIME_WAITED_MICRO NUMBER 等待事件的总时间(微秒)
EVENT_ID NUMBER 等待事件的标识符
WAIT_CLASS_ID NUMBER 等待事件的类标识符
WAIT_CLASS# NUMBER 等待事件的类的唯一编号
WAIT_CLASS VARCHAR2(64) 等待事件类的名称
查看系统的各项等待,按等待事件总数排序
SQL> select event,total_waits,total_timeouts,time_waited,average_wait from v$system_event order by total_waits desc;
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- -------------- ----------- ------------
rdbms ipc message 20927 20313 6532187 312.14
db file sequential read 6831 0 2957 .43
control file sequential read 6445 0 27 0
jobq slave wait 2298 2192 673056 292.89
control file parallel write 2182 0 1670 .77
pmon timer 2128 2125 617029 289.96
log file parallel write 812 0 1482 1.82
db file scattered read 515 0 357 .69
Streams AQ: qmn coordinator idle wait 452 227 611876 1353.71
Streams AQ: qmn slave idle wait 224 0 610886 2727.17
virtual circuit status 211 210 615410 2916.63
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- -------------- ----------- ------------
log file sync 116 0 365 3.14
dispatcher timer 105 105 610962 5818.68
LGWR wait for redo copy 87 0 1 .01
SQL*Net message to client 85 0 0 0
SQL*Net message from client 84 0 284450 3386.31
os thread startup 32 0 254 7.93
SQL*Net more data to client 26 0 0 0
smon timer 26 20 578859 22263.82
direct path read 18 0 0 0
rdbms ipc reply 14 0 97 6.92
latch: shared pool 10 0 3 .29
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- -------------- ----------- ------------
read by other session 9 0 145 16.15
SQL*Net break/reset to client 9 0 0 .04
library cache load lock 7 0 21 2.93
log file single write 7 0 2 .24
log file sequential read 7 0 8 1.18
direct path write 6 0 0 0
latch free 5 0 154 30.85
direct path read temp 3 0 0 0
enq: CF - contention 3 0 46 15.24
cursor: pin S wait on X 3 3 3 1.09
JS external job 3 0 197 65.83
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- -------------- ----------- ------------
enq: PR - contention 3 0 13 4.45
Streams AQ: qmn coordinator waiting for slave to start 2 0 7 3.64
Streams AQ: waiting for time management or cleanup tasks 2 1 77111 38555.65
instance state change 2 0 0 .01
latch: redo allocation 2 0 0 .05
latch: cache buffers lru chain 2 0 0 .07
latch: library cache 1 0 0 .09
buffer busy waits 1 0 0 .4
control file heartbeat 1 1 391 390.92
reliable message 1 0 1 .58
已选择43行。
v$session_event:
v$session_event 记录了session自启动以来所有的等待事件。SQL> desc v$session_event
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER 会话ID
EVENT VARCHAR2(64) 等待事件的名称
TOTAL_WAITS NUMBER 会话等待事件总数
TOTAL_TIMEOUTS NUMBER 会话超时事件总数
TIME_WAITED NUMBER 等待事件的总时间(百分之一秒)
AVERAGE_WAIT NUMBER 事件等待的平均时间(百分之一秒)
MAX_WAIT NUMBER 最大等待时间(百分之一秒)
TIME_WAITED_MICRO NUMBER 等待事件的总时间(微秒)
EVENT_ID NUMBER 等待事件的标识符
WAIT_CLASS_ID NUMBER 等待事件的类标识符
WAIT_CLASS# NUMBER 等待事件的类的唯一编号
WAIT_CLASS VARCHAR2(64) 等待事件类的名称
v$session_event 记录了当前session的等待事件,这些信息最终被累积进入v$system_event视图。