🚀 go-pugleaf

University of Munich
Nervenklinik
Nussbaumstr. 7
FRG-8000 Muenchen 2

We plan to build up a Ethernet PC-LAN with a UNIX Database back-end server
in our hospital. Our aim is to have DB-access from any PC connected to the
network.

As a prototype we are running a ORACLE 5.1.22.1 database on a COMPAQ 386/20
with SCO Xenix SysV Rel. 2.3.1. The connection to other sites is established
via TCP/IP, the DB can be accessed using ORACLE's SQL*Net.

After making a few simple performance tests, I found out that selecting a
count on a table via SQL*Net takes 3-4 times as long as selecting the same
count when logged into the DB directly.

That means, when I issue a query like "SELECT COUNT(*) FROM <table>"
when logged into the DB via SQL*Net ("SQLPLUS [NAME][/PASSWORD]
@T:<nodename>:<SID>, I have to wait much longer than I have to when
issuing it without using SQL*Net. The same applies to other SELECT
statements, although the difference in the time needed is usually much
smaller, due to the larger amount of IO.

Protocol:

$ SQLPLUS SCOTT/TIGER
SQL> SELECT COUNT(*) FROM ADDRESS;       (38 seconds)
SQL> QUIT

$ SQLPLUS SCOTT/TIGER@T:XENIX386:A       (loopback)
SQL> SELECT COUNT(*) FROM ADRESS;        (120 seconds)
SQL> QUIT

I tried to trace that problem by making more tests, but I couldn't find the
reason for this misbehavior.

Here is what I know up to know:

- All tests have been made when being the only user on the DB server.
- No additional processes were running when using SQL*Net.
- It does not make any difference, whether the SQL*Net statement comes from
  the DB-server itself (loopback) or from another client.
- The UID does not have any effect on the time needed.
- During the query, there is no data exchanged via the net.
- The results are the same for small and for large tables (40-40000 rows).
- The OS priority is exactly the same for both queries.
- In the end, both processes use the same amount of IO and CPU time.
- Monitoring the system shows that the additional amount of time needed for
  the SQL*Net process is not caused by other processes stealing resources.
  There are no processes using the CPU heavily or making lots of IO;
  actually the CPU is about 40% idle.

The only difference aside the time needed is, that one process' father
is "orasrv" (the SQL*Net process) and the other's father is "sqlplus".
Orasrv's son appears to use a lot of time to sleep for some event, of which
I don't know what it is.

What I am interested in is, whether anyone can reproduce those results or
not.
If she/he can, on which system/OS? Does this phenomena apply to all ORACLE
implementations on all OSs?

Since I see no reason, why a query (or a "count", respectively) issued via
net should be slower than one issued directly, I would like to know, whether
anyone can tell me how this comes. Did I make a mistake? If so, what was
wrong?

Please post your replies to the net.

Thanks!

UUCP:  rainbow@altger.uucp      *  The world is full of kings and queens
PATH: ...!unido!altger!rainbow  *  who blind your eyes, then steal your dreams.
Joerg Trinitis                  *  It's heaven and hell.  (DIO)
+49 089 569045 || +49 089 567962