AWR Load Trending Report

AWR Load Trending Report

AWR Load report

set lines 130 pages 1000
col stat_name for a25
col BEGIN_INTERVAL_TIME for a25
col END_INTERVAL_TIME for a25
col redo_size for '999,999,990.99'
col sess_l_reads for '999,999,990.99'
col blk_change for '999,999,990.99'
col phy_reads for '999,999,990.99'
col phy_writes for '999,999,990.99'
col user_calls for '999,999,990.99'
col parse_count_tot for '999,999,990.99'
col parse_count_hard for '999,999,990.99'
col sort_disk for '999,999,990.99'
col logons for '999,999,990.99'
col execute_count for '999,999,990.99'
col trans for '999,999,990.99'

select
date_time,
sum(case   WHEN stat_name='redo size' then round((e_val - b_val)/sec,2) else null end)              redo_size,
sum(case   WHEN stat_name='session logical reads' then round((e_val - b_val)/sec,2) else null end)  sess_l_reads,
sum(case   WHEN stat_name='db block changes' then round((e_val - b_val)/sec,2) else null end)       blk_change,
sum(case   WHEN stat_name='physical reads' then round((e_val - b_val)/sec,2) else null end)         phy_reads,
sum(case   WHEN stat_name='physical writes' then round((e_val - b_val)/sec,2) else null end)        phy_writes,
sum(case   WHEN stat_name='user calls' then round((e_val - b_val)/sec,2) else null end)             user_calls,
--sum(case WHEN stat_name='parse count (total)' then round((e_val - b_val)/sec,2) else null end)    parse_count_tot,
--sum(case WHEN stat_name='parse count (hard)' then round((e_val - b_val)/sec,2) else null end)     parse_count_hard,
--sum(case WHEN stat_name='sorts (disk)' then round((e_val - b_val)/sec,2) else null end)           sort_disk,
sum(case   WHEN stat_name='logons cumulative' then round((e_val - b_val)/sec,2) else null end)      logons,
sum(case   WHEN stat_name='execute count' then round((e_val - b_val)/sec,2) else null end)          execute_count,
round((sum(case WHEN stat_name='user commits' then (e_val - b_val)/sec else null end) +
sum(case WHEN stat_name='user rollbacks' then (e_val - b_val)/sec else null end)),2) trans
from
(
select
to_char(sn.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24_mi')|| to_char(sn.END_INTERVAL_TIME,'_hh24_mi') Date_Time,
b.stat_name stat_name,
e.value e_val,
b.value b_val,
(extract( day from (end_interval_time-begin_interval_time) )*24*60*60+
extract( hour from (end_interval_time-begin_interval_time) )*60*60+
extract( minute from (end_interval_time-begin_interval_time) )*60+
extract( second from (end_interval_time-begin_interval_time)) ) sec
FROM
dba_hist_sysstat b,
dba_hist_sysstat e,
dba_hist_snapshot sn
where
trunc(sn.begin_interval_time) ='&Date' and
b.snap_id(+) = e.snap_id-1
and e.snap_id = sn.snap_id
and b.dbid(+) = e.dbid
and e.dbid = (select dbid from v$database)
and sn.dbid = (select dbid from v$database)
and b.instance_number(+) = e.instance_number
and e.instance_number = (select instance_number from v$instance)
and sn.instance_number = (select instance_number from v$instance)
and b.instance_number(+) = e.instance_number
and b.stat_name = e.stat_name
and b.stat_name in (
'redo size',
'session logical reads',
'db block changes',
'physical reads',
'physical writes',
'user calls',
'parse count (total)',
'parse count (hard)',
'sorts (disk)',
'logons cumulative',
'execute count',
'transactions',
'user commits',
'user rollbacks'
)
)
group by date_time
Order by date_time
;


Enter value for date: 13-jan-12
old  32: trunc(sn.begin_interval_time) ='&Date' and
new  32: trunc(sn.begin_interval_time) ='13-jan-12' and

DATE_TIME             REDO_SIZE SESS_L_READS BLK_CHANGE  PHY_READS PHY_WRITES USER_CALLS     LOGONS EXECUTE_COUNT      TRANS
-------------------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ------------- ----------
01/13/12_00_00_01_00   10226.85       120.02      68.66       2.69       1.55        .07        .02          4.44        .02
01/13/12_01_00_02_00     495.21         7.27       1.36         .1        .23        .07        .02          1.07        .01
01/13/12_02_00_03_00     480.02         5.44        1.2        .03        .22        .07        .02           .88        .01
01/13/12_03_00_04_00     417.28         5.71       1.02        .02        .21        .07        .02           .88        .01
01/13/12_04_00_05_00     413.48         5.25        .99        .01        .19        .07        .02           .86        .01
01/13/12_05_00_06_00     405.08         5.32         .9          0         .2        .07        .02           .78        .01
01/13/12_06_00_07_00     406.53         5.35        .91          0        .19        .07        .02            .8        .01
01/13/12_07_00_08_00     397.37         4.84        .89          0        .18        .07        .02           .79        .01
01/13/12_08_00_09_00      458.1         5.46       1.02          0         .2        .07        .02           .79        .01
01/13/12_09_00_10_00     384.42         4.72        .85          0        .19        .06        .02           .77        .01
01/13/12_10_00_11_00     400.58         6.78        .94        .08        .18        .07        .02           .87        .01
01/13/12_11_00_12_00     434.46         6.46       1.02        .19         .2        .06        .02            .8        .01

12 rows selected.