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

I really hope it is just parameter issue. The configuration I used is not completely default. These are the parameters :

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

The query does go through the whole table, but it is a small table. Which parameters do I need increase ?

Thanks in advance,
Jung

Sybrand Bakker wrote:

> 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 - 17:42:34 CDT