Re: AWR Sample Report

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