这周跟oracle 10g undo耗上了,一周连续遇到两套数据库undo表空间使用率过高的情况,表面现象基本一致,但是实际的情况却不同,记录下。。。
案例一:
DB:10.2.0.2.0
OS:AIX
现象:早上一到就被客户找到说有套库的UNDO使用率很高(后来才知道前一天还因为undo表空间空间不足,一批处理程序报ORA-30036错误,没执行成功)。
一听到这个消息,我第一反应是问客户是否是10g数据库(10g数据库因为undo_retention自动优化引起undo表空间使用率过高的情况很常见,之前也遇到过几个这样的案例),客户说是10g的,惯性思维的认为是undo_retention自动优化功能引起的(有时候惯性思维也不好)。
简单的看了下该数据库设置的undo_retention设置为
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo boolean FALSE
undo_management string AUTO
undo_retention integer 43200
43200/60/60 = 12个小时,算是一个比较长的时间了。
通过查看v$undostat来查看undo_retention优化自动调整的undo_retention时间
BEGIN_TIME END_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------- ------------------- ---------- ------------- ----------- -------------------
2013-01-08 08:05:59 2013-01-08 08:15:59 0 2368 2907672 33808 43200
2013-01-08 08:15:59 2013-01-08 08:25:59 0 2368 2827800 32784 43200
2013-01-08 08:25:59 2013-01-08 08:35:59 0 2368 2829848 31760 43200
2013-01-08 08:35:59 2013-01-08 08:45:59 0 3392 2841368 32528 43200
2013-01-08 08:45:59 2013-01-08 08:55:59 0 3392 2846488 30480 43200
2013-01-08 08:55:59 2013-01-08 09:05:59 0 6464 2853656 26384 43200
2013-01-08 09:05:59 2013-01-08 09:15:59 0 4416 2880280 31504 43200
2013-01-08 09:15:59 2013-01-08 09:25:59 0 11584 2885400 20240 43200
SQL> select status,sum(bytes)/1024/1024 mb from dba_undo_extents group by status;
STATUS MB
--------- ----------
EXPIRED 21350.625
UNEXPIRED 1488.1875
ACTIVE 8
从上面的情况来看,确实是undo_retention时间比较长引起。
建议客户通过设置_undo_autotune为false来关闭undo_retention的自动优化功能,设置undo_retention为10800。
设置后让客户重新跑程序,结果到中午的时候发现undo 中active状态的extent持续增加,undo表空间又即将不足,又紧急扩容8G,难道是真的由于undo不足,经熊哥(O(∩_∩)O哈!,大牛老熊)指点,发现些线索。下面是前一天undo运行的情况:
BEGIN_TIME END_TIME SSOLDERRCNT NOSPACEERRCNT ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS
------------------- ------------------- ----------- ------------- ---------- ------------- -----------
2013-01-07 13:45:59 2013-01-07 13:55:59 0 0 1646136 142360 131488
2013-01-07 13:55:59 2013-01-07 14:05:59 0 0 1740112 127328 52288
2013-01-07 14:05:59 2013-01-07 14:15:59 0 0 1834240 81264 4352
2013-01-07 14:15:59 2013-01-07 14:25:59 0 0 1909856 9728 400
2013-01-07 14:25:59 2013-01-07 14:35:59 0 0 1910088 8864 344
2013-01-07 14:35:59 2013-01-07 14:45:59 2 4 1909032 10824 128
2013-01-07 14:45:59 2013-01-07 14:55:59 2 1 1908616 11352 16
2013-01-07 14:55:59 2013-01-07 15:05:59 0 1 3424 1916552 8
2013-01-07 15:05:59 2013-01-07 15:15:59 0 0 3656 1915664 664
2013-01-07 15:15:59 2013-01-07 15:25:59 0 0 2752 1915000 2232
2013-01-07 15:25:59 2013-01-07 15:35:59 0 0 3776 1906608 1792
在1月7日下午14:35到15:05之间NOSPACEERRCNT字段开始出现计数(计数总共6次),该字段表示请求undo空间未成功的次数,在14:35到14:45期间undo表空间使用的空间为active状态的extent为1908616*8192/1024/1024=14911(MB)、unexpired状态的extent为11352*8192/1024/1024=89(MB),合计15G,此次数据库PSAPUNDO表空间的大小也为15G,已没有空闲空间可分配(询问客户,这跟前程序报错的时间基本一致),随后的时刻active状态的extent降为3424*8192/1024/1024=27(MB),unexpired状态的extent增加为1916552*8192/1024/1024=14973(MB).
随后客户先扩容了8G,再看看扩容后的情况
BEGIN_TIME END_TIME SSOLDERRCNT NOSPACEERRCNT ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS
------------------- ------------------- ----------- ------------- ---------- ------------- -----------
2013-01-07 22:55:59 2013-01-07 23:05:59 0 0 2940528 3320 0
2013-01-07 23:05:59 2013-01-07 23:15:59 0 0 2940520 3296 32
2013-01-07 23:15:59 2013-01-07 23:25:59 0 0 2940520 3296 32
2013-01-07 23:25:59 2013-01-07 23:35:59 0 0 2376 2941288 184
2013-01-07 23:35:59 2013-01-07 23:45:59 0 3 2392 2941448 8
2013-01-07 23:45:59 2013-01-07 23:55:59 0 0 2368 2940888 592
在23:15到23:25之间,active状态的extent为2940520*8192/1024/1024=22973(MB),unexpired状态的extent为26(MB),合计23G左右,此时已没有空闲空间可分配,客户确认在此期间运行的批处理程序确实再次报错ORA-30036。
可以确定该批量程序需要的undo表空间的使用率确实超过undo自身的空间。
总结下:
出现ORA-30036报错的情况(目前能想到的):
1)undo_retention设置时间过大,导致undo中分配的大部分extent长期处于unexpired状态,影响extent的回收重用。
2)oracle 10g的新特性undo_retention自动优化功能根据undo的使用情况,自动计算出undo_retention的值,导致大部分extent长期处于unexpired状态,影响extent的回收重用。
3)undo表空间自身设置得过小,且置于非自动增长模式下,也会导致该错误的发生。
4)有过大的事务,其需要的undo空间大于目前数据库undo表空间的空间
5)Oracle的BUG。Bug:5387030 - Automatic tuning of undo_retentioncauses unusual extra space allocation [ID 5387030.8]
6) Oracle在undo_retention自动优化管理方面的缺陷。
顺带学习了下Oracle在undo表空间自动管理模式下,请求空间的情况如下:
当一个新事务开始,按以下步骤进行:
1) 在没有active transaction的undo segment上分配一个extent,oracle尽可能将事务分布在所有的undo segment上。
2) 如果没有找到online的segment,则尝试去找offline的segment,并online它。
3) 如果没有找到,则创建一个新的undo segment.
4) 如果空间不允许,在当前的undo segment上尝试重用expired状态的extent.
如果一个已经运行的事物,需要更多的undo空间时,执行以下步骤:
1) 如果当前的extent有free block,则使用它们。
2) 如果当前的extent没有free block,并且下一个exent是expired状态,则使用下一个extent.
3) 如果下一个extent不是expired状态,那么尝试从undo tablespace获取空间,如果有空闲空间,则为当前的segment分配新的extent.
4) 如果没有空闲空间,需要从offline segment上偷extent,先从offline segment上deallocate extent,然后加到当前的undo segment上。
5) 如果没有offline segment,则从online segment上偷extent,同样从online segment 上deallocate extent,然后加到当前的undo segment上。
6) 尝试扩展数据文件。
7) 尝试重用当前undo segment上unexpired状态的extent,如果所有的exetent都繁忙(包含未提交的事务),则下一步。
8) 尝试从offline的segment上偷取unexpired状态的extent
9) 尝试从online的segment上偷取unexpired状态的extent
10)如果以上步骤都无法获取空间,则报错ORA-30036:unable to extend segment by %s inundo tablespace ‘%s’
参考资料:http://www.hellodb.net/2009/02/oracle_undo.html