Yaping's Weblog

August 30, 2008

Undo/rollback

Filed under: Oracle — Yaping @ 2:51 am
Tags:

Overview

Undo/rollback segment is used for transaction rollback and read consistent or flashback query.

 

There’re two management methods: auto and manual, respectively called undo and rollback. The basic structure is the same.

 

Every database should have two type undo/rollback space: system and user data. The system undo/rollback is allocated from system tablespace, it mainly used for dictionary data.

 

In RAC/OPS environment, every instance has its own private undo/rollback space, it can be read by other instances, but it’s written only by its owner. The system undo/rollback can be shared by every instance.

 

 

Structure

Every segment header contains some control data, the most known structure is transaction table entries. The number of transaction table entries is limited by block size, within 8k block size, the number can’t exceed 98, it means there’re no more than 98 active transactions in one undo/rollback segment at the same time.

 

TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num

————————————————————————————————

0x00 9 0x00 0x00b4 0x002e 0x0000.00295c5e 0x00801210 0x0000.000.00000000 0x00000001 0x00000000

 

State

The state value 9 indicates this slot holds not active transaction, 10 means active transaction, the state can be other value, such as for distributed transaction, 2PC force rollback etc.

 

Wrap#

Sequence number indicates reuse of the slot. This number is incremented as each slot is reused.

 

Uel

Used to store starting extent of an active transaction, or the next pointer in the list of committed transaction if transaction inactive.

 

Nub

Hold the number of blocks in the undo chain.

 

You can query v$resource_limit view to determine the database has how many transactions at peak time. Be careful of distributed query, it produces transaction, even if it doesn’t consume undo block, but every transaction need one transaction table entry, if these query doesn’t be committed or exit normally, it’ll lead to use up all transaction table entries, then no transactions can start if no space extends.

 

 

TRN CTL:: seq: 0x0098 chd: 0x0006 ctl: 0x0013 inc: 0x00000000 nfb: 0x0005 mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x00800025.0098.01 scn: 0x0000.00293471

 

Scn

The committed SCN of the transaction rolled out of the transaction table. It means any transaction associated with SCN lower than this value is committed within this rollback segment. It can be used as commit SCN when delayed block cleanout and the slot has been reused.

 

CHD (commit head)

Head of the chain of the list of committed transactions stored by increasing scn. It indicates this slot will be reused for new transaction first.

 

CTL (commit tail)

Tail of the chain of the list of committed transaction.

 

OPT

Value for the parameter optimal.

 

 

Retention Table

———————————————————–

Extent Number:0 Commit Time: 1202330459

 

There’re also one structure called retention table, it’s introduced since Oracle 9i. The time format is Julian date. The commit time is not updated at every transaction commits, it is related to undo entries crossing the segment highwatermark.

 

 

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.026.000000ba 0x00800035.00d2.19 –U- 3 fsc 0x0000.00297f88

 

Fsc: Free space credit, track space freed in this block.

 

 

Rollback segments

The number of rollback segments is derived from the initial parameter transactions and transactions_per_rollback_segment.

 

System rbs + ceil(transactions/transactions_per_rollback_segment)

 

Every rollback segment must contains at least 2 extents, suggest set the minimum 20 extents.

 

The instance first acquires private rollback segments specified in initial parameter rollback_segments, if there are not sufficient, it attempts to acquire additional public rollback segments not already acquired by other instances.

 

Generally, every rollback segment should be the same size, but in some environment, we may consider to use different size rollback segments. For example, database runs as OLTP system by day, it performs batch data by night, we can create two rollback tablesapce with different size rollback segments, to switch them between day and night.

 

Generally, I don’t suggest to set optimal value for rollback segment. If there’s large transaction occasionally, you may consider to set optimal to let rollback segment to extend and shrink back. It can save some space. But if the database always needs rollback segment size larger than optimal value, you should not set optimal value and directly allocate rollback segment to expected size.

 

You should remember that transaction causes rollback segment to extend, not select statement. Even if there’s enough space within rollback tablespace, long run select statement still can raise ORA-01555 error.

 

Transactions should be allocated to rollback segments according to a least recently committed algorithm. As a transaction commits, the segment is placed in line behind the segments that do not have current transactions.

When transaction reaches the end of an extent, it has two choices: either move into the recently least used already allocated extent or allocate a new extent.

 

The choice hinges on whether the recently least used already allocated extent contains any active transactions or not. If there’re any undo blocks in that extent that belong to a transaction which has not yet been committed, then that block belongs to an active transaction. Oracle then can’t use that extent, also can’t skip that extent to use the next extent which even if has no active transactions in it. Then a new extent is added to the transaction.

 

v$rollstat.EXTENDS indicates adding new extents, WRAPS indicates reusing the already allocated extent. If WRAPS is large, it maybe means that transactions are large or extent size is too small.

 

From the above explain, some little transactions which don’t be committed may cause rollback segment extend continually and use up all rollback tablespace finally. If all transactions are committed promptly, even If hundreds or thousands are active simultaneously, there’s a very good chance that none of the rollback segments will extend even once.

 

 

Undo

One of the benefits of undo is the dynamic creation of undo segments as need.

 

undo_retention

This parameter’s functionality is available either in manual or auto mode since Oracle 9i. The key enhancement with respect to ORA-01555 error resolve is the undo_retention parameter, rather than undo_management parameter.

 

Undo retention is just soft limited, if there’s no enough space to keep data, committed transaction data will be overwritten. Since Oracle 10g, it introduced new functionality to guarantee the retention time. You can specify retention guarantee clause when create or alter tablespace.

 

You can calculate how much undo space the instance needs with a given retention time through query v$undostat.

 

The following is space allocating algorithm. The highlight part is changed since Oracle 9i.

 

1. As space is needed for transactions, a simple “next-undo-block” pointer advances from block to block within an extent in the body of a rollback segment.

 

Each transaction takes the next undo block as needed, and then advances the pointer to the next block.

 

2. At some point just before the last blocks in the current undo extent are reached, one of the transactions is selected to make a decision:

 

Looking ahead to the next extent of the rollback segment, are there any undo blocks currently in use by active (uncommitted) transactions?

 

    If so, then EXTEND the rollback segment by adding a new extent.

 

If the EXTEND operation is not possible because the tablespace is out of space, then raise the ORA-01562 (out of space) error message

 

      Else, are any of the “inactive” transactions in the next extent of the rollback segment committed less than UNDO_RETENTION seconds ago?

 

If so, then EXTEND the rollback segment by adding a new extent

 

Is there enough space in the tablespace to EXTEND the rollback segment by another extent?

 

If not, then WRAP into the next existing extent of the rollback segment.

 

      Else, then WRAP the rollback segment by directing the “next-undo-block pointer” into that next extent.

 

3. And so the advance of the “next-undo-block” pointer sweeps on into the extent chosen. If an EXTEND has occurred, then no overwrite of previous undo information is taking place. If a WRAP has occurred, then all of the blocks being written are overwriting undo information from previously concluded or inactive transactions.

 

 

Others

v$waitstat

save undo block

A save undo segment is generated when a tablespace is taken offline in an emergency situation, this tells how many waits have occurred on a save undo segment block.

 

save undo header

This tells how many waits have occurred in a save undo header.

 

In-Memory Undo

Saving undo information in memory areas, not in undo segment. It can decrease disk IO. This functionality is introduced since Oracle 10g.

 

v$transaction

UBAFIL, UBABLK: current undo block address

 

START_UBAFIL, START_UBABLK: undo block address when transaction start

 

USED_UBLK: Number of undo blocks consumed by the transaction. One undo block can only hold one active transaction. Undo block can contain active transaction record and inactive transaction record simultaneously. Undo blocks within one transaction can be not consecutive. We can determine whether the transaction is rollback through this value increases or decreases.

 

USED_UREC: This value includes record of table and index based on the table be updated. The number of record maybe be a little different between bitmap index and b*tree index. It also has difference on bitmap index between Oracle 9i and Oracle 10g.

 

x$ktuxe

This table stores all transaction table entries. Sometimes the abort transaction can not be queried from v$transaction, but we can query x$ktuxe with KTUXESTA=’ ACTIVE’ condition to obtain all active transactions.

 

Dump undo blocks associated with specific transaction

Alter system dump undo block ‘segment_name’ xid usn slot seq;

 

 

 

 

References

CATS, DOGS, AND ORA-01555 by Tim Gorman, SageLogix

Data Management by Oracle

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: