SELECT tim,

gscn,

round(rate),

round((chk16kscn - gscn) / 24 / 3600 / 16 / 1024, 1) "Headroom"

FROM (select tim,

gscn,

rate,

((((to_number(to_char(tim, "YYYY")) - 1988) * 12 * 31 * 24 * 60 * 60) +

((to_number(to_char(tim, "MM")) - 1) * 31 * 24 * 60 * 60) +

(((to_number(to_char(tim, "DD")) - 1)) * 24 * 60 * 60) +

(to_number(to_char(tim, "HH24")) * 60 * 60) +

(to_number(to_char(tim, "MI")) * 60) +

(to_number(to_char(tim, "SS")))) * (16 * 1024)) chk16kscn

from (select FIRST_TIME tim,

FIRST_CHANGE# gscn,

((NEXT_CHANGE# - FIRST_CHANGE#) /

((NEXT_TIME - FIRST_TIME) * 24 * 60 * 60)) rate

from v$archived_log

where (next_time > first_time)))

order by 1, 2;

WITH sysstat AS

(select sn.begin_interval_time begin_interval_time,

sn.end_interval_time end_interval_time,

ss.stat_name stat_name,

ss.value e_value,

lag(ss.value, 1) over(order by ss.snap_id) b_value

from dba_hist_sysstat ss, dba_hist_snapshot sn

where ss.snap_id = sn.snap_id

and ss.dbid = sn.dbid

and ss.instance_number = sn.instance_number

and ss.dbid = (select dbid from v$database)

and ss.instance_number = (select instance_number from v$instance)

and ss.stat_name = "calls to kcmgas")

select to_char(BEGIN_INTERVAL_TIME, "mm/dd/yy_hh24_mi") || to_char(END_INTERVAL_TIME, "_hh24_mi") date_time,

stat_name,

round((e_value - nvl(b_value,0)) / (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))),0) per_sec

from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0;