Wednesday, February 18, 2009

Average wait times for all events

Summary
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