Oracle SCN天花板排查语句

查询数据库SCN的增速情况.

select * from
(
SELECT SS.SNAP_ID AS SNAP_ID,
TO_CHAR(SN.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS "SNAP_DATE",
SUM(DECODE(STAT_NAME, 'calls to kcmgas', VALUE, 0)) -
LAG(SUM(DECODE(STAT_NAME, 'calls to kcmgas', VALUE, 0)), 1) OVER(ORDER BY SS.SNAP_ID) "KCMGAS",
TRUNC((SUM(DECODE(STAT_NAME, 'calls to kcmgas', VALUE, 0)) -
LAG(SUM(DECODE(STAT_NAME, 'calls to kcmgas', VALUE, 0)), 1)
OVER(ORDER BY SS.SNAP_ID)) /
TRUNC((CAST(SN.END_INTERVAL_TIME AS DATE) -
CAST(SN.BEGIN_INTERVAL_TIME AS DATE)) * 86400)) "KCMGAS PER SEC"
FROM SYS.DBA_HIST_SYSSTAT SS, SYS.DBA_HIST_SNAPSHOT SN
WHERE SS.SNAP_ID = SN.SNAP_ID
AND SS.STAT_NAME IN ('calls to kcmgas')
AND SS.DBID = SN.DBID
AND SS.INSTANCE_NUMBER = SN.INSTANCE_NUMBER
AND SN.INSTANCE_NUMBER = (select instance_number from v$instance)
AND SN.DBID = (select dbid from v$database)
GROUP BY SS.SNAP_ID, SN.BEGIN_INTERVAL_TIME, SN.END_INTERVAL_TIME
)


---这里的“KCMGAS PER SEC”显示增速

定位导致SCN增长过快的语句

select * from 
(
select sess.sid, stat.value, sess.sql_id, vsql.sql_text
from v$sesstat stat, v$session sess, v$sql vsql 
where statistic# = 
(select statistic# from v$sysstat where name ='calls to kcmgas')
and stat.sid = sess.sid 
and sess.sql_id = vsql.sql_id 
) where value>50000;

判断scn Headroom是否回升

set numwidth 17
set pages 1000
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
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;