Document Type | Technical Information
Category | Backup/Recovery
Applicable Product Version | 5SP1FS02
Document Number | TBATI027
Overview
This guide explains the process of checking the time taken for rollback when a transaction has not been committed and rollback is in progress after shutting down WAS or similar before shutting down the DB.
Method
If a normal DB down command (normal, post_tx, immediate, switchover) is issued while rollback is in progress, it will wait until the rollback is complete.
Checking Completion Time
Case 1. Tibero5sp1 FS02 and later versions
You can find out the completion time for rollback transactions through the EXP_RB_TIME column in V$TRANSACTION. (Unit: seconds)
- This item does not provide meaningful values for short transactions, but for long rollbacks lasting several minutes to several hours, you can roughly check the required time. However, the expected time may vary depending on the load within the DB.
SQL> select sess_id, start_time, used_blk, EXP_RB_TIME from v$transaction;
SESS_ID START_TIME USED_BLK EXP_RB_TIME
---------- -------------------------------- ---------- -----------
33 2016-04-22 19:35:32 142690 970
1 row selected.
Case 2. Versions prior to Tibero5sp1 FS02
If the version is older and the EXP_RB_TIME column does not exist in V$TRANSACTION, you can estimate the approximate completion time by calculating the speed at which the USED_BLK value decreases and the remaining amount.
- USED_BLK value in V$TRANSACTION: Number of undo blocks used by the transaction
SQL> !date
Fri Apr 22 17:27:22 KST 2016
SQL> select sess_id, start_time, used_blk from v$transaction;
SESS_ID START_TIME USED_BLK
---------- -------------------------------- ----------
30 2016-04-22 17:07:18 7537
1 row selected.
SQL> !date
Fri Apr 22 17:29:14 KST 2016
SQL> select sess_id, start_time, used_blk from v$transaction;
SESS_ID START_TIME USED_BLK
---------- -------------------------------- ----------
30 2016-04-22 17:07:18 2448
1 row selected.