RAC双节点 Undo表空间不回收

ellengan

刘大，您好，
         今天观察我们生产环境有一套RAC环境，两个节点的undo表空间一直在增长，没有回收过。
环境介绍如下：
os：aix 6.1
CRS版本为：10.2.0.5.0
数据库版本为：10.2.0.5.6
跟这个帖子症状很像：http://space.itpub.net/13478741/viewspace-701693
SQL> select count(*) from dba_undo_extents    where status in ('ACTIVE','UNEXPIRED')    and tablespace_name='UNDOTBS01';

  COUNT(*)
----------
      3535

SQL> select begin_time,txncount,maxquerylen,unexpiredblks,expiredblks,tuned_undoretention from v$undostat order by begin_time;

BEGIN_TIME    TXNCOUNT MAXQUERYLEN UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
1        2013/10/26 12:10:41        40733        563        18816        34960        1224
2        2013/10/26 12:20:41        37676        206        20096        33424        1764
3        2013/10/26 12:30:41        36076        194        13824        44816        1066
4        2013/10/26 12:40:41        34926        140        15616        36368        1138
5        2013/10/26 12:50:41        35609        312        13056        40080        972
6        2013/10/26 13:00:41        35185        227        14720        34960        1105
7        2013/10/26 13:10:41        36030        115        14088        38928        966
8        2013/10/26 13:20:41        37232        147        11656        44304        920
9        2013/10/26 13:30:41        37613        66        11904        44304        1282
10        2013/10/26 13:40:41        38328        282        10880        48272        956
11        2013/10/26 13:50:41        39655        221        13824        43536        1048
12        2013/10/26 14:00:41        41001        111        13312        43792        1055

。。。。。

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_gc_undo_affinity                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      undotbs01

麻烦刘大看看是否此为oracle的bug，因为我看附件文档说在oracle10.2.0.5里面已经修复了，
还有如是此bug，修改参数 Alter system set "_undo_autotune" = false;是否会引起其他问题，非常感谢
^_^

Liu Maclean(刘相兵

ellengan 发表于 2013-12-20 17:09
upup~~盼刘大回复，交易流水大表长期不搜集统计信息会影响执行计划么，前提是我们的应用比较稳定，不怎么修 ...

是否考虑锁住该表的统计信息？ 来避免以后再次统计？

dla001

ellengan 发表于 2013-12-20 17:09
upup~~盼刘大回复，交易流水大表长期不搜集统计信息会影响执行计划么，前提是我们的应用比较稳定，不怎么修 ...

是否需要收集统计信息要看具体情况。 要不要收集你们自己应该清楚。看SQL，如果执行计划一直正确就没必要去收集。

ellengan

upup~~盼刘大回复，交易流水大表长期不搜集统计信息会影响执行计划么，前提是我们的应用比较稳定，不怎么修改的情况下，谢谢刘大回复

ellengan

这张大表是交易流水表使用很频繁，请问刘大，这样时不时的搜集统计信息失败会不会对交易有影响，而且失败之后手工搜集也是同样的错误。
需要定期把_undo_autotune参数改回true收集这张大表的统计信息，然后再改回false么？
谢谢刘大回复^_^

ellengan

11.7号将rac两个节点的_undo_autotune=false修改之后，undo表空间使用恢复正常，一般为200m之内。
但是观察至今，有一张更新删除比较频繁的大的流水表在12&25号两次统计信息收集失败，在14&19号都搜集成功了，失败错误如下所示：
Mon Nov 25 23:11:49 GMT+08:00 2013ORA-01555 caused by SQL statement below (SQL ID: dkf47b8g90nda, Query Duration=2233 sec, SCN: 0x0006.10d8f387):
Mon Nov 25 23:11:49 GMT+08:00 2013insert /*+ append */ into sys.ora_temp_1_ds_3025064 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */"TH_TRACK2","TH_TRACK3","TH_PIN_BLOCK","TH_BATCH_NO","TH_BATCH_STATUS","TH_TRANSACTION_AMOUNT","TH_TM_TIME","TH_LOCAL_TM_TIME","TH_CREATE_TIMESTAMP","TH_UPDATE_TIMESTAMP","TH_TRF_FROM_TXN_ID","TH_REMARKS","TH_OFFLINE_EARN_DATE","TH_OFFLINE_EARN_TIME","TH_ORG_TXN_ID","TH_TXN_CARD_NO","TH_TERMINAL_NO","TH_STAN","TH_RRN","TH_CARD_NO","TH_ACCOUNT_NO","TH_PRIMARY_ACCOUNT_NO","TH_TRANSMISSION_DATETIME", rowid SYS_DS_ALIAS_0 from "OLSUSER"."TXN_HEADER" sample (  4.3613590061) t  UNION ALL SELECT * FROM sys.ora_temp_1_ds_3025064 WHERE 1 = 0
Mon Nov 25 23:45:16 GMT+08:00 2013Thread 1 advanced to log sequence 17444 (LGWR switch)
  Current log# 5 seq# 17444 mem# 0: +DATA/culprodb/onlinelog/redo05.log
Mon Nov 25 23:54:47 GMT+08:00 2013ORA-01555 caused by SQL statement below (SQL ID: 70u7f6nw6mjsz, Query Duration=2578 sec, SCN: 0x0006.10db6048):
Mon Nov 25 23:54:47 GMT+08:00 2013select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*),count("TH_TRACK2"),count(distinct "TH_TRACK2"),count("TH_TRACK3"),count(distinct "TH_TRACK3"),sum(sys_op_opnsize("TH_TRACK3")),substrb(dump(min(substrb("TH_TRACK3",1,32)),16,0,32),1,120),substrb(dump(max(substrb("TH_TRACK3",1,32)),16,0,32),1,120),count("TH_PIN_BLOCK"),count(distinct "TH_PIN_BLOCK"),count("TH_BATCH_NO"),count(distinct "TH_BATCH_NO"),sum(sys_op_opnsize("TH_BATCH_NO")),substrb(dump(min("TH_BATCH_NO"),16,0,32),1,120),substrb(dump(max("TH_BATCH_NO"),16,0,32),1,120),count("TH_BATCH_STATUS"),count(distinct "TH_BATCH_STATUS"),substrb(dump(min("TH_BATCH_STATUS"),16,0,32),1,120),substrb(dump(max("TH_BATCH_STATUS"),16,0,32),1,120),count("TH_TRANSACTION_AMOUNT"),count("TH_TM_TIME"),count(distinct "TH_TM_TIME"),count("TH_LOCAL_TM_TIME"),count(distinct "TH_LOCAL_TM_TIME"),count("TH_CREATE_TIMESTAMP"),count(distinct "TH_CREATE_TIMESTAMP"),count("TH_UPDATE_TIMESTAMP"
Mon Nov 25 23:54:47 GMT+08:00 2013GATHER_STATS_JOB encountered errors.  Check the trace file.
Mon Nov 25 23:54:47 GMT+08:00 2013Errors in file /u01/app/oracle/admin/xxxDB/bdump/culprodb1_j001_10158342.trc:

bash-3.2$ cat /u01/app/oracle/admin/CULPRODB/bdump/culprodb1_j001_10158342.trc
/u01/app/oracle/admin/xxxDB/bdump/xxxdb1_j001_10158342.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    AIX
Node name:      racdb01
Release:        1
Version:        6
Machine:        00F705644C00
Instance name: xxxxxDB1
Redo thread mounted by this instance: 1
Oracle process number: 261
Unix process pid: 10158342, image: oracle@racdb01 (J001)

*** 2013-11-25 23:54:47.212
*** ACTION NAME:(GATHER_STATS_JOB) 2013-11-25 23:54:47.200
*** MODULE NAME:(DBMS_SCHEDULER) 2013-11-25 23:54:47.200
*** SERVICE NAME:(SYS$USERS) 2013-11-25 23:54:47.200
*** SESSION ID:(530.28663) 2013-11-25 23:54:47.200
ORA-01555: snapshot too old: rollback segment number 61 with name "_SYSSMU61$" too small
*** 2013-11-25 23:54:47.212
GATHER_STATS_JOB: GATHER_TABLE_STATS('"xxxx"','"TXN_HEADER"','""', ...)
ORA-01555: snapshot too old: rollback segment number 61 with name "_SYSSMU61$" too small

ellengan

谢谢刘大回复，等更改后会上来更新结果^_^

Maclean Liu(刘相兵

action plan:

1、 设置 _undo_autotune=false
2、 建新的UNDO 表空间，给30g, autoextend off
3、 迁移到新的undo 表空间上
4、 过一段时间后 drop掉老的

ellengan

谢谢刘大.之前一号库的undo表空间undotbs01一直设置为8G大小，平时一直监控，阀值在80%以内，上周三开始一直报警100%，由于我们的undo表空间设置为autoextend on和maxsize unlimited，所以从上周三开始数据文件一直在不停的增长，最高增长至约30g.
所以昨晚就新建了一个undotbs3，将1号库的应用切换至新的undo表空间，老的undo表空间已经drop掉了。
然后通过该语句观察：select count(*)
  from dba_undo_extents
where status in ('ACTIVE', 'UNEXPIRED')
   and tablespace_name = 'UNDOTBS03';
一直在增长，现在已经增长至1600多,没有减少的趋势.表空间使用率也一直在增长.
所以现在暂时考虑要不要把数据文件的最大值限制一下，看undo表空间使用了100%且无法扩展的情况下会不会自动复用之前的undo空间。不过因为我们这个是24小时在线的交易系统，所以如果出现undo表空间还是无法复用的情况 ，肯定会影响交易了.谢谢刘大回复^_^

Maclean Liu(刘相兵

ellengan 发表于 2013-10-30 16:38
嗯，我们公司的undo表空间使用率也是差不多固定的，就是这两天突然一下子增长，不怎么回收，所以才奇怪了 ...

给出查看的过程

ellengan

不知道如果设置undo扩展的最大上限，待达到上限后，数据库会如何处理

ellengan

undo表空间重建之后，还是不停增长，无法回收

ellengan

嗯，我们公司的undo表空间使用率也是差不多固定的，就是这两天突然一下子增长，不怎么回收，所以才奇怪了

mql535

我们公司的UNDO表空间都不是自动增长的，都是根据业务量预估出来的。平时使用率都在80%上下。

ellengan

谢谢刘大回复，我们这个生产库运行比较稳定，之前使用的undo表空间率也比较稳定，数据库的运行sql也看过没有什么异常的事物，按照刘大的意思：
由于TUNED_UNDORETENTION    导致UNEXPIREDBLKS 的undo extent 减少缓慢属于正常现象。
我现在undo表空间一直在增长，那我暂时先不调整参数，待晚上重建下undo表空间，看是否还会发生此现象。
就怕undo表空间一直不回收，不停的增长，就比较麻烦了

Maclean Liu(刘相兵

undo_retention                       integer     900

TUNED_UNDORETENTION 一直大于 undo_retention ， TUNED_UNDORETENTION  是 "_undo_autotune"=true时  自动AUM调优算法获得的undo_retention

由于  TUNED_UNDORETENTION          导致UNEXPIREDBLKS 的undo extent 减少缓慢属于正常现象。


除非为了避免一些极端的 undo在短期内大幅度被 active transaction占用外 一般不需要设置 "_undo_autotune" = false;

除非你经常遇到ORA-1555


如果你确定要设置"_undo_autotune" = false;  那么一般推荐重建undo表空间