v$session_event 和v$system_event

| dbtan

v$session_event 和v$system_event：

前面提到过v$session及v$session_wait视图记录了活动会话当前正在发生的等待，但是要知道一个活动会话在其生命周期可能经历很多等待，这些等待通过v$session_event视图记录。但是需要注意的是，这个视图记录的是累积信息，同一会话对于同一事件发生的多次等待会被累计。以下是一个会话的等待事件输出：

sys@TQGZS> select sid,event,time_waited,time_waited_micro
  2  from v$session_event
  3  where sid=165
  4  order by 3;
       SID EVENT                          TIME_WAITED TIME_WAITED_MICRO
---------- ------------------------------ ----------- -----------------
       165 latch: shared pool                       0              4968
       165 db file scattered read                 157           1565804
       165 db file sequential read                527           5270844
       165 smon timer                          602029        6020292510

v$session_event的信息和会话生命周期相关，这些信息同时会被累积到v$system_event视图作为数据库整体等待数据保存，比如：

sys@TQGZS> select event,total_waits,time_waited,average_wait
  2  from v$system_event
  3  where event='latch: shared pool';
EVENT                          TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
------------------------------ ----------- ----------- ------------
latch: shared pool                      30          18           .6

但是注意，v$session_event或者v$system_event视图的累积信息以及关于等待的平均计算，使我们无法得知个别等待消耗的时间长短。

为了解决这一问题，Oracle 10g引入了一个新的视图v$event_histogram，通过这个视图可以看到等待事件的柱状图分布，从而可以对一个等待事件的具体分布有进一步的了解，在以下查询输出中可以看到，shared pool latch的竞争主要是2毫秒以内的短时竞争，但是注意等待时间在65536毫秒左右的等待也有4次，长时间的Latch竞争是在数据库优化时需要认真关注的：

sys@CCDB> select event,wait_time_milli,wait_count
  2  from v$event_histogram                
  3  where event = 'latch: shared pool'
  4  order by 2;
EVENT                          WAIT_TIME_MILLI WAIT_COUNT
------------------------------ --------------- ----------
latch: shared pool                           1      28073
latch: shared pool                           2       1456
latch: shared pool                           4       2895
latch: shared pool                           8       5422
latch: shared pool                          16       5706
latch: shared pool                          32       5452
latch: shared pool                          64       4145
latch: shared pool                         128       3833
latch: shared pool                         256       2947
latch: shared pool                         512       2532
latch: shared pool                        1024       2387
latch: shared pool                        2048       1673
latch: shared pool                        4096        954
latch: shared pool                        8192        334
latch: shared pool                       16384        167
latch: shared pool                       32768         49
latch: shared pool                       65536          4
17 rows selected.

- The End -