Re: Performance problems when using multi-threads in Oracle 9i

The law of diminishing returns applies with threads. Give your program some way of putting an upper bound on the threads you start. Test it with 1,2,4,8... threads and compare your results. Find a way of starting threads once then giving the work to the first idle thread (I used a message queue worked nicely for this). I found that 4-8 threads worked well on 8cpu Sun ES10000 when inserting into Oracle (8.0.5), 9-12 threads had no improvement in throughput, more than this and performance degraded.

Hope this helps,

"Loai Garelnabi" <gloai_at_hotmail.com> wrote in message news:804a1aa9.0204140441.25f0e01a_at_posting.google.com...
> I have a program that creates many threads. Each thread has its own
> context. The thread will connect to the database, execute a stored
> procedure and then exits.
> I have followed exactly what is required by the Oracle Documentation.
> I included:
> EXEC SQL ENABLE THREADS;
> EXEC SQL CONTEXT ALLOCATE :ctx;
> EXEC SQL CONTEXT USE :ctx;
> EXEC SQL CONTEXT FREE :ctx;
>
> This process is generating a huge load on the sever machine (100 %)
> and the program is very very slow. I'm using a Sun Fire 880, 2 800 MHZ
> CPUs and 4GB RAM, running Oracle 9i with RAC !!
>
> The program would use less load if I comment out the thread related
> statements.
>
> I would appreciate any suggestions on how to solve this probelm.
Received on Sun Apr 14 2002 - 18:14:31 CDT