Re: OPTIMIZER-Problem

ralf_at_hydrogen.emi.de (Ralf Korell) wrote:
>
>Hi, DB-Gurus !

    Sorry. I'm not a Guru, but I'll try to help you :-)

>I have three major problems, which are not easy to unsterstand,
>therefore I have decided to post every problem in a single mail.

    I think that's better different e-mails. Cleaner.

>Q 1: OPTIMIZER-Problem
>Q 2: Performance-Problem (Hardware ?)
>Q 3: failsave database ?
>________________________________________________________________
>General Information : we use oracle V 7.1.4.1.0, for performance

   You can upgrade to 7.1.6. 7.2 soon.

>so far, let's start with Q 2:
>Performance-Problem (Hardware ?)
>
>We got a performance lack on our customer's machine (HP 9000/887, HP-UX 9.05):
>
>I've simulated a multi-user connect whith a simple Pro*C-program,
>which performs the same select-statment 10.000 times.

At the same time?? I did a performance test before, but with 40 process running at the same time, performing querys in random periods, simulating real users (using runform -r file) to do that. I think that's less unreal.

>On our developer machine (HP 9000/715, HP-UX 9.05) there is no
>difference in answering-time for, let's say 20 parallel connects.
>Somewhat strange happens on the HP 800 : the more connects, the higher
>the answering time ! (e.g. from 1.1 sec. for 1 connect to
>+30 seconds for 20 connects).

   Are you using HP harddisks? Are your customer using different    harddisk brand? Raid? I experienced many performance problems    with HP only because harddisks. It's only a bet.

>Network problems cannot be the reason (fast FDDI Backbone !),
>the problem is the same, when I run the program on the server.

   Are your pro*c running on Unix? Pipe driver? If it's Ok, then    network can't be the reason. Other way, it's better to test your    fast FDDI anyway.

>the tkprof-utility gives the following
>timed statistic for 7 calls of the select statement :
>on 715 (which runs fast) :
>
>call count cpu elapsed disk query current rows
>-------- ------- -------- --------- -------- -------- ------- ----------
>Parse 7 0.08 0.08 0 0 0 0
>Execute 7 0.03 0.03 0 0 0 0
>Fetch 7 0.09 0.15 27 81345 0 0
>
>
>on 800, which slows down :
>
>call count cpu elapsed disk query current rows
>-------- ------- -------- --------- -------- -------- ------- ----------
>Parse 6 0.01 0.01 0 0 0 0
>Execute 6 0.00 0.00 0 0 0 0
>Fetch 6 4.54 12.46 0 82680 0 0

   If you see, query blocks on both tests are the same. Disk access    are the same too. So, it's almost sure that both Oracle servers    are doing the same thing. But you have much more CPU time for    fetching in the second case.

   for only 6 executions! It's too slow. Without doing more queries    access?! Probably problem with your HP-UX. Probably disk access is    spending much more time at the OS leve. Try some C programs to test    file I/O performance.

   I think that testint your I/O at C level is a good point to start.

>Qs : Has anybody experiences with oracle on a HP 800 ?
> Is this really a hardware-problem ?
> Is there a solution ?

   Probably. I use Oracle on many HP. I prefere Sun, IBM or even some    PCI Pentiums. HP is great, but I/O is not so good (yet?!).

>Please e-mail me directly and I will post a digest in the
>newsgroup.

   I e-mail Cc: comp.databases.oracle
> ,,,
> (o o)
>~*~~*~~*~~*~~*~~*~~*~~*~~oOO~~(_)~~OOo~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~
> Please apologize but english is not my native language

   Neither mine! I speak portuguese!

Regards,

Carlos Netto
cnetto_at_cps.softex.br
Software Design
Oracle Consultants Received on Sat Aug 12 1995 - 00:00:00 CEST