Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

lunedì, luglio 25, 2011

Transaction Rollback Monitoring

V$FAST_START_TRANSACTIONS displays information about the progress of the transactions that Oracle is recovering.

alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

select usn, state, undoblockstotal "Total", undoblocksdone||' ('||round(UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100,2)||'%)' "Done",
 undoblockstotal-undoblocksdone "ToDo",
         decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))||' ('|| round((undoblockstotal-undoblocksdone)/UNDOBLOCKSDONE*CPUTIME/3600,2)||'h)' "Estimated time to complete"
  from v$fast_start_transactions;

USN STATE Total Done ToDo Estimated time to complete
63
RECOVERING
349526
194208 (55,56%)
155318
27/07/2011 16:34:04 (20,25h)
86
RECOVERING
309253
194095 (62,76%)
115158
27/07/2011 11:20:29 (15,02h)

Database Hangs Because SMON is taking 100% CPU doing transaction recovery [ID 414242.1]
How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active [ID 238507.1]
Rollback Is Taking A Long Time After Session Killed [ID 1060831.6]
How Long Will My Rollback Take? Is my Transaction Doing Anything? [ID 169138.1]

Nessun commento: