# How Long Will My Rollback Take? Is my Transaction Doing Anything?

` [ID 169138.1]	 `
```Problem Description
-------------------

A large transaction is rolling back,  questions being asked are:

How long it will take to rollback ?
Is the transaction hung or is it performing work ?.

Solution Description
--------------------
Determine if a transaction is doing work or rolling back by querying
v\$transaction.used_urec and watching 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.

Oracle 8.x
-------------

In Oracle 8.x, if the database has not been shutdown and restarted, 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 on the following condition:

>select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
>from v\$session a, v\$transaction b

---------- ------------ ---------- ---------- ----------
14 SCOTT             3      10000         82

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

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 will need to dump all
the rollback segment headers initially.  .

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

>where segment_type='ROLLBACK';

--------------- ----------- ------------
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.

Solution Description - Oracle 9.x and above
-------------------------------------------------------
The 8.x method can be used for Oracle 9i and above,  if the database has not been shutdown and restarted.
Refer Note 304513.1  How To Determine Who Is Using Undo Tablespace And How Much?

If the database has been restarted , there is an easier way to determine
the number of undo blocks required for rollback.  That is by querying table
x\$ktuxe..ktuxesiz.

select distinct ktuxesiz from x\$ktuxe;

KTUXESIZ
----------
0
1
2
3
107

Column ktuxesiz specifies the number of undo blocks used by the transaction.
In the above output,we can see one of the transaction is using 107 undo blocks.

You can use

select sum(distinct(ktuxesiz)) from x\$ktuxe;

which will show the total number of blocks in the transaction that needs to be rolled back.

References
----------

Note:1021486.6 How to Tell When Rollback Finishes

```

• 博文量
194
• 访问量
611236