All the average times waited for the important wait events.
SELECT EVENT,
TOTAL_WAITS,
ROUND (100 * (TOTAL_WAITS / SUM_WAITS), 2) PCT_WAITS,
TIME_WAIT_SEC,
ROUND (100 * (TIME_WAIT_SEC / GREATEST (SUM_TIME_WAITED, 1)), 2)
PCT_TIME_WAITED,
TOTAL_TIMEOUTS,
ROUND (100 * (TOTAL_TIMEOUTS / GREATEST (SUM_TIMEOUTS, 1)), 2)
PCT_TIMEOUTS,
AVERAGE_WAIT_SEC
FROM (SELECT EVENT,
TOTAL_WAITS,
ROUND ( (TIME_WAITED / 100), 2) TIME_WAIT_SEC,
TOTAL_TIMEOUTS,
ROUND ( (AVERAGE_WAIT / 100), 2) AVERAGE_WAIT_SEC
FROM V$SYSTEM_EVENT
WHERE EVENT NOT IN
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'rdbms ipc reply',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data from client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep',
'jobq slave wait',
'Queue Monitor Wait',
'wakeup time manager',
'PX Idle Wait')
AND EVENT NOT LIKE 'DFS%'
AND EVENT NOT LIKE 'KXFX%'),
(SELECT SUM (TOTAL_WAITS) SUM_WAITS,
SUM (TOTAL_TIMEOUTS) SUM_TIMEOUTS,
SUM (ROUND ( (TIME_WAITED / 100), 2)) SUM_TIME_WAITED
FROM V$SYSTEM_EVENT
WHERE EVENT NOT IN
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'rdbms ipc reply',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data from client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep',
'jobq slave wait',
'Queue Monitor Wait',
'wakeup time manager',
'PX Idle Wait')
AND EVENT NOT LIKE 'DFS%'
AND EVENT NOT LIKE 'KXFX%')
ORDER BY 2 DESC, 1 ASC;
No comments:
Post a Comment