[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:
v$session.saddr=v$transaction.ses_addr.
>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
---------- ------------ ---------- ---------- ----------
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.
>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.
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
