Re: Performance question

On Fri, 30 May 2003 08:59:02 +0800, "music4" <music4_at_163.net> wrote:

>Greetings,
>
>I am developing a OCI program in C language on Unix platform. And I have a
>thread that only "delete from" and "insert into" some table. Now, I do
>commit on success on each delete and insert. But I find this way cannot meet
>the performance required. So I am considering that to do several (for
>example 10 or 20 or 100) deletes and inserts than performance a commit.
>Could that way improve performance? If yes, how many percentage could be
>improved?
>
>Thanks in advance!
>Evan
>
>

No. It is likely not going to help at all. Every transaction has a fixed overhead. Setting up smaller transactions will just increase the overhead. Look at sqlserver apps running on Oracle: they usually commit every individual record, and doing so will saturate any system.

You'll need to adequate analysis before you stick a thumb in the air and start fighting symptoms instead of causes. issue
alter session set events '10046 trace name context forever, level 8' in your program, and you'll get a trace file demonstrating *what* it is waiting for.
My initial hunch would be improperly sized online redologs, so switching and checkpointing too often.
You can easily see that from the alert (checkpoint not complete messages) and from v$log_history

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu May 29 2003 - 23:54:39 CDT