| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Optimal COMMIT frequency towards Batch DMLs
Darrelll , List
Of Now, COMMIT is issued after individual DMLs corresponding to 300 application Transactions . Each application Transaction size is 5 KB approx. Thus COMMIT done after a Total of about 1.5 MB ( 5KB * 300 )
NOTE -
1) Number of Application Transactions DMLed BEFORE COMMIT can be increased to any suitable Number e.g. 500 or 1000 etc as a Very Large Number of Transactions are processed (about 500,000) in one go .
2) Rollback Size is NOT an Issue as the Size of each Application Transaction is about 5 KB
3) DML with Arrays are NOT used by our application .
Qs What are the IMPORTANT Steps that take place on COMMIT ?
Qs Which of these Steps have a High Overhead , which get reduced by reducing the frequency of Commits ?
Qs Is the Optimal Commit frequency based on Size , Time between Commits ?
Any Docs , Links please ?
Thanks
-----Original Message-----
Sent: Monday, April 21, 2003 10:29 PM
To: Multiple recipients of list ORACLE-L
Like many, many questions on this list, this one also 'depends'. This
really depends on your business needs and on your resources (mostly
space for large enough rollback segs or undo space).
I'm usually inclined to limit commits when possible. If I've got a job
which updates records and my rollback space and segment size will
accommodate it, I will first entertain the idea of updating all affected
records in one set. However, I realize I must be prepared to tolerate a
lengthy rollback in the event of a failure. So, depending on the
circumstances, I may do this update in sets based on date or some other
criteria.
You may also have multiple sets of data which get updated based on
different criteria and you may want to commit after each set if you want
set A to remain even if set B has problems. You may also have a job
where it is all or nothing where all changes must be rolled back if any
fail.
Committing too frequently can also become troublesome. I've seen a
procedure which committed for each iteration of a loop and caused itself
ORA-01555 errors.
I'd say there's probably not a rule or a document which will say with any certainty how frequent commits should be in batch. However, what I would recommend is to search for a paper on what takes place when data is committed (or one really good source is Jonathon Lewis' book "Practical Oracle 8i", committing data is covered very early in his book / how it affects data blocks, blocks in redo, blocks in rollback, etc.) and that should 'guide' you on making a decision for each of your batch jobs depending on the circumstances. One thing for certain is that the more frequently a job commits, the more work it is doing and avoiding extra work is good where the database is concerned.
>>> VIVEK_SHARMA_at_infosys.com 04/21/03 10:21AM >>>
How may the Optimal COMMIT frequency in Batch DML Operations be determined ?
What is the Spirit behind the same ?
Any Links , Docs please ?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
INET: VIVEK_SHARMA_at_infosys.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
INET: DLANDRUM_at_zalecorp.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
INET: VIVEK_SHARMA_at_infosys.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon May 05 2003 - 01:31:35 CDT
 |
 |