Re: AWR report generator for past periods (with graphs)?
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Petr Novak <Petr.Novak@xxxxxxxxxxxx>
- Date: Fri, 7 Sep 2012 09:31:32 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
