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

Could it be as simple as you are performing full table scans, you never tuned the buffer cache, which results in the data immediately removed from the buffer cache.
Must be something like that, as the default parameters are way too low, and all the parameters you experimented with don't affect performance.
Sorry to say so, but Oracle can do better than that, and I know almost all people exposed to sqlserver, sybase or mysql, just start 'somewhere' and the next moment start complaining about Oracle. Which is what could be expected, as they installed it out of the box.

Regards,

Sybrand Bakker, Oracle DBA

On Tue, 17 Apr 2001 13:07:46 -0700, Jung Tjong <jtjong_at_cisco.com> wrote:

>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
>> >
>> >

Sybrand Bakker, Oracle DBA Received on Tue Apr 17 2001 - 15:54:31 CDT