Re: Too bad performance of nested table insert operation

"Min-Koo Seo" <pool0078_at_hanmail.net> wrote in message news:4c458db8.0405301821.762f82e7_at_posting.google.com...
> <snip>
> > - About your regular commits. As Tom Kyte puts it, "Only commit when
> > you really have to."
> >
> > Good luck on your research! This endeavour will yield more benefits if
> > you read the available literature on Oracle. The official manuals, Tom
> > Kyte's books, and even these forums. Well, I guess we're both on the
> > right track participating in this and all. =)
> >
> </snip>
>
> Thank you for your advice.
>
> BTW, could you possibly why I have to commit when I really have to?
>
> By commit at a regular basis, I can achieve some goals:
> - I can see the progress of my application by using select comands.
> - No *large* rollback segments are needed.
> - If my application fails, I can continue the running of the
> application from that point.
>
> TIA.
>
> Minkoo Seo

Two issues spring to mind, one practical and one philosophical.

The practical issue is that by frequent committing, you are rendering the undo generated by the first part of your transaction available for over-writing. That can give rise to ORA-1555 errors, amongst other things. I suppose that the other practical implication is that a commit forces LGWR to flush the log buffer, and that's not an exercise without costs (anything that induces physical I/O has a cost associated with it) -so there are performance implications from committing frequently.

The philosophical issue is that you should commit when your transaction is completed. Breaking a transaction up by repeatedly committing within it means you're not following a true transactional model any more. The classic definition of a transaction, I suppose, is 'either all of this happens or none of it does'. You throw in additional commits part-way through, and your definition has now to become 'some of this might happen, and some of it maybe won't'. If I am transferring money between my savings account and my cheque account, I'd really rather you didn't commit after the withdrawl from the savings account, just in case you don't quite get around to performing the insert into my cheque account. In that case, the state of my finances definitely depends on you not committing until both the withdrawal and the deposit are successfully completed.

In terms of the benefits for frequent committing that you mention, progress of an application is better handled by proper application instrumentation (in Oracle, there's even v$session_longops in conjunction with, if I remember correctly, dbms_application_info to provide that instrumentation). As for not wanting large rollback segments, that's another playing with ORA-1555s issue, and misses the point anyway: your rollback segments should be as large as your largest transactions, rather than your transactions being as small as your smallest rollback segment. Your third point is the philosophical issue I mentioned earlier: when you're playing with my bank balances, I sincerely hope they are not at the mercy of your application resumption procedures. :-)

In summary: all the reasons you advance for performing frequent commits can better be handled by other mechanisms, have costs of their own, or place data integrity at risk.

Regards
HJR Received on Mon May 31 2004 - 00:02:30 CDT