undo 表空间告警 处理一侧

    某web业务库 经常报undo表空间 使用率告警，我知道undo 自动调节导致undo extent block 没有释放 ，涉及一个隐含参数_undo_autotune  默认是true，如果调整此参数会导致自动调节功能消失，所以是我一直没有修改次参数的原因。

 

SQL> select b.tablespace_name,
  2         nvl(used_undo, 0) "USED_UNDO(M)",
  3         total_undo "Total_undo(M)",
  4         trunc(nvl(used_undo, 0) / total_undo * 100, 2) || '%' used_PCT
  5    from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name
  6            from dba_undo_extents
  7           where status in ('ACTIVE', 'UNEXPIRED')
  8           group by tablespace_name) a,
  9         (select tablespace_name, sum(bytes / 1024 / 1024) total_undo
 10            from dba_data_files
 11           where tablespace_name in
 12                 (select value
 13                    from v$spparameter
 14                   where name = 'undo_tablespace'
 15                     and (sid = (select instance_name from v$instance) or
 16                         sid = '*'))
 17           group by tablespace_name) b
 18   where a.tablespace_name(+) = b.tablespace_name;

TABLESPACE_NAME                USED_UNDO(M) Total_undo(M) USED_PCT
------------------------------ ------------ ------------- ---------------------
UNDOTBS1                         30741.5625         32767 93.81%

大量的undo 未过期 ，TUNED_UNDORETENTION （单位秒）表示系统调优后保留时间大概有好几天

SQL> SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
  2      TUNED_UNDORETENTION FROM V$UNDOSTAT;

BEGIN_TIME          TUNED_UNDORETENTION
------------------- -------------------
03/18/2019 10:03:56             2911614
03/18/2019 09:53:56             2912127
03/18/2019 09:43:56             3365732
03/18/2019 09:33:56             3391303
03/18/2019 09:23:56             3391156
03/18/2019 09:13:56             3390379
03/18/2019 09:03:56             3390334
03/18/2019 08:53:56             3387872
03/18/2019 08:43:56             3388428
03/18/2019 08:33:56             3389366
03/18/2019 08:23:56             3389989

BEGIN_TIME          TUNED_UNDORETENTION
------------------- -------------------
03/18/2019 08:13:56             3390092
03/18/2019 08:03:56             3390637
03/18/2019 07:53:56             3388613
03/18/2019 07:43:56             3389881
03/18/2019 07:33:56             3389780
03/18/2019 07:23:56             3389601
03/18/2019 07:13:56             3389838
03/18/2019 07:03:56             3390282
03/18/2019 06:53:56             3387401
03/18/2019 06:43:56             3390781
03/18/2019 06:33:56             3390930

BEGIN_TIME          TUNED_UNDORETENTION
------------------- -------------------
03/18/2019 06:23:56             3390988
03/18/2019 06:13:56             3390965
03/18/2019 06:03:56             3390912
03/18/2019 05:53:56             3388619
03/18/2019 05:43:56             3390721
03/18/2019 05:33:56             3390700
03/18/2019 05:23:56             3390656
03/18/2019 05:13:56             3390567
03/18/2019 05:03:56             3390624
03/18/2019 04:53:56             3187469
03/18/2019 04:43:56             3391283

BEGIN_TIME          TUNED_UNDORETENTION
------------------- -------------------
03/18/2019 04:33:56             3390832
03/18/2019 04:23:56             3390924
03/18/2019 04:13:56             3390744
03/18/2019 04:03:56             3390823
03/18/2019 03:53:56             3388204
03/18/2019 03:43:56             3391302
03/18/2019 03:33:56             3391201
03/18/2019 03:23:56             3391303
03/18/2019 03:13:56             3391261
03/18/2019 03:03:56             3391251
03/18/2019 02:53:56             3388820

BEGIN_TIME          TUNED_UNDORETENTION
------------------- -------------------
03/18/2019 02:43:56             3391118
03/18/2019 02:33:56             3391062
03/18/2019 02:23:56             3390984
03/18/2019 02:13:56             3390827
03/18/2019 02:03:56             3388783
03/18/2019 01:53:56             3385059
03/18/2019 01:43:56             3386404
03/18/2019 01:33:56             3387645
03/18/2019 01:23:56             3388516
03/18/2019 01:13:56             3391423
03/18/2019 01:03:56             3391606

BEGIN_TIME          TUNED_UNDORETENTION
------------------- -------------------
03/18/2019 00:53:56             3388384
03/18/2019 00:43:56             3387425
03/18/2019 00:33:56             3387327
03/18/2019 00:23:56             3387417
03/18/2019 00:13:56             3387373
03/18/2019 00:03:56             3387296
03/17/2019 23:53:56             3385425
03/17/2019 23:43:56             3386688
03/17/2019 23:33:56             3386872
03/17/2019 23:23:56             3386883
03/17/2019 23:13:56             3386896
........

03/14/2019 13:13:56             38975.6833
03/14/2019 13:03:56             39001.5167
03/14/2019 12:53:56             38992.1667
03/14/2019 12:43:56             38915.6333
03/14/2019 12:33:56             39108.1333
03/14/2019 12:23:56             39104.3333
03/14/2019 12:13:56             39119.9167
03/14/2019 12:03:56             39012.8833
03/14/2019 11:53:56             39226.2333
03/14/2019 11:43:56             39275.4167
03/14/2019 11:33:56                39209.4
03/14/2019 11:23:56             39395.1167
03/14/2019 11:13:56             39415.0833
03/14/2019 11:03:56             39401.5667
03/14/2019 10:53:56                39390.3
03/14/2019 10:43:56               39458.25
03/14/2019 10:33:56                39418.3
03/14/2019 10:23:56               39561.05

查看一遍文档，即不会关闭自动调节，也可以降低空间使用率

1).将UNDO表空间对应的数据文件调整为自动扩展，并为其设定一个最大值。
SQL> ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>

处理后 效果马上有了

感谢！！！

感谢future2012lg写的文章