AW: AWR report generator for past periods (with graphs)?

From: Petr Novak <Petr.Novak_at_trivadis.com>
Date: Fri, 7 Sep 2012 05:48:35 +0000
Message-ID: <09011014EB621E4CBC2536B62A1B64075ACF93BC_at_smxc002.trivadis.com>

Hallo Cheng

I would not use dba_hist_active_sess_history for load overview. Even if you would take wait time into account. ASH make samples , which are biased, because short time waits (latches,cache buffer chains) are not so often catched as longer waits (locks). I would suggest query based on DBA_HIST_SYSTEM_EVENT (Waits) and DBA_HIST_SYSSTAT (for CPU - stat_name='CPU used by this session').

Best Regards,
Petr

---

Von: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org]&quot; im Auftrag von &quot;Ls Cheng [exriscer_at_gmail.com] Gesendet: Donnerstag, 6. September 2012 23:35 An: Christopher.Taylor2_at_parallon.net
Cc: oracle-l_at_freelists.org
Betreff: Re: AWR report generator for past periods (with graphs)?

I use this sql

select TO_CHAR(TRUNC(sample_time, 'hh'), 'mm-dd hh24') sample_timestamp,

       state,
       count(*)/360 avg_active_sess
  from
       (select sample_time, sample_id,
               CASE
                   WHEN session_state = 'ON CPU' THEN 'CPU'
                   WHEN session_state = 'WAITING' THEN wait_class
                   ELSE 'Misc Waits'
               END state
         from dba_hist_active_sess_history
        where session_type IN ('FOREGROUND')
          and sample_time > TO_DATE (:start_time, 'yyyymmdd hh24mi')
          and sample_time < TO_DATE (:end_time, 'yyyymmdd hh24mi')
       )

group by TO_CHAR(TRUNC(sample_time, 'hh'), 'mm-dd hh24'), state order by TO_CHAR(TRUNC(sample_time, 'hh'), 'mm-dd hh24'), state;

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 07 2012 - 00:48:35 CDT