AWR Trending (Load Profile statistics)

Following query can be used on 10g for trending of Load Profile statistics. You just need to pass the date and it will print the hourly (or whatever interval you have set) physical reads/sec, physical writes/sec etc. statistics.

--alter session set nls_date_format='dd-mon-yy';

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
;

Sample output:

Enter value for date: 02-aug-09
old 32: trunc(sn.begin_interval_time) ='&Date' and
new 32: trunc(sn.begin_interval_time) ='02-aug-09' and

DATE_TIME REDO_SIZE SESS_L_READS BLK_CHANGE PHY_READS PHY_WRITES USER_CALLS LOGONS EXECUTE_COUNT TRANS
-------------------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ------------- ----------
08/02/09_00_00_01_00 15765.21 174.27 117.67 1.22 2.32 1.53 .05 6.43 .15
08/02/09_01_00_02_00 812.08 40.25 4 .03 .35 1.52 .05 3.35 .15
08/02/09_02_00_03_00 899.51 40.45 4.66 .05 .36 1.51 .05 3.31 .15
08/02/09_03_00_04_00 756.55 38.48 3.7 .03 .36 1.51 .05 3.21 .15
08/02/09_04_00_05_00 787.75 38.62 3.84 .03 .35 1.9 .07 3.48 .15
08/02/09_05_00_06_00 1171.49 289.58 41.65 .22 .49 1.69 .06 15.19 .16
08/02/09_06_00_07_00 11280.69 912.41 279.12 2.85 1.89 1.6 .06 13.59 .23
08/02/09_07_00_08_00 793.5 37.21 3.7 .04 .36 1.94 .07 3.45 .14
08/02/09_08_00_09_00 972.47 40.36 4.51 .04 .36 1.77 .06 3.48 .15
08/02/09_09_00_10_00 799.36 42.63 3.97 .03 .35 1.83 .06 3.41 .15
08/02/09_10_00_11_00 2206.13 430.48 90.97 .84 .63 1.69 .06 16.9 .18
08/02/09_11_00_12_00 743.9 36.13 3.47 .03 .35 1.49 .06 3.4 .14
08/02/09_12_00_13_00 845.58 39.75 4.14 .03 .35 1.89 .06 3.54 .15
08/02/09_13_00_14_00 826.71 40.49 3.95 .03 .35 1.69 .06 3.35 .15
08/02/09_14_00_15_00 1682.08 317.59 59.09 .06 .54 1.91 .06 15.66 .17
08/02/09_15_00_16_00 902.46 41.58 4.61 .03 .34 1.69 .06 3.44 .15
08/02/09_16_00_17_00 725.42 37.65 3.31 .03 .35 1.68 .06 3.38 .14
08/02/09_17_00_18_00 892.22 41.14 4.53 .03 .34 1.69 .06 3.42 .16
08/02/09_18_00_19_00 1866.91 340.55 66.31 .06 .56 1.69 .06 16.04 .17
08/02/09_19_00_20_00 907.04 41.34 4.42 .07 .39 2.47 .08 3.9 .16
08/02/09_20_00_21_00 880.26 38.45 3.84 .04 .39 1.71 .06 3.37 .14
08/02/09_21_00_22_00 796.58 39.68 3.91 .03 .35 1.69 .06 3.38 .15
08/02/09_22_00_23_00 3413.67 424.14 94.61 .2 .78 1.68 .06 16.68 .18
08/02/09_23_00_00_00 1345.72 52.08 8.42 .04 .36 1.53 .05 5.9 .43

24 rows selected.


I hope, it will be helpful for someone.