动态视图学习计划

Oracle event wait analysis is an extremely sophisticated part of Oracle system tuning.  In order to be effective, an Oracle wait analysis must interrogate v$system_event to look at system events that are currently happening within your system.  Excessive waits on system events can indicate disk I/O bottlenecks, network bottlenecks, or additional external environmental problems that are causing slowdowns within your Oracle database.

脚本：

set pages 999
set lines 90 

column c1 heading 'Event|Name'             format a30
column c2 heading 'Total|Waits'            format 999,999,999
column c3 heading 'Seconds|Waiting'        format 999,999
column c4 heading 'Total|Timeouts'         format 999,999,999
column c5 heading 'Average|Wait|(in secs)' format 99.999 

ttitle 'System-wide Wait Analysis|for current wait events' 

select
   event                         c1,
   total_waits                   c2,
   time_waited / 100             c3,
   total_timeouts                c4,
   average_wait    /100          c5
from
   sys.v_$system_event
where
   event not in (
    'dispatcher timer',
    'lock element cleanup',
    'Null event',
    'parallel query dequeue wait',
    'parallel query idle wait - Slaves',
    'pipe get',
    'PL/SQL lock timer',
    'pmon timer',
    'rdbms ipc message',
    'slave wait',
    'smon timer',
    'SQL*Net break/reset to client',
    'SQL*Net message from client',
    'SQL*Net message to client',
    'SQL*Net more data to client',
    'virtual circuit status',
    'WMON goes to sleep'
   )
AND
 event not like 'DFS%'
and
   event not like '%done%'
and
   event not like '%Idle%'
AND
 event not like 'KXFX%'
order by  c2 desc;

从 7.0.12 版本开始，Oracle有如下几个 OWI 视图:

V$EVENT_NAME
V$SESSION_WAIT
V$SESSION_EVENT
V$SYSTEM_EVENT

具体参见OracleWait Interface