通过视图查历史等待事件

过去发生的等待事件，我们可以通过什么视图查出来呢？

1.自实例启动以来

select *

from (

 select event,total_waits,time_waited,wait_class

 from v$system_event

 where wait_class<>'Idle'

 order by 3 desc

)where rownum<=50;

9i

select *

from (

 select event,total_waits,time_waited

 from v$system_event

 where event not in    ('rdbms ipc message','pmon timer','smon timer','SQL*Net message from client','rdbms ipc reply','SQL*Net more data from client')

 order by 3 desc

)where rownum<=50;

2.当前已连接的各会话的历史等待事件信息

select *

  from (select event, wait_class, sum(total_waits), sum(time_waited)

          from v$session_event

         where wait_class <> 'Idle'

         group by event, wait_class

         order by sum(time_waited) desc)

 where rownum < 20;

9i

select *

  from (select event, sum(total_waits), sum(time_waited)

          from v$session_event

          where event not in ('rdbms ipc message','pmon timer','smon timer','SQL*Net message from client','rdbms ipc reply','SQL*Net more data from client')

         group by event

         order by sum(time_waited) desc)

 where rownum < 20;

3.当前已连接的某SESSION的历史等待事件信息

 select *

  from (select sid, event, total_waits, time_waited

          from v$session_event

         where sid=&1

         order by sid asc,time_waited desc)

where event not like '%rdbms ipc%'

   and event not like '%SQL*Net message%';

9i:

   select sid, event, total_waits, time_waited

     from v$session_event

    where sid in (&1)

    order by sid asc, time_waited desc

备注：

total_waits代表总共等待的次数，比较重要的是time_waited的，这反映了用户的等待体验，time_waited是百分之一秒。