oracle v$system_event,学习动态视图(八) v$system_event

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