作者: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 -