87 积分	0 好友	5 主题

发消息

[SQL调优] ORACLE SCN增长十分快

1^#

发表于 2014-5-5 14:27:33 | 查看: 2673| 回复: 0

本帖最后由路人呵呵于 2014-5-5 15:22 编辑

有一接口数据库的SCN增长太快了这样正常么,需要打上关于SCN的补丁还是升级数据库?
与接口数据库有大量数据交换的数据仓库SCN增长正常吗?

环境介绍 :
接口服务器
数据库版本信息
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Productio
NLSRTL Version 10.2.0.1.0 - Production
操作系统是 aix 6.1

数据仓库数据库版本信息
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
操作系统是 aix 6.1

主要处理业务
接口服务器(132.77.127.78)的脚本按照接口协议每天从指定FTP下载各种数据文件(主要是几个xx详单(三千万数据量),其它详单一百万不等)然后用sqlload导入接口数据数据库, 数据仓库通过database link 从接口数据库获取数据进行加工处理。这两台数据库与其它数据库也存在有database link,但与其它数据交换量少。

以下是在接口服务器的诊断

1 SCN 健康检查
ScnHealthCheck
--------------------------------------------------------------
Current Date: 2014/05/04 17:33:52
Current SCN:  13769590944337
Version:    10.2.0.1.0
--------------------------------------------------------------
Result: A - SCN Headroom is good
Apply the latest recommended patches
based on your maintenance schedule
AND set _external_scn_rejection_threshold_hours=24 after apply.
For further information review MOS document id 1393363.1
--------------------------------------------------------------

2 在接口服务器(132.77.127.78) 查询scn变化
alter session set nls_date_format='DD/Mon/YYYY 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;
      TIM                            GSCN          ROUND(RATE)    Headroom
05/04/2014 04:03:05 13,769,555,678,154 94          72
05/04/2014 11:01:34    13,769,558,043,533 1,662 72
05/04/2014 11:06:41 13,769,558,553,802 1,711 72
05/04/2014 11:11:41    13,769,559,067,209 1,695 72
05/04/2014 11:16:44    13,769,559,580,865 1,642 72
05/04/2014 11:21:55    13,769,560,091,629 1,684 72
05/04/2014 11:27:00    13,769,560,605,127 1,085 72
05/04/2014 11:40:42    13,769,561,496,889 1,677 72
05/04/2014 11:50:29    13,769,562,481,559 1,277 72
05/04/2014 12:02:21    13,769,563,390,544 1,640 72
05/04/2014 12:08:59    13,769,564,043,276 1,677 72
05/04/2014 12:15:29    13,769,564,697,127 1,779 72
05/04/2014 12:21:37    13,769,565,351,779 1,844 72
05/04/2014 12:27:30    13,769,566,002,708 1,793 72
05/04/2014 12:33:35    13,769,566,657,188 1,798 72
05/04/2014 12:39:39    13,769,567,311,841 1,789 72
05/04/2014 12:45:43    13,769,567,962,864 1,826 72
05/04/2014 12:51:41    13,769,568,616,568 1,794 72
05/04/2014 12:57:46    13,769,569,271,466 1,814 72
05/04/2014 13:03:45    13,769,569,922,556 1,677 72
05/04/2014 13:10:15    13,769,570,576,436 1,710 72
05/04/2014 13:16:38    13,769,571,231,207 1,682 72
05/04/2014 13:23:05    13,769,571,882,145 1,743 72
05/04/2014 13:29:20    13,769,572,535,873 1,687 72
05/04/2014 13:35:47    13,769,573,188,881 1,897 72
05/04/2014 13:41:23    13,769,573,826,408 1,949 72
05/04/2014 13:46:50    13,769,574,463,822 1,801 72
05/04/2014 13:53:03    13,769,575,135,636 1,678 72
05/04/2014 13:59:59       13,769,575,833,763 1,426 72
05/04/2014 14:09:48    13,769,576,673,812 1,386 72
05/04/2014 14:18:37    13,769,577,406,911 1,555 72
05/04/2014 14:27:04    13,769,578,195,258 1,341 72
05/04/2014 14:38:23       13,769,579,105,497 1,073 72
05/04/2014 14:43:59    13,769,579,465,945 1,002 72
05/04/2014 14:49:58    13,769,579,825,606 981          72
05/04/2014 14:56:08    13,769,580,188,448 932          72
05/04/2014 15:08:34 13,769,580,883,882 1,617 72
05/04/2014 15:15:44    13,769,581,579,150 1,551 73
05/04/2014 15:23:05    13,769,582,263,037 1,587 73
05/04/2014 15:29:58    13,769,582,918,365 1,660 73
05/04/2014 15:37:02    13,769,583,622,086 1,666 73
05/04/2014 15:43:53    13,769,584,306,934 1,601 73
05/04/2014 15:50:40    13,769,584,958,452 1,522 73
05/04/2014 15:58:23    13,769,585,663,081 1,552 73
05/04/2014 16:05:46    13,769,586,350,835 1,478 73
05/04/2014 16:13:07    13,769,587,002,682 1,498 73
05/04/2014 16:20:56 13,769,587,705,333 1,481 73
05/04/2014 16:28:41    13,769,588,394,032 1,423 73
05/04/2014 16:36:20 13,769,589,047,353 1,555 73
05/04/2014 16:43:46 13,769,589,740,834 1,670 73
05/04/2014 16:50:40 13,769,590,432,057 19          73

3 在接口服务器(132.77.127.78) 从AWR分析出SCN的变化:
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增速迅速变大：
Session altered.
"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."

05/04/14_00_00_01_00 calls to kcmgas    1
05/04/14_01_00_02_01 calls to kcmgas          1
05/04/14_02_01_03_00 calls to kcmgas             2
05/04/14_03_00_04_00 calls to kcmgas          1
05/04/14_04_00_05_00 calls to kcmgas    1
05/04/14_05_00_06_00 calls to kcmgas          10
05/04/14_06_00_07_00 calls to kcmgas          2
05/04/14_07_00_08_00 calls to kcmgas          2
05/04/14_08_00_09_00 calls to kcmgas          4
05/04/14_09_00_10_00 calls to kcmgas          1
05/04/14_10_00_11_00 calls to kcmgas          45
05/04/14_11_00_12_00 calls to kcmgas          749
05/04/14_12_00_13_01 calls to kcmgas          896
05/04/14_13_01_14_00 calls to kcmgas          893
05/04/14_14_00_15_00 calls to kcmgas 652
05/04/14_15_00_16_00 calls to kcmgas          765
05/04/14_16_00_17_00 calls to kcmgas 715
05/04/14_17_00_18_00 calls to kcmgas 2
05/04/14_18_00_19_00 calls to kcmgas          1
05/04/14_19_00_20_00 calls to kcmgas          1
05/04/14_20_00_21_01 calls to kcmgas          6
05/04/14_21_01_22_00 calls to kcmgas          1
05/04/14_22_00_23_00 calls to kcmgas 1
05/04/14_23_00_00_00 calls to kcmgas          1
--------- 接口服务器(132.77.127.78) ------------

--------- 数据仓库(132.77.127.81) ----------------
DATE_TIME    STAT_NAME          PER_SEC
05/04/14_00_01_01_00 calls to kcmgas          32
05/04/14_01_00_02_00 calls to kcmgas    32
05/04/14_02_00_03_00 calls to kcmgas          31
05/04/14_03_00_04_00 calls to kcmgas          32
05/04/14_04_00_05_01 calls to kcmgas 31
05/04/14_05_01_06_00 calls to kcmgas          24
05/04/14_06_00_07_01 calls to kcmgas          33
05/04/14_07_01_08_00 calls to kcmgas          33
05/04/14_08_00_09_00 calls to kcmgas          29
05/04/14_09_00_10_00 calls to kcmgas          32
05/04/14_10_00_11_00 calls to kcmgas          27
05/04/14_11_00_12_00 calls to kcmgas          32
05/04/14_12_00_13_00 calls to kcmgas          41
05/04/14_13_00_14_00 calls to kcmgas          32
05/04/14_14_00_15_01 calls to kcmgas          27
05/04/14_15_01_16_00 calls to kcmgas          34
05/04/14_16_00_17_00 calls to kcmgas          95
05/04/14_17_00_18_00 calls to kcmgas          63
05/04/14_18_00_19_00 calls to kcmgas          45
05/04/14_19_00_20_00 calls to kcmgas       31
05/04/14_20_00_21_00 calls to kcmgas    32
05/04/14_21_00_22_00 calls to kcmgas 32
05/04/14_22_00_23_00 calls to kcmgas          31
05/04/14_23_00_00_00 calls to kcmgas          32