Skip to Main Content
  • Questions

  • speeding up rollback of current transactions

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, gary.

Asked: March 04, 2008 - 9:59 am UTC

Last updated: March 04, 2008 - 12:38 pm UTC

Version: 10.2.0.1

Viewed 10K+ times! This question is

You Asked

An update job against a huge table ran for 20 hours, was accidentally killed, then started rolling back for a few hours. Someone suggested we set fast_start_parallel_rollback=High, to speed-up the rollback that was currently taking place.

Is there any chance that changing fast_start_parallel_rollback could hasten a rollback that's already taking place?

and Tom said...

typically it takes *at least* as long if not *longer* to rollback.

A rollback is a logical operation, if you just did 1,000,000 row inserts and roll them back - we now have 1,000,000 deletes to perform (including the index maintenance and everything)

rollback insert = do a logical delete
rollback delete = do a logical insert
rollback update = do a logical update to put it back the way it was

row by row pretty much.


Now, you can use fast_start_parallel_rollback to try to have the rollback done in parallel - yes - but you'll want to make sure that you have sufficient free resources for it or else doing something in parallel like that will appear to have the qualities of a denial of service attack.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about current transactions

gary smith, March 04, 2008 - 11:32 am UTC

The crux of my question regards a transaction that has already started rolling back.

Can changing fast_start_parallel_rollback to 'HIGH' (it's dynamic) hasten a rollback that's already in progress?


Tom Kyte
March 04, 2008 - 12:38 pm UTC

if you crash the system and restart it - it is for "fast start", so if you shutdown abort/startup or startup force, it would.

it is for SMON, if your session is doing the rollback - not SMON - it won't affect it.

fast_start_parallel_rollback

Marcus, October 17, 2013 - 1:38 pm UTC

Maybe this could help. Do at you own risk.

alter system set fast_start_parallel_rollback=HIGH sid='*' ;

kill -9 <pid>

It will make smon do the rollback using all the resources available.

Parallel Rollback ? Could be...

J. Laurindo Chiappa, September 29, 2016 - 6:50 pm UTC

Yes, in some scenarios this could be useful, BUT in others not - for example, see metalink/MOS note "Database Hangs Because SMON Is Taking 100% CPU Doing Transaction Recovery" (Doc ID 414242.1) referencing one case where the parallel recover slaves could interfere/create concurrency one with another and the solution is to DISABLE parallel rollback/recover...
And just adding :

=> the same note have links about refs regarding SMON being blocked and/or suffering from CPU starvation

=> the ROLLBACK operation basically implies in an awful lot of I/Os to READ the data and another mountain of operations (in the Oracle dictionary and in memory) to PROCESS the read data - for sure, things like undo data being in the fastest disk/device (to help the I/O), internal/dictionay objects´ statistics and/or rescheduling of the data load and things like that COULD be in order, too...

Regards,

J. Laurindo Chiappa
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.