scn_check10.sql

/*  SCN internal rate check for Oracle 10g and higher.

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

sqlplus /nolog @scn_check10
*/
connect / as sysdba
set pagesize 300
col date__ format a14
col status format a14
col max_int_scn_rate format 99999999
col avg_int_scn_rate format 99999999

with 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'
and begin_interval_time>trunc(sysdate)-30)
, inststat as
(select instance_number, trunc(begin_interval_time,'DD') 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,'DD'))
, dbstat as
(select date_,round(sum(rate)) int_scn_rate from inststat group by date_)
,scn_rates as
(select round(avg(int_scn_rate)) avg_int_scn_rate, max(int_scn_rate) max_int_scn_rate
from dbstat)
select name dbname,to_char(SYSDATE,'YYYY/MM/DD HH24:MI') DATE_,avg_int_scn_rate,max_int_scn_rate,
(case when max_int_scn_rate > 15*1024 then 'Alert' else 'Normal' end) status
from v$database,scn_rates;