Re: intermittent commit on insert ?

On Wed, 5 Jun 2002, stephen.bell_at_cgi.ca wrote:

> It's interesting....this thread almost qualifies for the "Oracle
> myths" discussion a while back...
>
> I just opened one of my PL/SQL references and confirmed what I thought
> it said - here it is :
>
> Sql tips & techniques - "Use a counter to limit transaction size"
> "...it is possible and desireable to commit transactions at certain
> time intervals throughout the processing.."
>
> "TIP: Implement the use of a counter to perform a COMMIT for a
> specified number of transactions..this technique is most commonly
> implemented within loops, particularly those based on cursors"
> etc...then goes on with sample code...
>
> Also, a question on the authors' wording....isn't a 'transaction'
> DEFINED by a commit (or rollback)? how can you code a commit for a
> "specified number of transactions"?
>
> Anyway boys and girls....I've learned not to mimic that author's
> "tips" :)

Well, I just read one of my Oracle mailing distributions.

HERE'S THE HEADERS:     From: DBA Support <listsupport_at_internet.com>     Subject: DBAnews - The DBAsupport.com Newsletter for Early June     To: <galenboyer_at_hotpop.com>
    Date: Mon, 3 Jun 2002 10:20:08 -0500

    DBAnews: DBAsupport.com Newsletter for June 1, 2002

HERE'S THE RECOMMENDATION
FROM: http://dbasupport.com/oracle/ora9i/performance/tuning-2.shtml#commit

1. Issue Frequent COMMIT Statements

    Whenever possible, issue frequent COMMIT statements in all your     programs. By issuing frequent COMMIT statements, the performance of the     program is enhancedand its resource requirements are minimized as COMMIT     frees up the following resources:

• Information held in the rollback segments to undo the transaction, if necessary
• All locks acquired during statement processing
• Space in the redo log buffer cache
• Overhead associated with any internal Oracle mechanisms to manage the resources in the previous three items

HERE'S THE AUTHOR:     About the author:

    Prashant Sarode is currently working with Syntel India Ltd as a     Technical Analyst. Syntel is a Global Technlogy Company

    that is adept at developing and managing the latest advanced     technologies such as CORBA, J2EE, .NET, XML, WAP, etc. Prashant's skill     sets include Java, EJB, C, C++, Coldfusion, VB 6.0, CGI scripts in C on     the Linux platform, MySQL, and Oracle/ Developer 2000. Prashant can be     reached at prashuss_at_hotmail.com.

Seems like I'll be cancelling my subscription to this mailing list.

HERE'S THE SPONSORSHIP:     DBAnews: DBAsupport.com Newsletter for June 1, 2002  

                  This newsletter sponsored by:  
                              IBM
                       InterSystems' Caché

I have emailed this fellow my response as well.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.