Look at it like this. Consider all 3 factors:
1) In this current transaction (which can do any amount of work), my
need is to do N amount of work, then save that work before I move on.
Right here, I commit.
2) If this current transaction encounters a problem and has to
rollback, can I tolerate the length of time it takes to roll back before
I can correct the problem and move on. If not, I commit more
frequently. If so, then I commit less frequently. (However, I'd put
more effort into coding to avoid or handle problems than worrying about
rollback time.)
3) Is there a need for other sessions to read the data blocks which
contain rows that I am changing. If so, those sessions will get those
blocks from rollback segments. If my transaction causes others to get
"snapshot too old" errors, then the right thing to do is properly size
the rollback segments.
Committing more frequently "too keep rollback size small" can be a very
problematic cause of snapshot too old errors.
>
> From: "VIVEK_SHARMA" <VIVEK_SHARMA_at_infosys.com>
> Date: 2003/04/22 Tue AM 09:16:37 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: Optimal COMMIT frequency towards Batch DMLs
>
>
> COMMIT is issued after DMLs corresponding to 300 application
Transactions ,
> each of Transaction size about 5 KB . Thus COMMIT done after about
1.5 MB
>
> NOTE - DML with Arrays are NOT used by our application .
>
> Is the Optimal Commit frequency based on Size , Time between Commits
?
>
> Which Sub-operations on COMMIT have MAX. overhead which are thereby
reduced by reducing the frequency of Commits ?
>
> 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).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <rgaffuri_at_cox.net
INET: rgaffuri_at_cox.net
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).
Received on Tue Apr 22 2003 - 11:11:48 CDT
