Re: Multi-threaded OCI Application Does Not Scale

Acting upon previous suggestions, I have monitored V$SESSION_WAIT and V$SESSION_EVENT during program execution and have learned the following. The primary wait events in decreasing order of TIME_WAITED are "SQL*Net more data from client", "SQL*Net message from client," "log file sync," and "log buffer space." Together these wait events account for over 98% of total TIME_WAITED. To compare the behavior of threads vs. processes, I tested once using 1 process (3 threads/process) and once with 3 processes (1 thread/process). For most of the wait events, there was only slight variation in the average wait time. However, for the most important wait event the average wait time was 42.4 ms using multiple threads and only 17.3 ms using multiple processes. This finding suggests that the scaling of the "SQL*Net more data from client" wait event is responsible for the poor scaling behavior of my program's import rate (plan to verify this by studying N = 1, 2, and 4). Can anyone else (Jonathan?) provide further insight? Thanks.

Cary

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<b0rq85$9s7$1$8300dec7_at_news.demon.co.uk>...
> I think you are absolutely correct - the
> real oddity is that multiple threads don't
> scale but multiple processes do.
>
> I an rather hoping that you will see lots
> of 'sql*net message from client" as the
> wait event when you using multiple threads
> as this would tend to confirm that the
> problem was a failure at the front end
> to handle the threading.
>
> However it is possible that the bulk of
> the wait time will be an unexpected
> event like an enqueue that no-one has
> previously come across.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon a new one-day tutorial:
> Cost Based Optimisation
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> ____UK_______March
> ____USA_(FL)_May
>
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> Cary Lapoint wrote in message
> <6b0e1783.0301240741.4e22a8e4_at_posting.google.com>...
> >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
> >
Received on Mon Jan 27 2003 - 15:17:08 CST