Query to find out progress of transaction recovery by SMON

Have you killed the long running query?

SMON is solely responsible for recovering transactions when you kill any large running query( truncate and delete ) by killing OS process or aborting database, SMON will take all possible CPU to rolling back previous state and its highly time-consuming task.
And frustrated DBA/Person will think bouncing database will resolve this problem completely, as obvious “shutdown immediate” will take equal amount of time to rollback and finally database being “aborted” by the user.
Kindly note: Shutting down or aborting database is not the solution, and won’t reduce the amount of work SMON need to be performed to complete rollback.

Following query will help you to identify the total amount of work to be rolled back by SMON, simply progress of transaction recovery. Run it multiple time.

SQL> SELECT usn, state, undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Finish at"
FROM v$fast_start_transactions;

USN        STATE            Total      Done       ToDo       Finish at
---------- ---------------- ---------- ---------- ---------- --------------------
 724       RECOVERING       112623     4658       107965     09-MAR-2017 17:30:16
 737       RECOVERING       275333     1755       273578     09-MAR-2017 21:49:03

<<After some time>>

USN        STATE            Total      Done       ToDo       Finish at
---------- ---------------- ---------- ---------- ---------- --------------------
 724       RECOVERING       28647      9324       19323      09-MAR-2017 17:16:50
 737       RECOVERING       240190     2792       237398     09-MAR-2017 20:33:12

Note:

On the same time, your ADRCI database log will be flooded with following:

2017-03-09 17:17:25.063000 +05:30
SMON: Restarting fast_start parallel rollback

You can improve database performance while the transactional recovery is in progress, check here.