Re: sql*net tuning question

"john gallet" <john.gallet_at_wanadoo.fr> wrote in message news:3B03F09F.6B7EDE66_at_wanadoo.fr...
> Hi there,
>
> Here is what I am stuck into :
>
> Oracle 8.1.6.0 running on a Solaris 2.7 sparc (big) box.
> Pro*C binary (I do not have access to the code).
>
> If I use a local connection (ORACLE_SID is set, TWO_TASK is not set),
> the binary runs in 20 minutes.
> If I do the contrary (attacking the very same Oracle instance but
> through sql*net) it runs in an hour more.
>
> Basically, the single sql request made some 30 000 times in this binary
> takes 7-9 milliseconds in the "local" configuration, and more 100-110
> through Sql-net. Its is the same binary, the same database instance.
> (of course, this is because some distant connection had this flaw, this
> is not a real-life configuration, but enabled us to rule out any network
> equipment problems like some routor or whatever).
>
> Using a sniffer, we noticed that the answer is sent in two separate
> TCP/IP packets but with an uncomprehensible 100 ms delay.
>
> I can not play with the ARRAY_SIZE parameter in the binary. I am afraid
> simply trying to send all the data in a single packet is not the answer.
> The SQL request gets from one to thirty lines, and the problem is the
> same in all cases.
>
> We contacted the Oracle support in France, played with the TCP.NODELAY
> in protocol.ora, but to no avail.
>
> Any ideas welcome...
>
> TIA
> Yours sincerely,
> John Gallet

Sqlnet packets are being send with a maximum length of S(ystem)D(ata)U(nit) bytes. The default SDU is 2048, 1 Oracle block. The standard TCP/IP MTU is 1500 bytes, the IPC MTU is on most Unixes way bigger. Try to run an iptrace, followed by an ipreport, and you probably will see your sqlnet packets being fragmented by the TCP/IP layer. You can adjust the SDU by including it in both the SID_DESC section of the listener, and the CONNECT_DATA section of tnsnames.ora. The smaller value of the two will be used. Obviously, the ideal SDU will need to be a multiple of 1500.

Hth,

Sybrand Bakker, Oracle DBA

(and oh yes: fuck Oracle Support France. This info comes from Metalink!) Received on Thu May 17 2001 - 11:04:11 CDT