Re: Optimising an Oracle application for bandwidth

Jim Kennedy wrote:

> I think that you need to trace the entire "conversation" between Oracle and
> the VB application. It may be that the MS OLEDB driver is doing a lot of
> "talking" in addition to actually retrieving the data. One quick and dirty
> way to measure if this might be the case would be to run sqlplus on the
> client.

I've run a number of packet traces now, and have seen a number of interesting things, and have a few questions for the TNS wizards out there.

Whenever the application issues a SQL query, I see a packet containing the query go across the wire. The database responds with a packet containing the row headers for the result set before it actually provides the result set. This seems to me to be unnecessary, and, on a high latency slow link it is something I'd rather drop. Is there any way of changing this behaviour?

Also, when I do see the result set for the query returning, it arrives as one (or a stream) of TNS packets. At the end of the last packet, there is always an "ORA-01403: no data found" message.

The full conversation for a query goes:-

C> Request packet containing SQL query (359 data bytes)
S> Response packet (32 data bytes, not sure what this is)
C> Request packet (32 data bytes, not sure what this is)
S> Response packet containing column names for result set (458 bytes)
C> Request packet (8 data bytes, no sure what this is)
S> Response packet (29 data bytes, not sure what this is)
C> Request packet (224 data bytes, not sure what this is)
S> Response containing result set, and an ORA-01403 (147 bytes)
S> Above packet, sent again! (147 bytes)

 From start to end, this sequence takes 1.75 secs, for a relatively small query, returning a single row of data only. This seems a lot to me. Some of the overhead is caused by the networking side, but I can't change that, so need to optimise either the application or the database, or both.

So, can we change any parameters to reduce the chatter? There are several packets the purpose of which is unclear to me - the 224 byte request in particular is mostly null bytes with a few repeated sequences in it.

Any pointers much appreciated - in the meantime we're testing by converting some things to use server side stored procedures...

Mike. Received on Thu Nov 01 2001 - 07:28:42 CST