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

From: Ls Cheng <exriscer_at_gmail.com>
Date: Fri, 7 Sep 2012 09:31:32 +0200
Message-ID: <CAJ2-Qb9NC4=+U5LGe3BWXdBW+0iQOji88CtKnNgbzYi9+pcibw_at_mail.gmail.com>

Hi Petr

You are right, ASH probably misses some critical waits.

I do have another script though, usually I run both scripts, one to get more detail analysis (ASH data) and the other to get the load tendency which is the following, I suggest ASH first because it is simple query and easy to understand and the estimates are not too bad.

WITH CPU_TIME
AS
(SELECT snap_id, begin_interval_time, end_interval_time, instance_number, stat_id, stat_name, value

            FROM (SELECT /*+ leading(s,sn,sy) */
                         s.snap_id,
                         s.instance_number,
                         s.dbid,
                         s.begin_interval_time,
                         s.end_interval_time,
                         sy.stat_id,
                         sy.stat_name,
                         CASE
                            WHEN s.begin_interval_time = s.startup_time
                            THEN sy.value
                            ELSE sy.value - LAG (sy.value, 1)
                                            OVER (PARTITION BY
                                                  sy.stat_id,
sy.instance_number,
                                                  sy.dbid, s.startup_time
                                            ORDER BY sy.snap_id)
                         END value
                    FROM dba_hist_snapshot s,
                         dba_hist_sys_time_model sy
                   WHERE s.dbid = sy.dbid
                     AND s.instance_number = sy.instance_number
                     AND s.snap_id = sy.snap_id
                     AND s.instance_number = :inst_id
                     AND s.end_interval_time > TO_TIMESTAMP (:start_time,


'yyyymmdd hh24mi')

                     AND s.end_interval_time < TO_TIMESTAMP (:end_time,


'yyyymmdd hh24mi')

                     -- this ensures hourly data are used
                     AND to_char(trunc(s.end_interval_time, 'mi'), 'mi') =


'00'

                     AND sy.stat_name= 'DB CPU')),

WAIT_TIME
AS
(SELECT snap_id, begin_interval_time, end_interval_time, instance_number, wait_class, time_waited_micro

            FROM (SELECT s.snap_id,
                         s.instance_number,
                         s.dbid,
                         s.begin_interval_time,
                         s.end_interval_time,
                         sy.wait_class,
                         CASE
                            WHEN s.begin_interval_time = s.startup_time
                            THEN sy.time_waited_micro
                            ELSE sy.time_waited_micro - LAG
(sy.time_waited_micro, 1)
                                                        OVER (PARTITION BY
                                                              sy.event_id,
sy.instance_number,
                                                              sy.dbid,
s.startup_time
                                                        ORDER BY sy.snap_id)
                         END time_waited_micro
                    FROM dba_hist_snapshot s,
                         dba_hist_system_event sy
                   WHERE s.dbid = sy.dbid
                     AND s.instance_number = sy.instance_number
                     AND s.snap_id = sy.snap_id
                     AND s.instance_number = :inst_id
                     AND s.end_interval_time > TO_TIMESTAMP (:start_time,


'yyyymmdd hh24mi')

                     AND s.end_interval_time < TO_TIMESTAMP (:end_time,


'yyyymmdd hh24mi')

                     AND sy.wait_class != 'Idle'
                     -- this ensures hourly data are used
                     AND to_char(trunc(s.end_interval_time, 'mi'), 'mi') =

'00'))

SELECT end_interval_time, instance_number, wait_class, sum(elapsed_t) avg_sess
  FROM (SELECT end_interval_time, instance_number, wait_class, time_seconds/ela elapsed_t

          FROM (SELECT snap_id, TO_CHAR(TRUNC (end_interval_time, 'mi'),
'mm-dd hh24mi') end_interval_time,

                       instance_number, stat_name wait_class,
round(value/1000000, 2) time_seconds,
                       (cast(end_interval_time as date) -
cast(begin_interval_time as date)) * 24 * 3600 ela
                  FROM cpu_time
                 WHERE value is not null
                UNION ALL
                SELECT snap_id, TO_CHAR(TRUNC (end_interval_time, 'mi'),


'mm-dd hh24mi') end_interval_time,

                       instance_number, wait_class,
round(time_waited_micro/1000000, 2) time_seconds,
                       (cast(end_interval_time as date) -
cast(begin_interval_time as date)) * 24 * 3600 ela
                  FROM wait_time
                 WHERE time_waited_micro is not null))

group by end_interval_time, instance_number, wait_class order by end_interval_time, wait_class

Thanks

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

Received on Fri Sep 07 2012 - 02:31:32 CDT