Re: Optimising an Oracle application for bandwidth

"Mike Brodbelt" <m.brodbelt@__NO_SPAM_PLEASE__acu.ac.uk> wrote in message news:3BE14E0A.3040102@__NO_SPAM_PLEASE__acu.ac.uk...
> 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.
>

What you see is normal sqlnet behavior.
It looks like you don't have array fetching set up (and I don't know whether that's at all possible with OLEdb) and you are getting back one record at a time.
Setting event 10046 on that particular session from the Oracle server side is probably going to confirm with.
So other than implementing array fetch, and in a later stage, try to play with the Session Data Unit of sqlnet, there's no real solution. You should however try to find out whether the application unnecessarily closes cursors.
In a previous live in a Powerbuilder app all cursors for combo and listboxes were automatically closed. Once I found out how to prevent that, it appeared sqlnet traffic was reduced with some 33 percent.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address