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.
