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

Jonathan,

I have 512 mbyte on my machine, and I definitely didn't see any unusual paging going on.
I just borrowed other group's oracle installation on even a more powerful machine. Sun Enterprise 250 with 2 CPU and 2 Gbyte of memory. I'm afraid I got the same result.

In my previous testing I did it all on the same machine. This time, I run my test client on my machine and over the network to the e-250 machine.

This time I just used 1000 records.
1 query about 1 second.
2 queries about 2 seconds.
3 queries about 2.5 seconds
4 queries about 3 seconds
5 queries about 4 seconds.
10 queries about 8 seconds.

Not quite a linear increasing, perhaps the network factor, but definitely
increasing almost N times the 1 query.

I tried the alter table xxx cache. No difference. The difference with your setup is Solaris and the Oracle version. I'm using 8.1.6 not 8.1.7. Could it be caused by these ?

Jung

Jonathan Lewis wrote:

> 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
> >
Received on Wed Apr 18 2001 - 12:56:33 CDT