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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/55472/viewspace-422959/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/55472/viewspace-422959/