Home » SQL & PL/SQL » SQL & PL/SQL » Table update generates large amount of Rollback
Table update generates large amount of Rollback [message #236662] Thu, 10 May 2007 10:41 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I run this update command during our standard DB application upgrade procedures:

update gateway_log
set message_seq = gateway_log_seq.nextval where message_seq is null;

For a few clients, the gateway_log table is over 1 million rows. For one particular client, this statement took over 4 hours. How can I speed up this process? Should I execute this statement as a stored procedure?
This statement has failed previously due to lack of space within my rollback segments. Both segments were sized at 1GB, but that was not enough. I increased the maxsize on the segments and the statement executed. However, the 4 hours is too long.
Any suggestions?
I'm running 8.1.7 on Win2k.
Thanks.
Re: Table update generates large amount of Rollback [message #236667 is a reply to message #236662] Thu, 10 May 2007 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without changing hardware, I see none. Wink

Pre-allocate many extents to rollback segments and avoid optimal size. This to prevent from allocating/deallocating extents.
Check if you don't have messages in alert.log like "cannot allocate new log", "checkpoint not complete"...

Regards
Michel
Re: Table update generates large amount of Rollback [message #236726 is a reply to message #236667] Thu, 10 May 2007 13:45 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
dropping any indexes on message_seq may help depending on the % of table being updated. Re-create afterwards (maybe as unrecoverable). Also - make sure cache size of sequence is reasonable (say 20 - not 1).

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:6407993912330

Re: Table update generates large amount of Rollback [message #236727 is a reply to message #236726] Thu, 10 May 2007 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...Say 10000 or more if you have 1 million rows to update. Smile

Regards
Michel

Re: Table update generates large amount of Rollback [message #236932 is a reply to message #236662] Fri, 11 May 2007 06:36 Go to previous messageGo to next message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
Yes, you have to use Stored procedure for this.
Re: Table update generates large amount of Rollback [message #236940 is a reply to message #236932] Fri, 11 May 2007 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@ShivrajGutte

Why? Explain.
Which procedure? Post.

Regards
Michel
Re: Table update generates large amount of Rollback [message #236953 is a reply to message #236940] Fri, 11 May 2007 07:13 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

I wonder if you have any other activities running and using the same rbs as this update statement ?

If you create a tablespace especially for big data loads, then you could
execute DBMS_TRANSACTION.use_rollback_segment ('your big rbs');


Re: Table update generates large amount of Rollback [message #237748 is a reply to message #236953] Tue, 15 May 2007 10:11 Go to previous message
jrich
Messages: 35
Registered: February 2006
Member
You could try something like:

update gateway_log
set message_seq = gateway_log_seq.nextval where message_seq is null AND ROWNUM <= n;

Where n is the maximum # of rows you will update at one time. This way you can control the rollback segment usage. You will need to run the above statement in a loop, commit after each update, and exit when the number of rows updated is less then n.

The only problem with this approach in your case is that you are looking for null values, which are not included in a standard index, so you would be full table scanning every update statement. If the null values are typically a small percentage of the rows, then I would try a function based index like:

create index tracknulls on gateway_log (nvl2(message_seq,null,1));

Only the rows that have a null value for message_seq would be included in the index. Then change your update statement to:

update gateway_log
set message_seq = gateway_log_seq.nextval where nvl2(message_seq,null,1) = 1 AND ROWNUM <= n;

JR

Previous Topic: Design Question
Next Topic: Trigger
Goto Forum:
  


Current Time: Fri Dec 09 07:58:02 CST 2016

Total time taken to generate the page: 0.15448 seconds