Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Large set-based UPDATE

Re: Large set-based UPDATE

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Fri, 14 Feb 2003 05:10:42 +1100
Message-ID: <pan.2003.02.13.18.10.42.569004@yahoo.com.au>


On Thu, 13 Feb 2003 08:03:56 +0000, Paul wrote:

>>
>> In which case: how often does the bulk update happen?
>
> Every night.
>
>> Why can't it be
>> shunted off into the midnight hours so it doesn't upset your web users?
>
> It runs in the early hours and processing creeps into 9am-10am. Users are
> accessing the site at all times.
>

Hang on. You're only updating 150,000 rows. Why on Earth is it taking so long to do?? I don't know what 'early hours' means, but say it's 4.00am. You're saying it's taking 5 or 6 hours to update a paltry 150,000 rows??

That's your problem if so. That's about 10 seconds per row!

If you can modify code to insert unnecessary commits, why not modify the code so it runs faster (ie, more efficient SQL)?

I'm also now confused why your are doing select for updates at all. If no-one else can modify the columns that this batch update is doing; and if the batch update can be restarted at will with 'zero' chances of logical corruption, then there can't be any possibility of encountering the situation where what you thought you were going to update changes by the time you get to update it. Therefore, there seems to be no real reason why you'd bother to pre-lock the rows in the first place. Just get rid of the 'for update' bit, and lock each row as you come across it. That will eliminate a massive select before you start to update, and should reduce running time accordingly.

Otherwise, the advice still stands. Spurious commits are not a good idea, they will probably slow things down even more, and although the locking issue might be relieved by such an approach, the load on your database will be up, and you increase the risk of 1555s.

Regards
HJR   Received on Thu Feb 13 2003 - 12:10:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US