Monitoring Rollback activity for a large ETL operation with DML  (sept 1st and 1st of Ramadan) 2008

If you long running batch jobs that involve Updates, Inserts, and Delete, you can wait for long hours, not knowing what is going on and how is the progress of your operation.

I have recently found out that within the V$transaction dynamic views, there are two important fields that come to your rescue

Namely, the  USED_UBLK  and the USED_UREC

that first field shows you how many blocks are currently used up by your DML operation, and the second field shows you the no of rows that are being stored in the processed by your DML operation.

for example, i have delete four records from a specific table and inspected the V$transaction

SQL> SELECT USED_UBLK,USED_UREC FROM V$TRANSACTION;

USED_UBLK      USED_UREC
----------     ----------
1                 4
 

Sometimes, when a DML statement is affecting a large no of rows, and the statement fails to finish, an automatic rollback takes place. You can find out if this is hanppening by looking at the above two fields. if the values are decreasing with time, it means that there is a rollback operation taking place, and it the two values show you how much more is remaining for the rollback operation to finish. ie when the values become zero, this is an indication that the rollback operation is finished.

Sometimes, you kill a session that is being processed. When the status of the session is KILLED in the V$Session view and it seems to you that the session is still working, most probably the session is rolling back.  you can verify this by monitoring the two fields mentioned above.