Re: Does oracle db serialize concurrent read-only SELECT ?

I think you have a more fundamental performance problem: Your SGA is going to be around 300MB - how much memory do you have on the machine ? Is it possible that you are paging ?

It looks as if your tablescan should be automatically cached in the db_block_buffer (I estimate it will be about 250 blocks, which is less than 2% of your  db_block_buffers), but you might try
'alter table XXX cache' to see if this makes any difference.

I just built a testcase to get some idea of how bad your performance is - NT4.0, 266MHz CPU, 128MB RAM, Oracle 8.1.7. My table is 3,000 rows covering just over 2MB.

If I run 3 concurrent full scans, dumping the data to a spool file, they ALL complete within 0.8 seconds, and I can't start 5 jobs fast enough to ensure that they all run concurrently (without writing up some queuing code)..

You didn't tell us if your clients were local or remote, by the way.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Jung Tjong wrote in message <3ADCC98A.CB0DEDAB_at_cisco.com>...


>Jonathan,

>

>It's 1 CPU.  A single query takes about 1 second.

>So 5 queries when run from 5 threads, each with it's own

>connection, or from 5 processes takes about 5 seconds.

>The data per row is about 600 bytes,  INTEGER and VARCHAR(128) only

>and there are about 3000 rows in the table.

>

>First I thought it is thread/api issue, but I see the same thing

>with multiple processes.  (I tested the threading with C++ / OCI

>and Java with jdbc.)

>

>Here are other parameters values I used:

>

>open_cursors = 10000

>max_enabled_roles = 30

>db_block_buffers = 13464

>shared_pool_size = 134217728

>large_pool_size = 614400

>java_pool_size = 20971520

>log_buffer = 163840

>db_block_size = 8192

>sort_area_size = 65536

>sort_area_retained_size = 65536

>

>Jung

>