undo案列一

这周跟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
           

查看undo表空间extent的情况

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