Re: Wait Events

On Jun 22, 11:44 am, The Magnet <a..._at_unsu.com> wrote:
> 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;

The view v$session_event will contain information about current sessions only. On a busy system new sessions are being created all the time and others exit ceasing to exist. Session information is rolled up into the system statistics which would go back to instance startup.

HTH -- Mark D Powell -- Received on Thu Jul 07 2011 - 19:04:06 CEST