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

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

Jonathan Lewis wrote:

> How many CPUs in the server, what sort of
> time-scale, what are you running the
> multiple concurrent clients on, and how much
> data is your query returning from each row ?
>
> There are lots of ways in which a simple test
> may be giving the wrong impression about
> concurrency.
>
> --
> 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 <3ADCA291.28BB234_at_cisco.com>...
> >Jonathan,
> >
> >The table I'm querying has only about 3000 records containing
> >rows that have only about 15 int and string columns, totalling
> >to about .5k bytes. I did a select on all rows, so now where clause.
> >It's a very simple query.
> >
> >My test program takes only a few % of the cpu while running,
> >and each of the spawned process / connection seems to take
> >only 0.x % of the cpu. I don't see any abnormal disk accesses.
> >So I don't think it is straining the SUN ultra-60 server that I'm
> >running this on.
> >The same test on MySQL database took nearly the same
> >time whether I run one query or multiple concurrent queries.
> >
> >Jung
> >
> >Jonathan Lewis wrote:
> >
> >> Oracle does not do anything to serialized
> >> read-only queries - however you could get
> >> this type of behaviour if you query were
> >> CPU bound or I/O bound in the first place.
> >>
> >> Are you running a small volume query
> >> to do the test, or are you (for example)
> >> running a tablescan across a 256MB
> >> table as the base query ?
Received on Tue Apr 17 2001 - 17:54:02 CDT