Getting response time degradation when filling more than 1 TCP/IP blck

[jcote]

We have a Java program(OCI 8.0) that issues a SQL statement. We noticed that there is an exponential loss
of performance on the prefetch command when the amount of data returned by the prefetch doesn't fit into one
TCP/IP block. Each time a second block is needed to transmit the data to the client, a 50 millisecond overhead appears.This seems to happen only with Unix clients. On a
NT client, the response time is not affected.
Here is a snoop of the SQL. As you can see, more than oneblock is needed to transmit the data. (block #1, size 1460(which seems to be the maximum limit)
and block #4).
We verified that by snooping with data included.
ttimdb1 -> ttimdb2 TCP D=57664 S=8969 Ack=4117998560 seq=4132742338Len=1460
Win=8760
ttimdb1 -> ttimdb2 TCP D=57664 S=8969 Ack=4117998560Seq=4132743798 Len=587
Win=8760
ttimdb2 -> ttimdb1 TCP D=8969 S=57664Ack=4132744385 Seq=4117998560 Len=0
Win=8760
ttimdb1 -> ttimdb2 TCP D=57664S=8969 Ack=4117998560 Seq=4132744385 Len=189
Win=8760
ttimdb2 -> ttimdb1 TCPD=8969 S=57664 Ack=4132744574 Seq=4117998560 Len=121
Win=8760
here is a snoop of the same query except with 4 columns less in the SQL. Thus, we retrieve less data with each prefetch and the data fits into a single TCP/IP block. You see that the data all fit in one block (block #1). This takes about 50 miliseconds less than the
previous query.
zz1
ttimdb1 -> ttimd


b2 TCP D=57708 S=8969 Ack=4140641540 Seq=4162333278 Len=1460
Win=8760
ttimdb1 -> ttimdb2 TCPD=57708 S=8969 Ack=4140641540 Seq=4162334738 Len=527
Win=8760
ttimdb2 ->ttimdb1 TCP D=8969 S=57708 Ack=4162335265 Seq=4140641540 Len=121
Win=8760
Wetried lowering the number of row the prefetch return (from 10 to 7).

By doing so, the data returned by a prefetch fits into a single TCP/IP block and we avoid the 50 miliseconds wait. This improved the performance. So it is better to do more prefetch with less rows per prefetch than having less
prefetch with lots of rows...

We have put the SDU in the listener.ora file to 6000 and that didn't help.

Any Idea?

 

[MikeLacey]

I have goone through the same process you're going through now and I am convinced that you get *much* more benefit optimising your SQL and the way data is processed in the application, unless you're passing vast amounts of data to the client.

I'm sure this will offend the purists (if we *have* any purists on tek-tips that is) but I would work as if the network delay was zero -- optimise the application; ignore the network unless it's doing something *really* horrid.
Mike
michael.j.lacey@ntlworld.com

 

[jcote1]

Actually, we found a way around our problem. Net8 buffers the data it sent from the server to the client. Net8 gives you the possibility to specify the size of that buffer, throught the SDU parameter in the tnsnames.ora file and in the listener.ora file. We tried that earlier on, but it didn't work because there is a bug in the doc that dosen't tell you about this other parameter, TDU, that has to be set to enable the control over the buffer size.

By increasing the buffer, we were able to sent all of our data into one Net8 logical buffer. Doing that, we got rid of the ack that took 50 miliseconds.