今天有朋友在问关于undo 使用很高的问题,这个问题其实很常见了,上次也正好处理过一起,客户的
3节点rac,undo使用很高,当时操作后留了一份简单的记录,今天我发出来,供大家参考!
关于ucp库，undo使用较高的问题：
通过如下sql检查可以看到母亲undotbs1使用比较高，且目前undotbs1已经80g了。
通过上面查询，看到ucp1节点上undo extents，绝大部分是unexpired的，其中是unexpired的。 通常情况下，，如果
unexpired extent过多，说明是存在大量的事务，但是事实上这里并不是这样。 故有理由相信是oracle undo 自动调节
的缘故，如下：
从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 均为非自动扩展，如下：
oracle针对该问题有如下3种解决方案：
调整之后，观察了一会儿，发现有一定效果，如下：
等到第二天再去观察,发现undo基本上都比较正常了,均维持在50{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}左右,如下:
整个过程三非常简单的,供大家参考!
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) "{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b} Free",
6 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b} 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) {39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b} Free {39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b} 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) {39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b} Free {39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b} 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
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
SQL> 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}&par{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';
Enter value for par: UNDO
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}&par{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}UNDO{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}'
no rows selected
SQL> /
Enter value for par: undo_autotune
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}&par{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}undo_autotune{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}'
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
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>
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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}左右,如下:
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
整个过程三非常简单的,供大家参考!
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
