Script:挖掘AWR实现查询SCN历史增长走势

AWR中记录了快照时间内calls to kcmgas的统计值，calls to kcmgas的意义在于通过递归调用获得一个新的SCN，该统计值可以看做SCN增长速度的主要依据，通过挖掘AWR可以了解SCN的增长走势，对于我们诊断SCN HEADROOM问题有所帮助：    

alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "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_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/

  示例输出如下， 可以看到最近快照中SCN增速迅速变大：    

DATE_TIME                                STAT_NAME                    PER_SEC
---------------------------------------- ------------------------- ----------
11/27/09_01_00_02_01                     calls to kcmgas                    0
11/27/09_02_01_03_00                     calls to kcmgas                    0
11/27/09_03_00_04_00                     calls to kcmgas                    0
11/27/09_04_00_05_00                     calls to kcmgas                    0
11/27/09_05_00_06_00                     calls to kcmgas                    0
11/27/09_06_00_07_00                     calls to kcmgas                    0
11/27/09_07_00_08_00                     calls to kcmgas                    0
11/27/09_08_00_09_00                     calls to kcmgas                    0
11/27/09_09_00_10_00                     calls to kcmgas                    0
11/27/09_10_00_11_00                     calls to kcmgas                    0
11/27/09_11_00_12_00                     calls to kcmgas                    0
11/27/09_12_00_13_00                     calls to kcmgas                    0
11/27/09_13_00_14_00                     calls to kcmgas                    0
11/27/09_14_00_15_00                     calls to kcmgas                    0
11/27/09_15_00_16_00                     calls to kcmgas                    0
11/27/09_16_00_17_00                     calls to kcmgas                    0
11/27/09_17_00_18_00                     calls to kcmgas                    0
11/27/09_18_00_19_00                     calls to kcmgas                    0
11/27/09_19_00_20_00                     calls to kcmgas                    0
11/27/09_20_00_21_00                     calls to kcmgas                    0
11/27/09_21_00_22_00                     calls to kcmgas                    1
11/27/09_22_00_23_00                     calls to kcmgas                    0
11/27/09_23_00_00_00                     calls to kcmgas                    0
11/28/09_00_00_01_00                     calls to kcmgas                    0
11/28/09_01_00_02_00                     calls to kcmgas                    0
11/28/09_02_00_03_00                     calls to kcmgas                    0
11/28/09_03_00_04_00                     calls to kcmgas                    0
11/28/09_04_00_05_00                     calls to kcmgas                    0
11/28/09_05_00_06_00                     calls to kcmgas                    1
11/28/09_06_00_07_00                     calls to kcmgas                    0
11/28/09_07_00_08_00                     calls to kcmgas                    0
11/28/09_08_00_09_00                     calls to kcmgas                    0
11/28/09_09_00_10_00                     calls to kcmgas                    0
11/28/09_10_00_11_00                     calls to kcmgas                    1
11/28/09_11_00_12_00                     calls to kcmgas                    0
11/28/09_12_00_13_00                     calls to kcmgas                    0
11/28/09_13_00_14_00                     calls to kcmgas                    0
11/28/09_14_00_15_00                     calls to kcmgas                    0
11/28/09_15_00_16_00                     calls to kcmgas                    0
11/28/09_16_00_17_00                     calls to kcmgas                    0
11/28/09_17_00_18_00                     calls to kcmgas                    0
11/28/09_18_00_19_00                     calls to kcmgas                    0
11/28/09_19_00_20_00                     calls to kcmgas                    0
11/28/09_20_00_21_00                     calls to kcmgas                    0
11/28/09_21_00_22_00                     calls to kcmgas                    0
11/28/09_22_00_23_00                     calls to kcmgas                    1
11/28/09_23_00_00_00                     calls to kcmgas                    0
11/29/09_00_00_01_00                     calls to kcmgas                    0
11/29/09_01_00_02_00                     calls to kcmgas                    0
11/29/09_02_00_03_00                     calls to kcmgas                    0
11/29/09_03_00_04_00                     calls to kcmgas                    0
11/29/09_04_00_05_00                     calls to kcmgas                    0
11/29/09_05_00_06_00                     calls to kcmgas                    1
11/29/09_06_00_07_00                     calls to kcmgas                    0
11/29/09_07_00_08_00                     calls to kcmgas                    0
11/29/09_08_00_09_00                     calls to kcmgas                    0
11/29/09_09_00_10_00                     calls to kcmgas                    0
11/29/09_10_00_11_00                     calls to kcmgas                    1
11/29/09_11_00_12_00                     calls to kcmgas                    0
11/29/09_12_00_13_00                     calls to kcmgas                    0
11/29/09_13_00_14_00                     calls to kcmgas                    0
11/29/09_14_00_15_00                     calls to kcmgas                    1
11/29/09_15_00_16_00                     calls to kcmgas                    0
11/29/09_16_00_17_00                     calls to kcmgas                    0
11/29/09_17_00_18_00                     calls to kcmgas                    0
11/29/09_18_00_19_00                     calls to kcmgas                    0
11/29/09_19_00_20_00                     calls to kcmgas                    0
11/29/09_20_00_21_00                     calls to kcmgas                    0
11/29/09_21_00_22_00                     calls to kcmgas                    0
11/29/09_22_00_23_00                     calls to kcmgas                    1
11/29/09_23_00_00_00                     calls to kcmgas                    0
11/30/09_00_00_01_00                     calls to kcmgas                    0
11/30/09_01_00_02_00                     calls to kcmgas                    0
11/30/09_02_00_03_00                     calls to kcmgas                    0
11/30/09_03_00_04_00                     calls to kcmgas                    0
11/30/09_04_00_05_00                     calls to kcmgas                    0
11/30/09_05_00_06_00                     calls to kcmgas                    0
11/30/09_06_00_07_00                     calls to kcmgas                    0
11/30/09_07_00_08_00                     calls to kcmgas                    0
11/30/09_08_00_09_00                     calls to kcmgas                    0
11/30/09_09_00_10_00                     calls to kcmgas                    0
11/30/09_10_00_11_00                     calls to kcmgas                    0
11/30/09_11_00_12_00                     calls to kcmgas                    0
11/30/09_12_00_13_00                     calls to kcmgas                    0
11/30/09_13_00_14_00                     calls to kcmgas                    0
11/30/09_14_00_15_00                     calls to kcmgas                    0
11/30/09_15_00_16_00                     calls to kcmgas                    0
11/30/09_16_00_17_00                     calls to kcmgas                    0
11/30/09_17_00_18_00                     calls to kcmgas                    0
11/30/09_18_00_19_00                     calls to kcmgas                    0
11/30/09_19_00_20_00                     calls to kcmgas                    0
11/30/09_20_00_21_00                     calls to kcmgas                    0
11/30/09_21_00_22_00                     calls to kcmgas                    1
11/30/09_22_00_23_00                     calls to kcmgas                    1
11/30/09_23_00_00_00                     calls to kcmgas                    0
12/01/09_00_00_01_00                     calls to kcmgas                    0
12/01/09_01_00_02_01                     calls to kcmgas                    0
12/01/09_02_01_03_00                     calls to kcmgas                    0
12/01/09_03_00_04_00                     calls to kcmgas                    0
12/01/09_04_00_05_00                     calls to kcmgas                    0
12/01/09_05_00_06_00                     calls to kcmgas                    0
12/01/09_06_00_07_00                     calls to kcmgas                    0
12/01/09_07_00_08_00                     calls to kcmgas                    0
12/01/09_08_00_09_00                     calls to kcmgas                    0
12/01/09_09_00_10_00                     calls to kcmgas                    0
12/01/09_10_00_11_00                     calls to kcmgas                    0
12/01/09_11_00_12_00                     calls to kcmgas                    0
12/01/09_12_00_13_00                     calls to kcmgas                    0
12/01/09_13_00_14_00                     calls to kcmgas                    0
12/01/09_14_00_15_00                     calls to kcmgas                    0
12/01/09_15_00_16_00                     calls to kcmgas                    0
12/01/09_16_00_17_00                     calls to kcmgas                    0
12/01/09_17_00_18_00                     calls to kcmgas                    0
12/01/09_18_00_19_00                     calls to kcmgas                    0
12/01/09_19_00_20_00                     calls to kcmgas                    0
12/01/09_20_00_21_00                     calls to kcmgas                    0
12/01/09_21_00_22_00                     calls to kcmgas                    0
12/01/09_22_00_23_00                     calls to kcmgas                    1
12/01/09_23_00_00_00                     calls to kcmgas                    0
12/02/09_00_00_01_00                     calls to kcmgas                    0
12/02/09_01_00_02_00                     calls to kcmgas                    0
12/02/09_02_00_03_00                     calls to kcmgas                    0
12/02/09_03_00_04_00                     calls to kcmgas                    0
12/02/09_04_00_05_00                     calls to kcmgas                    0
12/02/09_05_00_06_00                     calls to kcmgas                    0
12/02/09_06_00_07_00                     calls to kcmgas                    0
12/02/09_07_00_08_00                     calls to kcmgas                    0
12/02/09_08_00_09_00                     calls to kcmgas                    0
12/02/09_09_00_10_00                     calls to kcmgas                    0
12/02/09_10_00_11_00                     calls to kcmgas                    0
12/02/09_11_00_12_00                     calls to kcmgas                    0
12/02/09_12_00_13_00                     calls to kcmgas                    0
12/02/09_13_00_14_00                     calls to kcmgas                    0
12/02/09_14_00_15_00                     calls to kcmgas                    0
12/02/09_15_00_16_00                     calls to kcmgas                    0
12/02/09_16_00_17_00                     calls to kcmgas                    0
12/02/09_17_00_18_00                     calls to kcmgas                    0
12/02/09_18_00_19_00                     calls to kcmgas                    0
12/02/09_19_00_20_00                     calls to kcmgas                    0
12/02/09_20_00_21_00                     calls to kcmgas                    0
12/02/09_21_00_22_00                     calls to kcmgas                    1
12/02/09_22_00_23_00                     calls to kcmgas                    1
12/02/09_23_00_00_00                     calls to kcmgas                    0
12/03/09_00_00_01_00                     calls to kcmgas                    1
12/03/09_01_00_02_00                     calls to kcmgas                    0
12/03/09_02_00_03_00                     calls to kcmgas                    0
12/03/09_03_00_04_00                     calls to kcmgas                    0
12/03/09_04_00_05_00                     calls to kcmgas                    0
12/03/09_05_00_06_00                     calls to kcmgas                    0
12/03/09_06_00_07_00                     calls to kcmgas                    0
12/03/09_07_00_08_00                     calls to kcmgas                    0
12/03/09_08_00_09_00                     calls to kcmgas                    0
12/03/09_09_00_10_00                     calls to kcmgas                    0
12/03/09_10_00_11_00                     calls to kcmgas                    0
12/03/09_11_00_12_00                     calls to kcmgas                    0
12/03/09_12_00_13_00                     calls to kcmgas                    0
12/03/09_13_00_14_00                     calls to kcmgas                    0
12/03/09_14_00_15_00                     calls to kcmgas                    0
12/03/09_15_00_16_00                     calls to kcmgas                    0
12/03/09_16_00_17_00                     calls to kcmgas                    0
12/03/09_17_00_18_00                     calls to kcmgas                    0
12/03/09_18_00_19_00                     calls to kcmgas                    0
12/03/09_19_00_20_00                     calls to kcmgas                    0
12/03/09_20_00_21_00                     calls to kcmgas                    0
12/03/09_21_00_22_00                     calls to kcmgas                    0
12/03/09_22_00_23_00                     calls to kcmgas                    1
12/03/09_23_00_00_00                     calls to kcmgas                    0
12/04/09_00_00_01_00                     calls to kcmgas                    1
12/04/09_01_00_02_00                     calls to kcmgas                    0
12/04/09_02_00_03_00                     calls to kcmgas                    0
12/04/09_03_00_04_00                     calls to kcmgas                    0
12/04/09_04_00_05_00                     calls to kcmgas                    0
12/04/09_05_00_06_00                     calls to kcmgas                    0
12/04/09_06_00_07_00                     calls to kcmgas                    0
12/04/09_07_00_08_00                     calls to kcmgas                    0
12/04/09_08_00_09_00                     calls to kcmgas                    0
12/04/09_09_00_10_00                     calls to kcmgas                    0
12/04/09_10_00_11_00                     calls to kcmgas                    0
12/04/09_11_00_12_00                     calls to kcmgas                    0
12/04/09_12_00_13_00                     calls to kcmgas                    0
12/04/09_13_00_14_00                     calls to kcmgas                    0
12/04/09_14_00_15_00                     calls to kcmgas                    0
12/04/09_15_00_16_00                     calls to kcmgas                    0
12/04/09_16_00_17_00                     calls to kcmgas                    0
12/04/09_17_00_18_00                     calls to kcmgas                    0
12/04/09_18_00_19_00                     calls to kcmgas                    0
12/04/09_19_00_20_00                     calls to kcmgas                    0
12/04/09_20_00_21_00                     calls to kcmgas                    0
12/04/09_21_00_22_00                     calls to kcmgas                    1
12/04/09_22_00_23_00                     calls to kcmgas                    0
12/04/09_23_00_00_00                     calls to kcmgas                    0
12/05/09_00_00_01_00                     calls to kcmgas                    1
12/05/09_01_00_02_00                     calls to kcmgas                    0
12/05/09_02_00_03_00                     calls to kcmgas                    0
12/05/09_03_00_04_00                     calls to kcmgas                    0
12/05/09_04_00_05_00                     calls to kcmgas                    0
12/05/09_05_00_06_00                     calls to kcmgas                    1
12/05/09_06_00_07_00                     calls to kcmgas                    0
12/05/09_07_00_08_00                     calls to kcmgas                    0
12/05/09_08_00_09_00                     calls to kcmgas                    0
12/05/09_09_00_10_00                     calls to kcmgas                    0
12/05/09_10_00_11_00                     calls to kcmgas                    1
12/05/09_12_00_12_37                     calls to kcmgas                    9
12/05/09_12_37_12_38                     calls to kcmgas                  581