| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow ODBC connectivity
Patrice
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
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: Boivin, Patrice J [mailto:BoivinP_at_mar.dfo-mpo.gc.ca]
Sent: Thursday, February 05, 2004 8:23 AM
To: 'oracle-l_at_freelists.org'
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.
Patrice.
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |