Re: AWR Sample Report
Date: Fri, 12 Dec 2008 05:08:00 -0800 (PST)
Message-ID: <fe268718-8bba-46f6-9b46-a0e0486fc61a@m12g2000vbp.googlegroups.com>
On Dec 12, 7:39 am, raja <dextersu..._at_gmail.com> wrote:
> Hi Charles/ Steeve,
>
> Query 1 ( Query given by charles ) :
> select
> to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
> dhse.instance_number,
> time_waited_micro - nvl(lag(time_waited_micro, 1, 0) over (partition
> by dhse.instance_number, dhse.event_name order by dhse.snap_id),0) as
> time_waited,
> total_waits - nvl(lag(total_waits, 1, 0) over (partition by
> dhse.instance_number, dhse.event_name order by dhse.snap_id),0) as
> total_waits
> from dba_hist_snapshot dhs,
> dba_hist_system_event dhse
> where dhs.snap_id = dhse.snap_id
> and dhs.instance_number = dhse.instance_number
> order by 1, 2;
>
> Since i am not able to post those files in our group's thread, i am
> sending those files to ur mail ids.
> Please check these 2 files and tell me your observations.
> Also, another doubt is : whether should be neglect the idle events
> always ? then why are they notified to us...
>
> With Regards,
> Raja.
Raja,
Now that I am able to see the output of the query that I suggested
that you run, I see that I did not tell you to select the
dhse.event_name column, so I am not able to see the wait event names
associated with the various waits, also the sort order probably needs
to be improved. The fixed query should look something like this:
select
dhse.event_name,
to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhse.instance_number,
time_waited_micro - nvl(lag(time_waited_micro, 1, 0) over
(partition
by dhse.instance_number, dhse.event_name order by dhse.snap_id),0) as
time_waited,
total_waits - nvl(lag(total_waits, 1, 0) over (partition by
dhse.instance_number, dhse.event_name order by dhse.snap_id),0) as
total_waits
from dba_hist_snapshot dhs,
dba_hist_system_event dhse
where dhs.snap_id = dhse.snap_id
and dhs.instance_number = dhse.instance_number
order by 2, 4;
The idle wait events do have value, as they tell you to what degree the database server is not causing performance problems (the user took a 2 hour nap, resulting in a SQL*Net Message from Client exceeding 2 hours). Idle waits have a limited value in Statspack and AWR reports (for instance "SQL*Net more data to client" shows 4,642,420 waits, possibly indicating that the clients are requesting a lot of data to be returned which will not fit into a single SDU size per request). However, the idle wait events are very important wait events when examining 10046 traces for sessions, as a 10046 trace is time scoped for a single session.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Dec 12 2008 - 07:08:00 CST