UNDO使用率高，且unexpire过高
同事反应某数据库undo过高，且unexpire达到90%，
通过如下sql检查可以看到母亲undotbs1使用比较高，且目前undotbs1已经200多g了。
SQL> set linesize 666
SQL> set long 999999
SQL> col TABLESPACE_NAME for a30
SQL> select c.TABLESPACE_NAME,c.CONTENTS,d.sum_MB,d.free_MB,d.use_precent,
2 c.INITIAL_EXTENT,c.NEXT_EXTENT,c.MAX_EXTENTS from dba_tablespaces c,
3 (select b.tablespace_name,round(sum(b.bytes)/1024/1024,0) sum_MB, round(sum(nvl(a.bytes,0))/1024/1024,0) free_MB,
4 round((sum(b.bytes)-sum(nvl(a.bytes,0)))/sum(b.bytes),4)*100 use_precent
5 from (select tablespace_name,file_id,sum(bytes) bytes from dba_free_space group by tablespace_name,file_id ) a,
6 dba_data_files b
7 where a.file_id(+)=b.file_id and a.tablespace_name(+)=b.tablespace_name
8 group by b.tablespace_name
9 order by use_precent) d
10 where c.TABLESPACE_NAME=d.TABLESPACE_NAME
11 order by 6;
TABLESPACE_NAME CONTENTS SUM_MB FREE_MB USE_PRECENT INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
------------------------------ --------- ---------- ---------- ----------- -------------- ----------- -----------
DATA_CRMS PERMANENT 32766 32212 1.69 65536 2147483645
DATA_MDEC PERMANENT 5115 5115 .01 65536 2147483645
SYSAUX PERMANENT 20480 15237 25.6 65536 2147483645
DATA_CSS PERMANENT 17406 5379 69.1 65536 2147483645
DATA_MMWLAN PERMANENT 398629 102459 74.3 65536 2147483645
MMWLAN PERMANENT 937403 438967 53.17 65536 2147483645
UNDOTBS2 UNDO 223222 4 100 65536 2147483645
INDX PERMANENT 255 255 .02 65536 2147483645
DATA_PCPUSH PERMANENT 1023 1023 .01 65536 2147483645
DATA_UCENTER PERMANENT 30705 30704 0 65536 2147483645
DATA_MM_PPMS_NEW PERMANENT 368628 253721 31.17 65536 2147483645
SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS WHERE
tablespace_name = 'UNDOTBS2' GROUP BY STATUS;
STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------- -------------------- ----------
UNEXPIRED 212122.875 16016
EXPIRED 3630.5 638
ACTIVE 376.125 26
通过上面查询，看到undo extents，绝大部分是unexpired的，通常情况下，，如果
unexpired extent过多，说明是存在大量的事务，但是事实上这里并不是这样。 故有理由相信是oracle undo 自动调节
的缘故，如下：
NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_undo_autotune TRUE enable auto tuning OF undo_retention
从oracle 10.2开始，该隐含参数默认为true，虽然在后面版本中修复了大量的bug，但是仍然存在一些问题。
例如 Bug 9681444 : TUNED_UNDORETENTION CAN BE TOO HIGH AFTER DB BOUNCE IF HIGH WORKLOAD BEFORE
该bug，实际上要在oracle 12.1版本中才能修复。
对于undo datafile是非自动扩展的情况下，oracle为了避免ora-01555错误，会进行undo_retention的自动调节。
在oracle进行undo_retention自动调节的情况下，手工设置的参数undo_retention将通常不会起作用。
关于undo_retention oracle有如下2种机制：
1) undo datafile autoextend off
结合undo表空间的大小，根据v$undostat.TUNED_UNDORETENTION来决定undo_retention的大小，这种情况下，
往往TUNED_UNDORETENTION的值都非常大。
2)在undo为自动调节的情况下，undo_retention的计算是根据v$undostat.MAXQUERYLEN+300来判断，
最后取(MAXQUERYLEN+300，undo_retention,) 中的最大值。
目前undo datafile 均为非自动扩展
FILE_ID FILE_NAME BYTES/1024/1024
---------- ------------------------------------------------------------ ---------------
424 +DATA01/datafile/undotbs2.417.822744349 16383
455 +DATA01/datafile/undotbs2.418.822744353 16383
459 +DATA01/datafile/undotbs2.498.822753727 16383
468 +DATA01/datafile/undotbs2.671.822771279 16383
482 +DATA01/datafile/undotbs2.261.822725123 16383
554 +DATA01/datafile/undotbs2.596.822763851 16383
556 +DATA01/datafile/undotbs2.634.822767307 16383
558 +DATA01/datafile/undotbs2.674.822771293 16383
644 +DATA01/datafile/undotbs2.925.833863871 30720
667 +DATA01/datafile/undotbs2.949.835461505 30719
668 +DATA01/datafile/undotbs2.950.835736441 30719
SQL> SELECT inst_id,MIN(TUNED_UNDORETENTION) FROM gv$UNDOSTAT GROUP BY inst_id ORDER BY 1;
INST_ID MIN(TUNED_UNDORETENTION)
---------- ------------------------
1 414442
2 507899
oracle针对该问题有如下3种解决方案：
1. 调整undo datafile
ALTER DATABASE DATAFILE 424 AUTOEXTEND ON MAXSIZE 16383m;
ALTER DATABASE DATAFILE 455 AUTOEXTEND ON MAXSIZE 16383m;
ALTER DATABASE DATAFILE 459 AUTOEXTEND ON MAXSIZE 16383m;
ALTER DATABASE DATAFILE 468 AUTOEXTEND ON MAXSIZE 16383m;
ALTER DATABASE DATAFILE 482 AUTOEXTEND ON MAXSIZE 16383m;
ALTER DATABASE DATAFILE 554 AUTOEXTEND ON MAXSIZE 16383m;
ALTER DATABASE DATAFILE 556 AUTOEXTEND ON MAXSIZE 16383m;
ALTER DATABASE DATAFILE 558 AUTOEXTEND ON MAXSIZE 16383m;
ALTER DATABASE DATAFILE 644 AUTOEXTEND ON MAXSIZE 30720m;
ALTER DATABASE DATAFILE 667 AUTOEXTEND ON MAXSIZE 30719m;
ALTER DATABASE DATAFILE 668 AUTOEXTEND ON MAXSIZE 30719m;
2. 调整undo shrink模式
3. 关闭undo_retention的自动调节功能
经过考虑采用第一种方法，目前unexpire已经降了：
STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------- -------------------- ----------
EXPIRED 219349.188 16358
UNEXPIRED 3519.3125 633
ACTIVE 227 5
目前undo使用率也已经下降，恢复正常：
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
DATA_MM_PPMS_NEW 368628 253719.125 69 31
DATA_MM_OOP 16383 8146.75 50 50
DATA_EAF 2046 1003 49 51
MMWLAN 937403 438966.75 47 53
INDX_MM_PPMS 1173222 449224 38 62
DATA_CSS 17406 5379.125 31 69
UNDOTBS2 223222 61680.8125 28 72
DATA_PUBUSER 35836 10210 28 72
DATA_PPMS 2532517 675338.5 27 73
DATA_MMWLAN 398629 102451.438 26 74
DATA_MM_TAAC 397239 63993.625 16 84
3590
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
