某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>
处理后 效果马上有了
感谢!!!