HELP - are use of cursors discouraged in large updates ?

[Advocate]

hello again ...

currently testing update of 3 fields in approx 45k records in table of 430k records on oracle 7.x

pl/sql package is VERY slow updating; took about 21 hours to update about 12k records yesterday; took about 2 hours this morning to update first 1k records and another 2 hours to update next 1k records

talking with colleagues and considering options; database tuning is not an option due to lack of suitable client's resources - also fiddling with indexes etc not allowed

read back through forums on slow processing and one article caught my eye from this time last year; prosenjit commented on 30 nov 2000 that "Though cursors are discouraged ..."

does this apply to any particular type of work ? any advice please ?

also, past experience has shown that inserting required records into temporary tables instead of using cursors can speed things up - any comments ?

trying to read up on optimisation - anyone have any tutorial links please ? how can i check which method is currently being used (rule-based or cost-based ?)

thank you in anticipation ...
Best of Irish Luck, David.
djwilkes@hotmail.com

 

[stefanhei]

Hi.
Rigth now I'm doing updates like you described with use of cursors. There are no problems when you do a commit every now and then (for me after 10k records), or you use a large rollbacksegment (alter your session or use transaction).
If you update a field with index on it you get very bad performance (especially if the index is on more than that specific column). I guess thats your problem. Dropping the index and recreating it after the update usualy solves that situation. Check if that is possible for you.

Stefan

 

[MikeLacey]

David,

I have to agree with Stefan. It seems to me that the use of cursors would actually be encouraged with big updates - because it allows better control of the transaction size.

It's a shame that you're not able to tune tha database and it's object as almost certainly this is where you'd get the most gain.

Apart from that - the obvious question is: How often are you committing? Every Record? (that will be *really* slow)

Try some experiments and find the best trade-off between speed of processing and size of transaction. Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[jimbopalmer]

if you are writing to JBOD (Just a Bunch Of Disks) waiting to commit until you have almost filled the rollback segments is a good plan for speed. (if you get TOO close, you need to be sure no one else is upto a similar plan)

if you use RAID, very frequently it will have non volitile write buffering in RAM, (4 meg, 24 meg, 60 meg, something)

if your redos, arches and commits are all smaller than this number, whatever it is, then they will be a RAM to RAM transfer, and appear to take 0 ms. So with RAID, one often wants smaller, more frequent commits than with JBOD. Often there is no penalty to commiting each record to NVRAM

Very large writes will more than fill the NVRAM and go back to mere disk speeds, which is no fun.

Commits are 'synchronous' writes, Oracle waits for them to complete before proceding and they must be done First in first out. By writing to NVRAM, the program can now go forward when the RAM transfer is done and not wait for the disk write so we are making the next batch ready while the disk I/O is going on. The RAID controller can now batch the disk writes in any order that it finds to be fastest.
I tried to remain child-like, all I acheived was childish.