Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow ODBC connectivity

RE: Slow ODBC connectivity

Date: Thu, 5 Feb 2004 09:12:05 -0600
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D607AFFEB0@EXCHMN3>


   Since it sounds like an in-house application, you might have the developers insert a statement to turn tracing on. Review of the trace might give you an idea of where the delays occur. From your posting, it sounds like you are assuming ODBC is the culprit, yet you also mention that you found one SQL statement that is causing significant delay itself. The question is whether the delay is due to ODBC or due to the SQL statements. Sometimes problems occur just due to the amount of network traffic back and forth between the server and the client. I think this question must be resolved before you decide on a tuning plan. A detailed trace should reveal the answer to this question. As Cary Millsap says in his book, this may be a situation where the SQL wait times are significant and can't be treated as inconsequential. Good luck, these types of situations can require a lot of time and patience to unravel.

Dennis Williams
Lifetouch, Inc.

-----Original Message-----

From: Boivin, Patrice J [] Sent: Thursday, February 05, 2004 8:23 AM To: ''
Subject: Slow ODBC connectivity

We have a problem with a remote S-Plus server that connects to Oracle 8i on UNIX via ODBC.

The Oracle 8i server is painfully slow at sending packets back to the client.

I upgraded the ODBC driver on the client to that latest 8i driver, that made a small difference. I unchecked most of the options for ODBC, that helped a bit too.

I also discovered the monster query that ODBC submits whenever it connects, picking up all the java classes in the rdbms (LOL!). The workaround for that is to create local views in the user's schema for all_synonyms and all_objects, I asked the developers if they can do that for us.

I also set SDU and TDU to the maximum values, we noticed a small performance improvement. I read about multiples of 1460 or 1461 (not clear which is best so I changed the values back down to 1460). A page on the 'net says set it to the max value, a Don Burleson page says on Ethernet set it to 1514 (I think that's what he says, "On standard Ethernet networks, the default MTU size is set to 1,514 bytes"). Don also says that Oracle recommends that MTU and SDU be the same, certainly SDU is not supposed to be larger than MTU. I am starting to wonder whether we need a second TNS listener on the unix machine, would that speed up the response time?

Any ideas on how to speed up connectivity through ODBC?

No, we can't stop using ODBC... I tried suggesting that many times.


Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Thu Feb 05 2004 - 09:12:05 CST

Original text of this message