Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: A Nifty Commit Optimization...

Re: A Nifty Commit Optimization...

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Thu, 28 Oct 1999 12:10:12 GMT
Message-ID: <3817cf22.31244096@news.eagles.bbs.net.au>


Hi Randy,

Commits place load on LGWR and the 'redo allocation' latch. The higher your commit frequency, the harder these are to tune. Therefore your commit frequency should be LOW.

Why not use dbms_utility.get_time to commit every 5 minutes (30000 centiseconds) instead?

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.com.au/



On Mon, 25 Oct 1999 17:43:28 -0400, Randy DeWoolfson <randy_at_euclidsys.com> wrote:

>Hey all ORACLE Readers...
>
>This is not a question. I thought I would break up the monotony of the
>newbie questions and throw out this neat little trick I came up with for
>optimizing Commits.
>
>Maybe you have noticed that when you work on a large block of records,
>say parsing, manipulating and then either updating or inserting, that if
>you commit once for every record, then you get alot of overhead, and
>worse performance than if you commit say every 50 records...
>
>I had this problem, and then the following question: what exactly is
>the optimal number of records to process between commits?
>
>My first thought was to run the app several times manually tuning the
>commit frequency in the code. This worked fine until I ran the same
>test the next day and got a different number. (number of users online
>and transaction volume were variables).
>
>So realizing that there was no one correct answer, I came up with this:
>
>In the code, I picked a commit increment, and set it to say 10.
>
>then I started off through the loop processing records keeping track of
>the total time for each record plus the commit. Then I would
>programatically increment the number of records before the next commit.
>If the time per record got smaller, I would increment again etc until it
>got bigger, then i could use the resulting optimized commit interval for
>the rest of the processing...
>
>This was cool because then the program would tune itself each time it
>was run for the current environment.
>
>
>Any thoughts?
>hope you have a nice day
>
>Randy
Received on Thu Oct 28 1999 - 07:10:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US