rainbow@altger.UUCP (Joerg Trinitis) (07/04/89)
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 567962jbeard@quintus.UUCP (Jeff Beard) (07/11/89)
For our Sun3 environment with Oracle 5.1.22 the following script when run local to the DBMS vs remotely on a client shows negligible variations in through-put or resource consumption. cat sql.test # time sqlplus SCOTT/TIGER <<EOF SELECT COUNT(*) FROM emp; quit EOF time