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
转载于:https://blog.51cto.com/maclean/1278474