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 -