Multiple commits or One commit

[dlabdo]

I have a c++ program that makes a one column change to one table. Now this change could happen for 10,000 entries at a time but here's what I'm getting at. Do you think its more efficient to keep track of all the unique id's of entries i need to change, then at the end add a thousand at a time to the unit of work and commit

OR

commit after each entry is done within the c++???

Or will the efficiency even change in these two scenarios?

thanks.

 

[Thargy]

dlabdo,

interesting one this. I can see me coming unstuck under the watchful gaze of SantaMufasa if I'm not careful.

This is a combination high/low level question, IMHO.

First of all, doing single large commits is usually bad news for the undo tablespace. It has to record everything, until you do a commit. Now if it's just you, 10,000 changes will be peanuts, but what if 100 users simultaneously use the application?

You should normally keep work units as small as possible,so that undo is not onerous. Also, if an entry is posted ASAP, it makes it visible to other users, instead of disappearing down a black hole of uncommitted stuff. This means that your app is always as up to date as possible.

What if there's a screw up in mid-activity, you lose all 10,000 data? Therefore I suggest, commit ASAP, and do lots of small commits.

The "efficiency" is not really the issue here. You want reliable fast processing, not minimum RDBMS engine activity. If Oracle has to sweat a bit to meet performance criteria, then so be it.

Also, if you are using RMAN backups, it is possible to restore to a given SCN. If one change involves 10,000 records, and the cause of the problem is in there somewhere, how will you divide and conquer? If on the other hand, there are lots of small changes, you can restore to just before a particular one, and get as many good commits in as possible, before dud data causes a crash. It'll be easier to bug hunt in small quantities of data than one humungous block.

For what it's worth, my three hap'orth.

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

Amen to Tharg's comments.

Commit early and commit often.

As soon as your data changes move the company from one consistent business state to another consistent business state, then you should commit.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dlabdo]

I understand what you guys mean. I think its the best way as well but i did leave out that this program is a batch job that is run nightly. Only one instance per night is run so i guess either way wouldnt hog the system.

this job is "restartable" so if it poops out half way through they can restart it and it will start where it left off. Errrr...but its not really restartable b/c its getting its input from a table, not an input file. if it craps out half way through all it has to do is query the table again. ehhh....my head hurts...

 

[SantaMufasa]

So, dladbo, do you have any follow-on questions for Tharg and me (and others)?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Is it crutial for your application to fail say after 50-th entry? Is it re-entarable? If not, you MUST make the whole task in one transaction. 10000 records even quite wide is not an issue for database in quasy-single user mode, the speed is also expected to be better without intermediate commits, locks don't affect others (there's none ), so why do you need to commit after each record? Batch application is not the same as OLTP one.

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Ok, was in a hurry. More unambiguous:
if your application is not re-enterable and you can not resume processing after failure then you must do it in single transaction.

Regards, Dima

http://seminihin.narod.ru