Re: Net8 & ODBC: DB Response padded with 1000's of spaces - why?

On Sat, 04 Jan 2003 00:51:07 GMT, "Jamie" <jamie-20030103_at_mcleodnet.com> wrote:

>We have a C application running on Windows NT communicating with the Oracle
>server using ODBC and Oracle Net8 over a satellite link. The C application
>calls stored procedures on the server. Using ethereal and snoop, I can see
>that the request is tightly packed but the reply is padded with thousands of
>spaces.
>
>Due to network delay, queries that should run in one second, even with a
>satellite link, are taking anywhere from one second to three minutes. I'm
>responsible for the C application and other people are looking at the
>satellite routing priorities. I know the delays are in the network and the
>packet traces prove it. The traces also show that the responses from the
>Oracle db are consuming multiple packets due to a large number of space
>padding. I'm thinking that if the spaces could be removed performance would
>improve drastically.
>
>Calling the following stored procedure results in one packet from the client
>and 13 packets from the server. There are 34 bytes of data and 11,966 space
>characters. The spaces account for 97% of the payload.
>
> | Total
>Pkt No: 69 70 71 72 73 74 75 76 79 80 81 82 85|
>Packet: 1460 563 1460 588 1460 563 1460 588 1460 563 1460 588 29| 12,242
> |
>data: 1 4 12 17 | 34
>spaces: 1419 561 1444 575 1433 561 1444 562 1445 561 1444 517 | 11,966
>FF: 5 2 6 2 5 2 6 2 5 2 6 2 | 45
>other: 35 10 7 10 10 7 10 10 69 29| 197
>
> SQL> desc getivrdigits
> FUNCTION getivrdigits RETURNS NUMBER
> Argument Name Type In/Out Default?
> ---------------- -------- ------ --------
> P_CALLED_ID NUMBER IN
> P_USER_TYPE_CD VARCHAR2 OUT
> P_IVR_SERVICE_CD VARCHAR2 OUT
>
>We are using the following software:
> Oracle ODBC Driver 8.1.5
> Oracle Net8
> Oracle 8.1.5 Server
> Solaris8 Intel
>
>I came across a post that says to change the TSU and DSU in the
>listener.ora. This should reduce the number of packets from 13 to 9, but
>they will still be padded with spaces. According to documentation, Oracle
>recommends setting these values to increments of 1KB but setting this to 1KB
>wouldn't reduce the number of packets that much.
>
>Questions:
>1) Is there a way to eliminate or greatly reduce the number of spaces in the
>payload?
>2) Is there something that can be changed in the stored procedure?
>3) Is it possible that the ODBC function calls and/or parameters are
>affecting the DB response?
>4) Would upgrading the database and/or ODBC drivers make a difference?
>5) Can the TSU/DSU variables be set to any value or does it have to be an
>increment of 1KB?
>
>Thanks in advance.
>
>Jamie
>

 1 sqlnet packets are not padded PERIOD. 2 Changing the SDU and the TDU won't change anything 2a The SDU needs to be a multiple of the MTU of the ethernetcard, usually 1500 bytes.
3 The ODBC driver or Microsux ODBC dll's must be responsible for this 3 a the only other option is your SP is incorrectly fetching data in CHAR variables instead of VARCHAR2 variables. 4 Please trace the net8 connection. I have posted the procedure to enable tracing on level 16 many times, and it is also in the docs

8.1.5 is desupported more than a year now, please upgrade to 8.1.7

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Jan 04 2003 - 06:57:41 CST