Solved

Excessive rollback time

Posted on 2009-04-03
3
814 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:rostara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 250 total points
ID: 24060563
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
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 250 total points
ID: 24062202
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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question