Re: Oracle application from remote client, very slow

Krist wrote:

> We have Application package using Oracle 8i SE (on Win2K) as backend.
> The application is accessed from both local client (LAN) and
> remote client ( 64Kbps digital leased line ).
>
> These two type of clients get very difference performance.
> e.g : for data entry application
> Local client needs 2 seconds to save to backend
> Remote client needs 60 seconds to save to backend
>
> Apart from that this could be because the application and the database
> is not designed properly,

Yea and no.

The no part.
Oracle does not care if the actual SQL statement is submitted via IPX, TCP or PPP, or via a T1, 64kb leased line or 28.8k modem connection. The statement is executed the same way. No amount of physical database design can make Oracle perform an UPDATE statement faster if it is submitted via a leased line.

The yes part.
What does influence overall performance, is the size of the SQL statement. After all, that must be send to Oracle via a pipe and all pipes are not of equal size.

Repeatedly transmitting a big size SQL UPDATE statement to Oracle will be a lot slower on a PPP dialup connection, than on a 100mbit LAN.

The network bandwith thus becomes a problem, resulting in a bottleneck. How do you solve that? Obviously, increasing the bandwidth, but then that is often not a possibility. Thus, all that remains is making the SQL statement smaller - which means replacing the massive UPDATE SQL statement, with a PL/SQL stored procedure call. All you need to pass across now from the client is the bind variable values and not the complete UPDATE SQL statement. You can reduce the amount of Oracle bandwith generated by a client program by far over 50% this way.

However, this problem is not due to a "bad design" in Oracle (the design may well be perfect 3rd normal form and the UPDATE statement 100% correct). Simply that an external factor, such as limited bandwidth, is the problem that can be addressed in Oracle in such a case. (not ignoring the fact that it is often better to have your programmers use a PL/SQL packages ifo doing "their thing" with Oracle SQL)

> Is there any general setting that could be the reason ?

Yes. What also could be a problem is the way the network is configured. For example, if reverse DNS is enabled on the Oracle server, it may take the IP stack a lot longer to resolve incoming dial-in connections. This will cause dial-in clients to have a many times slower connection time.

> Or 64Kbps is not enough ?

You can answer that question yourself. Get a sniffer and check, or hire a consultancy company to draw up a network impact report for you (something I recommend doing before rolling out any client-server application).

> or any other thing that we could do without
> re-designed the application or the database ?

Besides the issue of dealing with transmissions of large SQL statements, there are other issues too - mostly on the client side.

Does the client driver use optimal TCP settings? For example, sending a 1KB SQL statement as a 100 IP packets is a lot slower than transmitting it as a single IP packet. This could be set via some Oracle ODBC drivers in the past (have not used ODBC myself for many years now).

Is the client software not at fault? For example, some years ago I did tests from a Win95 client to Oracle, using a Delphi 3 client and a VB4 client. Same SQL statement. Same Oracle ODBC driver. Same Oracle database. From the same WIn95 client.

The VB4 client generate 4x the traffic than the Delphi3 client. The reason - the way VB4 "interrogated" the Oracle database needlessly with additional SQL statements as part of its "optimising" database access (and ironically Microsoft at the time call this db layer in VB and MS Access, "The Jet Engine").

--
Billy