最常用的性能视图——v$system_event、v$session_event（3）！

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视图。