scn_stats.sql

/*
   SCN Headroom check.

     Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru) 
     http://AndreyNikolaev.wordpress.com

     SCN Headroom history, avg daily SCN rate, 
      and intrinsic SCN generation rate for database.

        sqlplus /nolog @scnrates
*/
rem connect / as sysdba
set verify off
set echo off

rem set timing on
prompt
prompt Current SCN Headroom and average complete and internal SCN rates:
col date_ format a16
col scn_headroom format a12
col scn_rate format 999999
col int_scn_rate format 999999
col scn_age format 99999
col db_age format 99999
/* This statement should work even for noarchivelog 9.2 database */

select name dbname,to_char(SYSDATE,'YYYY/MM/DD HH24:MI') DATE_,
   to_char((((
    ((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
    ((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
    (((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +
    (to_number(to_char(sysdate,'HH24'))*60*60) +
    (to_number(to_char(sysdate,'MI'))*60) +
    (to_number(to_char(sysdate,'SS')))
    ) * (16*1024)) - dbms_flashback.get_system_change_number)
   / (16*1024*60*60*24),'9999999.99') scn_headroom, scn_rate, int_scn_rate,
   round(dbms_flashback.get_system_change_number/ (16*1024*60*60*24)) scn_age,
   round(sysdate-created) db_age
   from v$database,
    (select round(sum(s.value/((sysdate-i.startup_time)*86400))) int_scn_rate
     from gv$sysstat s,gv$instance i where s.inst_id=i.inst_id and s.name='calls to kcmgas'),
    (select round((scn_max-scn_min)/(time_max-time_min)/86400) scn_rate from 
       (select distinct time_dp time_min,(SCN_WRP * 4294967290) + SCN_BAS scn_min 
          from sys.smon_scn_time where time_dp in (select min(time_dp) from sys.smon_scn_time)),
       (select distinct time_dp time_max,(SCN_WRP * 4294967290) + SCN_BAS scn_max 
          from sys.smon_scn_time where time_dp in (select max(time_dp) from sys.smon_scn_time)));

prompt History of SCN Headroom and daily averaged SCN rates: 
set pagesize 300
col date__ format a14
col scn_rate format 99999999
col int_scn_rate format 99999999
define FM=DD
rem define FM=HH24

with logstat as  /* total SCN rate (archived logs) */
( select trunc(FIRST_TIME,'&FM') date_ ,
     min(FIRST_TIME) KEEP (DENSE_RANK FIRST ORDER BY  (FIRST_TIME))  as ftime,
     max(FIRST_TIME) KEEP (DENSE_RANK LAST ORDER BY  (FIRST_TIME))  as etime,
     min(FIRST_CHANGE#) KEEP (DENSE_RANK FIRST ORDER BY  (FIRST_TIME))  as fscn,
     max(FIRST_CHANGE#) KEEP (DENSE_RANK LAST ORDER BY  (FIRST_TIME))  as escn,
     round(min(((((
        ((to_number(to_char(FIRST_TIME,'YYYY'))-1988)*12*31*24*60*60) +
        ((to_number(to_char(FIRST_TIME,'MM'))-1)*31*24*60*60) +
        (((to_number(to_char(FIRST_TIME,'DD'))-1))*24*60*60) +
        (to_number(to_char(FIRST_TIME,'HH24'))*60*60) +
        (to_number(to_char(FIRST_TIME,'MI'))*60) +
        (to_number(to_char(FIRST_TIME,'SS')))
        ) * (16*1024)) - FIRST_CHANGE#)
        / (16*1024*60*60*24))),2) min_hdr
from gv$archived_log
where (next_time > first_time)
group by trunc(FIRST_TIME,'&FM'))
, lograte as 
(select date_, decode(etime-ftime,0,0,round((escn-fscn)/((etime-ftime)*24*60*60))) scn_rate,min_hdr from logstat)
, snapstat as     /*  intrinsic SCN rate */
(
  SELECT snap_id,instance_number,begin_interval_time, end_interval_time,stat_name,
          (cast(END_INTERVAL_TIME as date) -cast(BEGIN_INTERVAL_TIME as date))*86400 delta_t,
          VALUE e_value,
          LAG (VALUE, 1) OVER (partition by instance_number,startup_time  ORDER BY snap_id) b_value
  FROM dba_hist_sysstat  join dba_hist_snapshot  using (snap_id,dbid,instance_number)
                   join v$database using  (dbid)
  WHERE stat_name = 'calls to kcmgas')
, inststat as
(select instance_number, trunc(begin_interval_time,'&FM') date_,sum(e_value-b_value)/sum(delta_t) rate
 from snapstat where b_value is not null /* instance restart */
 group by instance_number, trunc(begin_interval_time,'&FM'))
, dbstat as 
(select date_,round(sum(rate)) int_scn_rate  from inststat group by date_)
, smonscntime as      /* total SCN rate (smon_scn_time) */
(select trunc(time_dp,'&FM') date_,
     (max(scn) KEEP (DENSE_RANK LAST  ORDER BY  (time_dp)) - min(scn) KEEP (DENSE_RANK FIRST ORDER BY  (time_dp))) dscn,
     (max(time_dp) KEEP (DENSE_RANK LAST  ORDER BY  (time_dp)) - min(time_dp) KEEP (DENSE_RANK FIRST ORDER BY  (time_dp)) )*(3600*24) dtime, 
     round(min(((((
        ((to_number(to_char(time_dp,'YYYY'))-1988)*12*31*24*60*60) +
        ((to_number(to_char(time_dp,'MM'))-1)*31*24*60*60) +
        (((to_number(to_char(time_dp,'DD'))-1))*24*60*60) +
        (to_number(to_char(time_dp,'HH24'))*60*60) +
        (to_number(to_char(time_dp,'MI'))*60) +
        (to_number(to_char(time_dp,'SS')))
        ) * (16*1024)) - scn)
        / (16*1024*60*60*24))),2) min_hdrs
from sys.smon_scn_time
group by trunc(time_dp,'&FM'))
, smonrate as
(select date_,min_hdrs,decode(dtime,0,0,round(dscn/dtime)) smon_scn_rate from smonscntime)
select to_char(date_,'yyyy/mm/dd hh24') date__,to_char(nvl(min_hdr,min_hdrs),'9999999.99') scn_headroom,nvl(scn_rate,smon_scn_rate) scn_rate,int_scn_rate
 from lograte full outer join smonrate using(date_) full outer join dbstat using(date_) 
order by date_;