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

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 ?
>
> --
> 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 <3ADC8EDF.28D814E2_at_cisco.com>...
> >I'm new to Oracle, and while testing multiple
> >concurrent select request I found that
> >the server seems to serialize cursor SELECT done from multiple
> >threads or processes, Java (jdbc) or C++ (oci).
> >
> >There is no write operations on the table,
> >so there should not be any lock on it.
> >I'm doing a simple SELECT from 1 table and
> >measure the time it takes to execute the select and fetch
> >all the rows. The result of N concurrent SELECT are
> >approximately N times a single SELECT.
> >
> >I'm using ORACLE 8i - (No parallel server)
> >I tried changing server parameters that may relate to this:
> > max_cursor
> > processes
> > open_links
> > mts_servers
> > parallel_max_servers
> > parallel_threads_percpu
> >I tried changing connection network parameters in the listener.ora,
> >sqlnet.ora and tnsnames.ora files
> > server = shared
> > server = dedicated
> >
> >The results are still the same.
> >I heard that the server process is single threaded, but
> >I did set the mts_servers to N, I see the database started-up
> >N ora_sxxxx_<i>dbname</i> processes.
> >In addition for each connection I open I see that it spawn
> >one oracle<i>dbname</i> process. This seems to be the case
> >whether I set the parameter for DEDICATED or MTS.
> >I don't know which process is serializing the request.
> >
> >It's hard to believe that this is how it is supposed to behave,
> >or is it ? What do I need to do to improve this ?
> >
> >Thanks
> >
> >
Received on Tue Apr 17 2001 - 15:07:46 CDT