about Undo Tablespace used High ?
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
今天有朋友在问关于undo 使用很高的问题,这个问题其实很常见了,上次也正好处理过一起,客户的
3节点rac,undo使用很高,当时操作后留了一份简单的记录,今天我发出来,供大家参考!
关于ucp库，undo使用较高的问题：
通过如下sql检查可以看到母亲undotbs1使用比较高，且目前undotbs1已经80g了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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 自动调节
的缘故，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 '%&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 SQL> / 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 均为非自动扩展，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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'; |
调整之后，观察了一会儿，发现有一定效果，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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%左右,如下:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
整个过程三非常简单的,供大家参考!
7 Responses to “about Undo Tablespace used High ?”
[…] 详见原文地址:about Undo Tablespace used High ? 作者：lovewifelovelife 发表于2012-12-7 14:41:33 原文链接 阅读：26 评论：0 查看评论 […]
这个系统的oracle版本是10.2.x.x的？
是11.2.0.3.3
其实如果我觉得不再爱你，就不会再感觉寂寞。有些事情注定无能为力，所以只剩下悲伤。爱的最后你还是你，我却不是那个自己。我用一秒钟爱上你却要用我一生的时间来忘记你。…
文中说『 通常情况下，如果unexpired extent过多，说明是存在大量的事务，但是事实上这里并不是这样。 故有理由相信是oracle undo 自动调节的缘故』
针对这句话，我有以下疑问：
>>如果是因为存在大量的事务导致unexpired extent过多，怎么来判断是否存在大量的事务?
指甲花开在女孩子的指尖。嘁嘁喳喳的笑声和顽皮的嬉闹被一缕缕鲜红的光线追逐，午后的阳光笑了，还有那个叫小秋的男孩子也笑了。 小秋在缭乱的光芒中，眼睛一直追逐最鲜艳和纤细的那一缕，不肯…
同问：>>如果是因为存在大量的事务导致unexpired extent过多，怎么来判断是否存在大量的事务?
Leave a Reply
You must be logged in to post a comment.