Re: Multi-threaded OCI Application Does Not Scale

Interesting that the program in question does exactly what yours does - loads telco data (call records, I assume) into an Oracle database. As it happens, our tables are already partitioned - by day and switch.  Still, the question remains... Why would the total import rate increase (until exhausting system resources) when launching additional program instances but remain roughly constant or decrease slightly when launching additional threads from within a single process. From this observation, I can completely rule out any sort of database bottleneck, no? From a database perspective, the two cases are identical - multiple connections/sessions, each performing array INSERT'S of the same data into the same partitions. If I am missing something, please point it out. All the same, I will try having a look at V$SESSION_EVENT during program execution to see what I see. Thanks.

Cary

vafanassiev_at_aapt.com.au (Vsevolod Afanassiev) wrote in message news:<4f7d504c.0301232210.49ec676e_at_posting.google.com>...
> Cary,
>
> As Jonathan suggested, try to determine whether the delay is occuring at
> the client (OCI program) or server (database itself).
> You can look at V$SESSION_EVENT.
> Alternatively, compare performance of OCI program
> with performance of INSERT AS SELECT or SQL*Loader -
> how long it takes to insert 100000 records, etc.
>
> If the problem is at the database level, look at INITRANS,
> FREELISTS, size of extents, etc. Also, consider partitioning.
> Depending on the nature of your data, it may be possible
> to partition by such criteria that each incoming "chunk"
> of data belongs to one partition only. For example,
> we have application that inserts data from telco switches
> into Oracle database. As each incoming file contains data
> for only one switch, inserts for Sydney switch go into
> Sydney partition, inserts for Melbourne switch go into Melbourne
> partition, and so on, and do not interfere with each other.
Received on Fri Jan 24 2003 - 09:41:49 CST