OraFAQ Forum: Performance Tuning » Optimal Chunk size for update of 50M record table

Home » RDBMS Server » Performance Tuning » Optimal Chunk size for update of 50M record table (Oracle 11g RAC, Linux OEL 6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Optimal Chunk size for update of 50M record table [message #672106]

Wed, 03 October 2018 04:56

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Hi all,

- Large table - 50M records
- Of which I need to update 40M records.
- Production environment, while working
- Cannot prevent accessing the table - transactional data frequently read & updated ( but not the records I am updating )
- Cannot do set the table or the operation to NOLOGGING , and cannot rename/have it offline for no time whatsoever

I know it's the worst conditions possible, but I was challenged to do so numerous time by different customers.

So I tried to update the table at once - but even when setting the UNDO_RETENTION & RETENTION_GUARANTEE parameters to provide more resource for the operation - got problems with the UNDO tablespaces.

Which led me to try to update the table in chunks , every 10k records, or every 50k records e.t.c

Which leads me to the final question I am asking you DBA experts:

How can I calculate the optimal chunk size for an update on a large table in the given scenario ?

Which params do I need to take into account, which considerations do I make ?

Thanks in advance,
Andrey

Report message to a moderator

[Message index]

		Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 03 October 2018 04:56
		Re: Optimal Chunk size for update of 50M record table By: John Watson on Wed, 03 October 2018 06:48
		Re: Optimal Chunk size for update of 50M record table By: Bill B on Wed, 03 October 2018 06:59
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 03 October 2018 07:13
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Sun, 28 October 2018 11:46
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Sun, 28 October 2018 12:07
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Sun, 28 October 2018 12:40
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Tue, 30 October 2018 04:03
		Re: Optimal Chunk size for update of 50M record table By: John Watson on Tue, 30 October 2018 04:25
		Re: Optimal Chunk size for update of 50M record table By: cookiemonster on Tue, 30 October 2018 04:30
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 31 October 2018 07:32
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 31 October 2018 08:50
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Wed, 31 October 2018 08:56
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 31 October 2018 08:57
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 31 October 2018 13:31
		Re: Optimal Chunk size for update of 50M record table By: John Watson on Wed, 31 October 2018 13:37
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 31 October 2018 14:45
		Re: Optimal Chunk size for update of 50M record table By: John Watson on Wed, 31 October 2018 15:46
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Thu, 01 November 2018 04:31
		Re: Optimal Chunk size for update of 50M record table By: John Watson on Thu, 01 November 2018 04:53
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Thu, 01 November 2018 06:54
		Re: Optimal Chunk size for update of 50M record table By: John Watson on Thu, 01 November 2018 07:05
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Thu, 01 November 2018 07:17
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Thu, 01 November 2018 07:45
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Thu, 01 November 2018 07:54
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Thu, 01 November 2018 07:59
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Thu, 01 November 2018 08:09
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Thu, 01 November 2018 08:21
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Thu, 01 November 2018 09:01
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Sat, 03 November 2018 19:18
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Sat, 03 November 2018 19:33
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Sat, 03 November 2018 19:48
		Re: Optimal Chunk size for update of 50M record table By: John Watson on Sun, 04 November 2018 02:07
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Tue, 06 November 2018 03:10
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 03 October 2018 07:06
		Re: Optimal Chunk size for update of 50M record table By: John Watson on Wed, 03 October 2018 07:12
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 03 October 2018 07:27
		Re: Optimal Chunk size for update of 50M record table By: John Watson on Wed, 03 October 2018 07:30
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 03 October 2018 07:34
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Wed, 03 October 2018 07:41
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Wed, 03 October 2018 07:47
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Wed, 03 October 2018 11:13
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Sun, 28 October 2018 03:59
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Sun, 28 October 2018 08:17
		Re: Optimal Chunk size for update of 50M record table By: Andrey_R on Sun, 28 October 2018 08:45
		Re: Optimal Chunk size for update of 50M record table By: BlackSwan on Sun, 28 October 2018 08:53
		Re: Optimal Chunk size for update of 50M record table By: Bill B on Wed, 03 October 2018 07:50

Previous Topic:	A question about how to code a procedure to use dbms_parallel_execute package for a huge insert
Next Topic:	how to avoid window sort?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 09:28:58 CDT 2024