2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
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
posted on 2013-03-19 00:52 Oracle和MySQL 阅读(174) 评论(0) 编辑 收藏 举报
