查询数据库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;