How to monitor transaction Rollback Progress

Suppose we execute a huge update or delete operation on a table & issued a kill statement. What happens? 

Session does not terminate immediately. It is marked for kill and the rollback is done in the background.

SQL> ALTER SYSTEM KILL SESSION ‘138,81’
/

ALTER SYSTEM KILL SESSION ‘138,81’
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> select username, status from v$session where SID=138
/

USERNAME             STATUS
——————–      ——–
SYS                            KILLED

So how do we track, whether background rollback is in progress or not?

We can query the dynamic view v$transaction. Column used_ublk will be updated frequently. The value will keep on decreasing.  Once the value becomes 0 or no row is returned from this view rollback is complete and entry will be removed from v$session.

SQL> select used_ublk from v$transaction
/
1245670

SQL> select used_ublk from v$transaction
/
1245134

We can translate this value in terms of time. But we’ll have to formulate an arithmetic for this.

Note down the time when 1st select is issued on v$transation.  Then after 1 minute, issue same statement.  The amount of the data rolled back (i.e. the difference between the values of used_ublk in 1 minute), will give us an idea of the time required to complete the rollback operation.

Advertisements
This entry was posted in Oracle Database Architecture and tagged . Bookmark the permalink.

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