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

From: Ls Cheng <exriscer_at_gmail.com>
Date: Fri, 7 Sep 2012 09:56:51 +0200
Message-ID: <CAJ2-Qb_ex5drb-P=dof0NWjE=rg8i2X_PSxRgOgrw9TPuPNDfg_at_mail.gmail.com>

oops was pasting the wrong script which groups waits into wait classes, this is the one to get the top 5 events which simulates the AWR report

WITH CPU_TIME
AS
(SELECT snap_id, 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.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, end_interval_time, instance_number, event_name, time_waited_micro

            FROM (SELECT s.snap_id,
                         s.instance_number,
                         s.dbid,
                         s.end_interval_time,
                         sy.event_name,
                         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 rango, end_interval_time, instance_number, event, time_seconds   FROM (SELECT end_interval_time, instance_number, event, time_seconds, dense_rank() over (partition by snap_id order by time_seconds desc) rango

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

                       instance_number, stat_name event,
round(value/1000000, 2) time_seconds
                  FROM cpu_time
                 WHERE value is not null
                UNION ALL
                SELECT snap_id, TO_CHAR(TRUNC (end_interval_time,


'&ROUND_FORMAT'), 'mm-dd hh24mi') end_interval_time,

                       instance_number, event_name event,
round(time_waited_micro/1000000, 2) time_seconds
                  FROM wait_time
                 WHERE time_waited_micro is not null))

 WHERE rango <= 5;

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

Received on Fri Sep 07 2012 - 02:56:51 CDT