UNDO 分配过程

1、首先检查本回滚段当前区是否有空间块，如果有直接使用。
2、如果没有，事务伸展到下一个区，这被称为一次Wrap，v$rollstat中的WRAPS列加1。如果本回滚段有多个事务，第一个伸展到下一个区的事务会引起WRAPS列加1，其他事务再伸展至一下区，此列不再增加。
3、如果本回滚段中没有可用的区，从UNDO表空间中请求区。这被称为一次Extend，记入v$rollstat的EXTENTS列。
4、UNDO表空间中没有Free ，从其他回滚段Steal过期的区。Steal的单位一定是以区为单位。无论Steal是否成功，V$UNDOSTAT的EXPSTEALCNT列都会加1，此列统计的是次数。EXPBLKRELCNT列是成功Steal的块数。
5、如果Steal过期区不成功，试图扩展数据文件。
6、如果无法扩展文件，在本回滚段中重用未过期的区，V$UNDOSTAT.UNXPBLKREUCNT列增加（增加值是区中块的个数）
7、如本回滚段无过期的块，则Steal其他回滚段中未过期的区。每Steal一次，无论是否成功，都会记入V$UNDOSTAT.UNXPSTEALCNT列。Steal的块数记入UNXPBLKREUCNT列。
8、如还不行，报ORA-30036错误。

 ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;(导致30036的原因）

The Undo Block allocation algorithm in Automatic Undo Management  is the following :

1. If the current extent has more free blocks then the next free block is allocated.
2. Otherwise, if the next extent expired then wrap in the next extent and return the first block.
3. If the next extent is not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
4. If there is no free extent available, then steal expired extents from offline undo segments. De-allocate the expired extent from the offline undo segment and add it to the undo segment. Return the first free block of the extent.
5. If no expired extents are available in offline undo segments, then steal from online undo segments and add the new extents to the current undo segment.  Return the first free block of the extent.
6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment and then return the block.
7. Tune down retention in decrements of 10% and steal extents that are now unexpired with respect to the lower retention value.
8. Steal unexpired extents from any offline undo segments.
9. Try to reuse unexpired extents from own undo segment. If all extents are currently busy (they contains uncommitted information) go to the step 10. Otherwise, wrap into the next extent.
10. Try to steal unexpired extents from any online undo segment.
11. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace '%s'

When the UNDO tablespace is created with NO AUTOEXTEND, following the allocation algorithm, here is the explanation for this correct behavior.:

For a fixed size UNDO tablespace (NO AUTOEXTEND), starting with 10.2, we provide max retention given the fixed undo space, which is set to a value based on the UNDO tablespace size.
This means that even if the undo_retention is set to a number of seconds (900 default), the fixed UNDO tablespace supports a bigger undo_retention time interval (e.g: 36 hours), based on the tablespace size, thing that makes the undo extents to be UNEXPIRED. But this doesn't indicate that there are no available undo extents when a transaction will be run in the database, as the UNEXPIRED undo segments will be reused.

 

UNDO 分配过程
如果都开始偷窃其他回滚段中未过期的空间了，说明可用空间已经被用完了。

UNDOTSN 在这个时间段,最后一个活动undo tabspace 的ID
UNDOBLKS 代表了所使用的总共的undo block,可以用这个column 来获得 consumption rate of undo blocks, 依次评估undo tablespace 的size
TXNCOUNT 代表了在这个时间段所有的transaction数量
MAXQUERYLEN 反应了在这个period 中,最长的query,可以用他来评估UNDO_RETENTION参数.query 的长度是从这个cursor被open开始到最后一次fetch/execute
MAXQUERYID 代表了运行时间最长的query的SQL ID
MAXCONCURRENCY 代表了这个period中,最大的并发的transaction 数.
UNXPSTEALCNT 代表了有多少次试图从其它的transaction获取未到期(unexpired)的extent,
UNXPBLKRELCNT 有多少未到期的blocks被移动了,一边被其它的transaction使用.
UNXPBLKREUCNT 有多少未到期的blocks被其它的transaction重用了
EXPSTEALCNT 总共多少次试图获取多少到期的blocks 从其它的 other undo segments
EXPBLKRELCNT Number of expired undo blocks stolen from other undo segments
EXPBLKREUCNT Number of expired undo blocks reused within the same undo segments
SSOLDERRCNT Identifies the number of times the error ORA-01555 occurred,以用他来评估UNDO_RETENTION参数
NOSPACEERRCNT 有多少次出现no free space available 的情况,也就是说undo tablespace 空间不足了..
ACTIVEBLKS 时间段内undo表空间中活动的块数
UNEXPIREDBLKS 时间段内undo表空间中未过期的块数
EXPIREDBLKS 时间段内undo表空间中已过期的块数
TUNED_UNDORETENTION 优化的undo_retention参数的值

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-756605/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-756605/