
Oracle 7, ODBC, SQL*Net, VB4 and WANs
Greetings.
We are in the process of deploying a series of world-wide applications which
will retrieve data from either a regional or central Oracle server. We have
two predominate groups of programs - one group is Web-based, and the other is
written using Visual Basic.
We are having problems as our VB applications move to a wide area network,
whether it's domestic frame relay or international circuits. The problem is
not performance on either side of the application, but rather the sheer number
of seemingly useless packets which are transmitted between the client and
server. Since each packet is dependent upon receiving the previous packet, we
are hitting enourmous latency problems.
Server: Sun and HP systems running various versions of Oracle 7.
Client: Windows 95 or Windows NT (4/3.51)
SQL*Net 2.x (tried 2.1, 2.2, and 2.3 with same results)
Oracle ODBC (unsure of version) as well as some Visigenic
VB4 runtime libraries
As an example, a single select statement generates the following network
traffic (note: All data sizes are TCP data sizes, not overall packet sizes):
Source Size Function
Client 18 unknown
Server 14 unknown
Client 18 unknown
Server 11 unknown
Client varies SQL statement (in this case a SELECT)
Server 38 unknown
Client 42 unknown
Server varies Column headers
Client varies unknown
Server varies Data from request
Client 15 unknown
Server 11 unknown
Client 15 unknown
Server 11 unknown
I can probably deal with the middle six packets (although I'd like to drop the
column headers), but I do not understand why the first and last four packets
are necessary. It seems that the DB engine is starting up a seperate
connection within the socket and closing it after the data has been retrieved.
Here are my questions:
1) What layer is adding the additional packets? We have changed versions of
SQL*Net and ODBC to no avail.
2) Is there a particular vendor's ODBC driver that will work better?
3) Is this problem specific to VB, or will we see it also with VC++?
4) What is the function of the first and last four packets?
5) Has ANYONE tried to deploy a world-wide application in which the database
server is located in a different area of the world (therefore going over
high-latency WAN links)?
6) Are there any good ODBC drivers which will allow the developer to call
stored procedures on the server? Any limitations to those?
7) etc
the results as appropriate.
David
Network Assessment and Optimization