
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.
Thanks, Stay tune. 🙂
This REALLY saved me yesterday, as well as the link to “speed it up”. Thank you so much.