Useful AWR comparision stats scripts

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'

set pages 0
set feedback off

spool load_profile.txt
select 'date_time, redo_size, sess_l_reads, blk_change, phy_reads, phy_writes, user_calls, parse_count_tot, parse_count_hard, sort_disk, logons, execute_coun
t, trans' from dual;

select
date_time ||','||
sum(case WHEN stat_name='redo size' then round((e_val - b_val)/sec,2) else null end) ||','||
sum(case WHEN stat_name='session logical reads' then round((e_val - b_val)/sec,2) else null end)  ||','||
sum(case WHEN stat_name='db block changes' then round((e_val - b_val)/sec,2) else null end) ||','||
sum(case WHEN stat_name='physical reads' then round((e_val - b_val)/sec,2) else null end)  ||','||
sum(case WHEN stat_name='physical writes' then round((e_val - b_val)/sec,2) else null end) ||','||
sum(case WHEN stat_name='user calls' then round((e_val - b_val)/sec,2) else null end) ||','||
sum(case WHEN stat_name='parse count (total)' then round((e_val - b_val)/sec,2) else null end) ||','||
sum(case WHEN stat_name='parse count (hard)' then round((e_val - b_val)/sec,2) else null end) ||','||
sum(case WHEN stat_name='sorts (disk)' then round((e_val - b_val)/sec,2) else null end) ||','||
sum(case WHEN stat_name='logons cumulative' then round((e_val - b_val)/sec,2) else null end) ||','||
sum(case WHEN stat_name='execute count' then round((e_val - b_val)/sec,2) else null end)  ||','||
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) between sysdate -100 and sysdate
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
;

spool off

-------------------------

event_reponse.sql

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

Accept From_date prompt "Enter the Start date in DD-mm-YY Format:"

Accept To_date prompt "Enter the End date in DD-mm-YY Format:"

set pages 0

set linesize 300

Spool &Spoolfilename

select

        to_char(sn.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24_mi')|| to_char(sn.END_INTERVAL_TIME,'_hh24_mi') ||','||

        e.event_name ||','||

        nvl(e.total_waits - nvl(b.total_waits,0),0)   ||','||

        (e.total_waits - nvl(b.total_waits,0)) /

        (extract( day from (sn.end_interval_time-sn.begin_interval_time) )*24*60*60+

        extract( hour from (sn.end_interval_time-sn.begin_interval_time) )*60*60+

        extract( minute from (sn.end_interval_time-sn.begin_interval_time) )*60+

        extract( second from (sn.end_interval_time-sn.begin_interval_time))) ||','||

        100 * (e.total_timeouts - nvl(b.total_timeouts,0)) /

        (e.total_waits - nvl(b.total_waits,0)) ||','||

        (e.time_waited_micro -  nvl(b.time_waited_micro,0)) / 1000000 ||','||

        ((e.time_waited_micro -  nvl(b.time_waited_micro,0)) / 1000) / (e.total_waits - nvl(b.total_waits,0)) ||','||

        e.wait_class                 ||','

        from dba_hist_system_event b,

        dba_hist_system_event e,

        dba_hist_snapshot sn

        where

                trunc(sn.begin_interval_time)   between '&From_Date' and '&To_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.event_id(+)               = e.event_id

                and e.total_waits               > nvl(b.total_waits,0)

                and e.wait_class                != 'Idle'

--                and e.event_name='Backup: sbtwrite2'

--                and e.event_name='read by other session'

--                and e.event_name='db file scattered read'

                and e.event_name='ASM file metadata operation'

        order by to_char(sn.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24_mi')|| to_char(sn.END_INTERVAL_TIME,'_hh24_mi')

;

spool off