The Oracle (tm) Users' Co-Operative FAQ

Is there a way to detect processes that are rolling back, and can I figure out how long it will take ?


Author's name: Mark D Powell

Author's Email: Mark.Powell@eds.com

Date written: 17 August 2001

Oracle version(s): 8.1

Is there a way to detect processes that are rolling back, and can I figure out how long it will take ?

Back to index of questions


Detecting sessions that are in the process of rolling back in Oracle is possible and fairly easy to do, but estimating the time to complete rollback is a little less certain though you can certainly make the attempt.

Start by looking at the v$transaction dynamic performance view, which is documented in the Oracle version# Reference Manual. The columns used_ublk and used_urec contain the number of undo blocks and undo records held by the transaction. Undo is just another name for rollback segments.

To make information about a transaction meaningful you probably want to tie it to the session/user it belongs to and to the rollback segment it is using by name and perhaps to the object involved in the SQL or even to the actual SQL. But we will use a simple version for a small demo:

    select  s.username, rn.name,     rs.curext
           ,rs.curblk,  t.used_ublk, t.used_urec
    from    v$transaction   t
           ,v$session       s
           ,v$rollname      rn
           ,v$rollstat      rs
    where  t.addr     = s.taddr
    and    t.xidusn   = rn.usn
    and    rn.usn     = rs.usn

Remember that a commit or rollback command ends a transaction in Oracle so the entry in v$transaction is removed after either command completes.

To save screen space and make reading this article easier I am going explain the test and only show the results. Using the query above with one additional line to limit output to my id during the test:.

	Format the username and name columns
	Run the query
	Insert around 94 rows into a test table issuing two savepoints, A and B.
	Run the query 
	Rollback to savepoint B
	Run the query
	Rollback to savepoint A
	Run the query
	Rollback
	Run the query

Observe the values of the used_ublk and used_urec.

no rows selected [As expected before the first insert we are not a transaction]

USERNAME        NAME                CUREXT     CURBLK  USED_UBLK  USED_UREC
--------------- --------------- ---------- ---------- ---------- ----------
MPOWEL01        ROLL03                   1        139          3         94


Rollback complete. [rollback to B]


USERNAME        NAME                CUREXT     CURBLK  USED_UBLK  USED_UREC
--------------- --------------- ---------- ---------- ---------- ----------
MPOWEL01        ROLL03                   1        139          2         61


Rollback complete. [rollback to A]


USERNAME        NAME                CUREXT     CURBLK  USED_UBLK  USED_UREC
--------------- --------------- ---------- ---------- ---------- ----------
MPOWEL01        ROLL03                   1        139          1         29


Rollback complete.

no rows selected [When the rollback completes our session is no longer a transaction]

If you change the test job to use a commit in place of issuing savepoint and perform the query immediately before and after the commit you will be able to see the transaction information updated and verify that you get a 'no rows returned' message after the commit.

By issuing the query at a timed interval and figuring the change in block or row count during that time period you can estimate the time to complete a rollback by setting the problem up as a proportional problem. Obvious example - where x represents total time, if it took 30 seconds to rollback 1000 rows and you had 10,000 rows to rollback then step 1 is duration / unknown = process / total to be processed or 30/x = 1000/10,000 step 2 is to cross-multiply so you have 300,000 = 1000x so solving for x gives us 300,000 / 1000 = x which in turn becomes 300 = x. Our unit of measure was seconds so 300 / 60 = 5 minutes. If your math skills are like mine Oracle will complete rolling back small tasks before you can make an estimate, but a job can take longer to rollback X number of rows than it took to modify them in the first place. Remember that the rate of rollback may not be constant due to IO and CPU contention.

I mentioned that you can select the objects or SQL associated with a transaction, but the more tables you add to the query the greater the potential you will slow it down or clutter it with unneeded detail. Here is some code similar to what I have seen posted on the comp.databases.oracle newsgroups, but be aware that most of the code I have seen posted to show the user sessions assigned to a rollback segment suffered from one potential flaw. Most of the SQL drove off of v$locked_object to show the objects locked by the session and several did not even access v$transaction since v$locked_object contains the rollback segment number, usn, allowing the authors to bypass v$transaction, but not all transactions lock an object! In a distributed environment, a distributed select takes a rollback segment entry for two phrase commit processing, but being a select never locks an object. This situation will only effect the result in a distributed environment, but if SQL is not good for all situations you need to be aware of its limitations least one day they jump up and bite you in the hind quarters.

Example with v$locked_object and dba_objects using outer join on v$locked_object to enable finding distributed transactions:

	select  s.username, rn.name,     rs.extents
	       ,rs.status,  t.used_ublk, t.used_urec
	       ,do.object_name
	from    v$transaction   t
	       ,v$session       s
	       ,v$rollname      rn
	       ,v$rollstat      rs
	       ,v$locked_object lo
	       ,dba_objects     do
	where  t.addr        = s.taddr
	and    t.xidusn      = rn.usn
	and    rn.usn        = rs.usn
	and    t.xidusn      = lo.xidusn(+)
	and    do.object_id  = lo.object_id

If you do not have SQL to perform this function then you should modify one or both of the queries on this page to select those columns of interest to you in your environment.


Further reading: See the Oracle Concepts Manual for a discussion on transactions and transaction management. Also see the Oracle {version#} Reference Manual for more information on the mentioned v$ dynamic performance tables.


Back to top

Back to index of questions