sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle RAC数据库undo使用率较高的解决思路办法

时间:2016-12-17 11:39   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库undo使用较大,结合案例分析undo使用问题。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: about Undo Tablespace used High

今天有朋友在问关于undo 使用很高的问题,这个问题其实很常见了,上次也正好处理过一起,客户的3节点rac,undo使用很高,当时操作后留了一份简单的记录,今天我发出来,供大家参考!

关于ucp库,undo使用较高的问题:通过如下sql检查可以看到母亲undotbs1使用比较高,且目前undotbs1已经80g了。

SQL> SELECT /* + RULE */
  2   df.tablespace_name "Tablespace",
  3   df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
  4    5   Nvl(Round(SUM(fs.bytes) * 100 / df.bytes), 1) "% Free",
  6   Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  7    FROM dba_free_space fs,
  8         (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
  9   10   11   WHERE fs.tablespace_name(+) = df.tablespace_name
12   GROUP BY df.tablespace_name, df.bytes
UNION ALL
13   14  SELECT /* + RULE */
15   df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
16   17   SUM(df.bytes_free) / (1024 * 1024),
18   Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
19   20    FROM dba_temp_files fs,
21         (SELECT tablespace_name, bytes_free, bytes_used
          FROM v$temp_space_header
22   23           GROUP BY tablespace_name, bytes_free, bytes_used) df
24   WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, fs.bytes, df.bytes_free, df.bytes_used
25   26   ORDER BY 4 DESC;

Tablespace                      SIZE (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
TEMP                                20480          0        300        400
TEMP                                20480        316        300        400
USERS                                1024       1023        100          0
PERFSTAT                              500        499        100          0
WEB_INDEX                          102400 97798.0625         96          4
SYSTEM                              10240   9448.375         92          8
SYSAUX                               4096       1809         44         56
UNDOTBS2                            40960 13049.5625         32         68
UCP_INDEX                          102400 32680.3125         32         68
UNDOTBS3                            40960  9150.9375         22         78
WEB_DATA                           102400      19123         19         81

Tablespace                      SIZE (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
UCP_DATA                           184320  28827.125         16         84
UNDOTBS1                            81920   6292.375          8         92

13 ROWS selected.

SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS WHERE
2 > tablespace_name = 'UNDOTBS1' GROUP BY STATUS;

STATUS    SUM(BYTES)/1024/1024   COUNT(*)
--------- -------------------- ----------
UNEXPIRED           75531.8125      50290
EXPIRED                27.8125        115
ACTIVE                      64          1

通过上面查询,看到ucp1节点上undo extents,绝大部分是unexpired的,其中是unexpired的。 通常情况下,如果unexpired extent过多,说明是存在大量的事务,但是事实上这里并不是这样。 故有理由相信是oracle undo 自动调节的缘故,如下:

SQL> SET linesize 120
SQL> COL name FOR a30
SQL> COL VALUE FOR a20
SQL> COL describ FOR a60

SQL>SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
Enter VALUE FOR par: UNDO
OLD   3:  WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
NEW   3:  WHERE x.indx = y.indx AND x.ksppinm LIKE '%UNDO%'

no ROWS selected

Enter VALUE FOR par: undo_autotune
OLD   3:  WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
NEW   3:  WHERE x.indx = y.indx AND x.ksppinm LIKE '%undo_autotune%'

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,) 中的最大值。

目前ucp的undo datafile 均为非自动扩展,如下:

   FILE_ID FILE_NAME                                                    AUT BYTES/1024/1024/1024                                                                                                       
---------- ------------------------------------------------------------ --- --------------------                                                                                                       
         3 +DATA/ucp/datafile/undotbs1_01.dbf                           NO                    20                                                                                                       
         9 +DATA/ucp/datafile/undotbs1_02.dbf                           NO                    20                                                                                                       
        30 +DATA/ucp/datafile/undotbs1_03.dbf                           NO                    20                                                                                                       
        31 +DATA/ucp/datafile/undotbs1_04.dbf                           NO                    20                                     Oracleо                                                                  
        32 +DATA/ucp/datafile/undotbs2.306.796113455                    NO                    20                                                                                                       
        33 +DATA/ucp/datafile/undotbs3.307.796113663                    NO                    20                                                                                                       
        35 +DATA/ucp/datafile/undotbs2.329.799111255                    NO                    10                                                                                                       
        36 +DATA/ucp/datafile/undotbs3.330.799111491                    NO                    10                                                                                                       
        37 +DATA/ucp/datafile/undotbs3.331.800037727                    NO                    10                                                                                                       
        38 +DATA/ucp/datafile/undotbs2.332.800037971                    NO                    10                                                                                                       

10 ROWS selected.

SQL> SELECT inst_id,MIN(TUNED_UNDORETENTION) FROM gv$UNDOSTAT GROUP BY inst_id ORDER BY 1;

   INST_ID MIN(TUNED_UNDORETENTION)
---------- ------------------------
         1                  3249338
         2                  3745175
         3                  1657628

SQL> SELECT 1657628/3600 FROM dual;

1657628/3600
------------
  460.452222

oracle针对该问题有如下3种解决方案:

1. 调整undo datafile

ALTER DATABASE DATAFILE '+DATA/ucp/datafile/undotbs1_01.dbf' AUTOEXTEND ON MAXSIZE 20480m;
ALTER DATABASE DATAFILE '+DATA/ucp/datafile/undotbs1_02.dbf' AUTOEXTEND ON MAXSIZE 20480m;
ALTER DATABASE DATAFILE '+DATA/ucp/datafile/undotbs1_03.dbf' AUTOEXTEND ON MAXSIZE 20480m;
ALTER DATABASE DATAFILE '+DATA/ucp/datafile/undotbs1_04.dbf' AUTOEXTEND ON MAXSIZE 20480m;
ALTER DATABASE DATAFILE 32  AUTOEXTEND ON MAXSIZE 20480m;
ALTER DATABASE DATAFILE 33  AUTOEXTEND ON MAXSIZE 20480m;
ALTER DATABASE DATAFILE 35  AUTOEXTEND ON MAXSIZE 10240m;
ALTER DATABASE DATAFILE 36  AUTOEXTEND ON MAXSIZE 10240m;
ALTER DATABASE DATAFILE 37  AUTOEXTEND ON MAXSIZE 10240m;
ALTER DATABASE DATAFILE 38  AUTOEXTEND ON MAXSIZE 10240m;

2. 调整undo shrink模式

alter system set "_smu_debug_mode"=33554432 scope=both sid='ucp1';
alter system set "_smu_debug_mode"=33554432 scope=both sid='ucp2';
alter system set "_smu_debug_mode"=33554432 scope=both sid='ucp3';

3. 关闭undo_retention的自动调节功能

alter system set "_undo_autotune" = false scope=both sid='ucp1';
alter system set "_undo_autotune" = false scope=both sid='ucp2';
alter system set "_undo_autotune" = false scope=both sid='ucp3';
调整之后,观察了一会儿,发现有一定效果,如下:

SQL> /

TABLESPACE_NAME                    SUM_MB    FREE_MB USE_PRECENT
------------------------------ ---------- ---------- -----------
USERS                                1024       1023          .1
PERFSTAT                              500        499          .2
WEB_INDEX                          102400      97798        4.49
SYSTEM                              10240       9448        7.73
SYSAUX                               4096       1806        55.9
UCP_INDEX                          102400      32624       68.14
UNDOTBS2                            40960      13042       68.16
UNDOTBS3                            40960       9021       77.98
WEB_DATA                           102400      19059       81.39
UCP_DATA                           184320      28827       84.36
UNDOTBS1                            81920       6148       92.49

11 ROWS selected.

SQL> /

TABLESPACE_NAME                    SUM_MB    FREE_MB USE_PRECENT
------------------------------ ---------- ---------- -----------
USERS                                1024       1023          .1
PERFSTAT                              500        499          .2
WEB_INDEX                          102400      97798        4.49
SYSTEM                              10240       9448        7.73
SYSAUX                               4096       1806        55.9
UCP_INDEX                          102400      32624       68.14
UNDOTBS2                            40960      13042       68.16
UNDOTBS3                            40960       9021       77.98
WEB_DATA                           102400      19059       81.39
UCP_DATA                           184320      28827       84.36
UNDOTBS1                            81920       7106       91.33

11 ROWS selected.

等到第二天再去观察,发现undo基本上都比较正常了,均维持在50%左右,如下:

TABLESPACE_NAME                    SUM_MB    FREE_MB USE_PRECENT
------------------------------ ---------- ---------- -----------
USERS                                1024       1023          .1
PERFSTAT                              500        499          .2
WEB_INDEX                          102400      97798        4.49
SYSTEM                              10240       9448        7.73
UNDOTBS2                            40960      21477       47.57
UNDOTBS1                            81920      40418       50.66
UNDOTBS3                            40960      19701        51.9
SYSAUX                               4096       1833       55.26
UCP_INDEX                          102400      32600       68.16
WEB_DATA                           102400      18931       81.51
UCP_DATA                           184320      28816       84.37
整个过程三非常简单的,供大家参考!

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle RAC数据库undo使用率较高的解决思路办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1409.html

Oracle研究中心

关键词:

10gR2 undo full

about Undo Tablespace used High

Oracle数据库undo使用较大的原因