Re: COMMIT frequency

On Wed, 18 Mar 1998 10:25:19 -0500, Tad Davis <davist_at_isc.upenn.edu> wrote:
>The circumstances may be unique.... the project is a Data Warehouse
>system, and our updates are generally scrubbing steps that follow the
>data load rather than preceding it. There are no update reporting
>requirements. Generally the logic is to find all rows that meet a
>certain condition (say, null organization code) and populate the missing
>data. In the event of a rerun, the cursor locates the rows that were
>missed the first time around.

OK, I'm also working on a Data Warehouse project. I understand you must be updating very large tables, as I do.

Although I haven't tested it yet, I believe a periodic COMMIT will yield better performance than a row-by-row COMMIT. When a COMMIT statement is issued, Oracle must discard or somehow mark as available all the original table blocks kept in the rollback segment by the current transaction, thus making the updates permanent, allowing other transactions to see the data you've just updated. Performing this operation e.g. 1000 times (relative to 1000 updates) or performing it only once, I *believe* performing it only once will result in less processing overhead but, since I haven't studied (yet!) the internal details of how this is done, I can't guarantee you that.

One hint - when you use SQL-Loader to load files to tables, an implicit periodic COMMIT is issued, not a row-by-row COMMIT.

If you're looking for gaining performance on your update statements, try locking the entire table (LOCK TABLE tablename IN EXCLUSIVE MODE NOWAIT;) before issuing the UPDATE statement. Of course, do this only if there are no users changing the table's data. I read on a book about Oracle Performance Tuning that doing things this way, your update statement will not need to lock every row, before updating it, resulting in better performance. I've noticed a good improvement using this technique.

Give some feedback if you reach any conclusion.

Good Luck,

Nuno Guerreiro Received on Wed Mar 18 1998 - 00:00:00 CST