Net8 & ODBC: DB Response padded with 1000's of spaces

From: Jamie <jamie-20030103_at_mcleodnet.com>
Date: Sat, 04 Jan 2003 00:51:07 GMT
Message-ID: <%dqR9.84039$xp4.3495602@news1.telusplanet.net>

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 Received on Fri Jan 03 2003 - 18:51:07 CST

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