Excessive rollback time

I executed an update on a single column in a table with 225 million records via a scheduled job.  After 3 hours, I decided to kill the session and modify the query to try and get it to run faster. The subsequent rollback is estimated to take 35 hours (select used_urec from v$transaction;). It doesn't seem like a query should take 10 times longer to rollback. Is it possible that one of the database parameters is misconfigured? The SGA is approximately 20 GB.
rostaraAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
There is a parameter retention_interval in the SPFILE that says how long the entries in the UNDO should be kept.
Normally it is very big.
In your case pute there a smaller value like 5 (minutes).
So the UNDO will shrink faster
0
 
mrjoltcolaConnect With a Mentor Commented:
I also suggest maybe you need to run your db for this type of transaction.

1) Are you using explicit rollback segments or managed undo? Consider creating a specific large rollback segment in a tablespace specifically for this. Then use the rollback segment in the transaction. Put the tablespace on a different disk.

2) Do you have a lot of indexes, etc.? Maybe consolidating indexes would help overall. But a rollback should not take many times more than a query, which is why I think maybe you have IO contention (see 1).

0
All Courses

From novice to tech pro — start learning today.