Re: Oracle on Linux performance problem

SQLGuy_at_aol.com wrote:
>
> I am having a performance problem with Oracle 8.1.6 SE on Red Hat Linux 6.2.
> I am running a program written in Delphi 5 using the BDE to access Oracle.
> When I had Win2K and Oracle8i installed on the database server the program
> (on my client machine) would return the results of a particular query (56,000
> records) in about 30 seconds. After installing RH 6.2, Oracle8i, and the
> same database on the database server the same query now takes 9 minutes and
> 10 seconds. The server is a Compaq ProLiant dual PII 400 with 1 GB of RAM.
> I set the shared memory / semaphore parameters and rebuilt the kernel before
> installing Oracle and the SGA, etc. is setup exactly as it was on Win2K.
>
> I actually think that the problem is more of a Linux thing than an
> Oracle/Linux thing. The reason is that I ran tkprof on the query when the
> server was Win2K and now that it is Linux and I received the same results of
> 8 seconds on both. I ran top on the Linux box and observed about 25 seconds
> of activity before the CPU and I/O dropped to 0% which sounds about right.
> However, the results of the query do not show up on the client side until 9
> minutes later. Just to make sure that it wasn't a hardware issue with that
> machine I install RH 6.2, Oracle8i, and the database on another PC (PII 266
> with 192 MB of RAM) and the query takes...guess how long...9 minutes and 10
> seconds. It seems to only happen with about 10,000 plus records because if I
> only access about 5,500 records I get the same response time on the client
> side as when the database is on Win2K. So what is the problem with Linux? I
> am thinking it is some configuration issue...hopefully something simple. Any
> ideas? Please provide any input you have. I really need to have this server
> working correctly by Monday.
>

You should find out what is really the bottleneck, i.e. what determines the 9 minutes.

- I/O -> vmstat
- CPU -> top
- memory/swapping -> vmstat
- network bandwith -> /proc/net/dev 

...

Is the optimizer strategy switching when you increase the result set?

U.

-- 
Uwe Schneider - WEB.DE AG  - Amalienbadstr. 41  - 76227 Karlsruhe 
F&E / Database Technologies - Tel. (+49) 721 94329 8320
"In a world without walls and fences, who needs windows and gates?"