Re: Multiple processes inserting into one table

Good points from the other two replies.
You should always look at v$system/session_event to find out where you are losing time - don't forget to set timed_statistics =true.

Probable points are:

    buffer busy waits
    redo log sync/write
    latch free
    enqueue

Are there any indexes on the table -
freelists help only with tables, index entries have to go into the right place, so if 32 process have rows that have to go into a small set of blocks, you get a bottleneck.

Another possibility is contention for the segment header block on those 48 freelist - you could recreate the table with multiple freelist groups - say 6 groups with 8 freelists each.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

John O'Rourke wrote in message
<1b42a8fe.0111010621.51293346_at_posting.google.com>...


>Hi,

>We have multiple processes all inserting into one table using array

>inserts. we are running on a 24 CPU server, we are noticing that the

>performance levels off when we have 32 processes inserting into the

>table. However the CPU's aren't maxxed out. We have set the number of

>freelists to 48. We have played with the array insert size and the

>commit point but to no avail. We believe our databse is fairly well

>tuned. My question really is to improve performance would Oracle

>partitioning help us ? or would we be better splitting the one table

>into multiple tables e.g. table_001, table_002 etc, and have set

>processes inserting into set tables ?

>

>Any help greatly appreciated,

>Thanks

>John.