Wait Events

From: The Magnet <art_at_unsu.com>
Date: Wed, 22 Jun 2011 08:44:51 -0700 (PDT)
Message-ID: <1929967c-1190-48df-b72f-846c544ec328_at_22g2000yqv.googlegroups.com>



Hi,

I'm trying to understand these queries. They both gather data from the wait event views, but they return totally different results. The one that comes from the system views seems to be much more static. Should I be looking at one over the other, or is one more accurate on the system health?

SELECT a.event, a.total_waits, a.time_waited, a.average_wait FROM v$system_event a, v$event_name b, v$system_wait_class c WHERE a.event_id=b.event_id
AND b.wait_class#=c.wait_class#
AND c.wait_class IN ('Application','Concurrency') ORDER BY average_wait DESC;

SELECT event, total_waits, time_waited, average_wait FROM (SELECT a.event, a.total_waits, a.time_waited, a.average_wait, ROW_NUMBER() OVER (PARTITION BY a.event ORDER BY average_wait DESC) rnum

          FROM v$session_event a, v$event_name b, v$session_wait_class c

          WHERE a.event_id=b.event_id
          AND b.wait_class#=c.wait_class#
         AND c.wait_class IN ('Application','Concurrency'))
WHERE rnum = 1
ORDER BY average_wait DESC; Received on Wed Jun 22 2011 - 17:44:51 CEST

Original text of this message