Monitoring Transactions and Rollback

Monitoring Transactions and Rollback

Monitoring Transactions and Rollback - 

When performing large transactions, it is often necessary to monitor the rollback activity.

Here is a list of the most popular questions regarding how to monitor a large transaction:

• Is the transaction moving forward or rolling back?
• Is it possible to monitor the rollback activity?
• Is the transaction hung or is it performing work?
• How long will the current transaction take to rollback?

The details needed to answer these questions can be obtained from the v$transaction dynamic performance view.

Oracle will insert an entry in v$transaction for each active transaction in the database.

When the transaction is complete, (either COMMIT or ROLLBACK), the entry should go away.

Additionally, starting with Oracle V7.3, some columns were added to v$transaction view that will allow the DBA to monitor a transaction in greater detail.

Is the transaction moving forward or rolling back?

To determine if a transaction is doing work or rolling back, query v$transaction.used_urec (Number of Undo Records) several times and watch for a change in the value. If used_urec is increasing, the transaction is moving forward. If used_urec is decreasing, the transaction is rolling back.

How long will it take to rollback a transaction? (Oracle 8.x)

If the database HAS NOT been shutdown and restarted. - If the database has not been shutdown and restarted, look at look at v$transaction.used_urec and v$transaction.used_ublk. These fields are the number of undo records and undo blocks currently held by a transaction.

By querying v$transaction over a time interval, the number of records/blocks rolled back in a given time period can be calculated from this rate. Use the following to figure how long it will take to rollback the entire transaction.

v$session can be joined to v$transaction using the following query:

• SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
• FROM v$session a, v$transaction b
• WHERE a.saddr = b.ses_addr;
• SID USERNAME XIDUSN USED_UREC USED_UBLK
• ---------- ------------ ---------- ---------- ----------
• 18 TRUESOURCE 4 10000 82

The above shows that sid 18 is holding 10,000 undo records in rollback segment number 4.

If the database HAS been shutdown and restarted- If the database has been shutdown (abort) and restarted, the information in v$transaction is reset and is not useful. To find out how long the rollback will take, dump the rollback segment header to find the number of undo blocks. Take two segment header dumps, calculate the number of undo blocks rolled back during the time interval, and then calculate how long to roll back the entire transaction.

If the database has been restarted it will be difficult to tell which rollback segment was being used so you will need to dump all the rollback segment headers initially.

To dump the file headers, first determine which block stores the file header.

• SELECT segment_name, header_file, header_block
• FROM dba_segments
• WHERE segment_type='ROLLBACK';
• SEGMENT_NAME HEADER_FILE HEADER_BLOCK
• --------------- ----------- ------------
• SYSTEM 1 2
• RBS0 2 2
• RBS1 2 514
• RBS2 2 1026
• RBS3 2 1538

Next, issue the following command in 8.x+ to dump the file header.

alter system dump datafile 2 block 1026;

This will generate a dump file in user_dump_dest. In the dump file look for the transaction table for the rollback segment. There will be a column called nub which holds the number of undo blocks for the transaction.

-- Trace file snippet follows:

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

---

0x00 10 0xc0 0x1995 0x0007 0x0000.009dd0ac 0x00800193 0x0000.000.00000000 0x00000052

0x01 9 0x80 0x1994 0x003c 0x0000.009dd007 0x00801f3f 0x0000.000.00000000 0x00000001

Notice that the first slot holds an uncommitted transaction (state=10) and the nub (number undo blocks) = 0x52 or decimal 82.

How long will it take to rollback a transaction? (Oracle 9.x)

The 8.x method should work for Oracle 9.i, however it has not been tested with the new undo tablespace in 9i.

If the database has been restarted in 9i, there is an easier way to determine the number of undo blocks required for rollback by using the following query:

• SELECT DISTINCT ktuxesiz FROM x$ktuxe;
• KTUXESIZ
• ----------
• 0
• 1
• 2
• 3
• 107