UNDO使用率高，且unexpire过高

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24996904/viewspace-1165333/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24996904/viewspace-1165333/