从awr中获取scn变化趋势的另外一个脚本--来自www.oracledatabase12g.com

来源：互联网发布：北京股商怎么样知乎编辑：程序博客网时间：2024/05/17 07:48

从awr中获取scn变化趋势的另外一个脚本--来自www.oracledatabase12g.com
本文转帖自：
http://www.oracledatabase12g.com/archives/tag/scn-headroom

alter session set nls_date_format='dd-mon-yy';set lines 160 pages 1000 echo off feedback offcol stat_name for a25col date_time for a40col BEGIN_INTERVAL_TIME for a20col END_INTERVAL_TIME for a20prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."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_secfrom sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0/

0 0