mortenmo
asked on
Slow to connect to oracle w/ JDBC & SQLPLUS over network
We're having a problem where it takes up to 12 seconds to establish a new connection over network to a Oracle Database (running on a 16 processor HP box). The Client runs on Win2k. Both JDBC and SQLPLUS takes 8-12 seconds to connect to Oracle, but after the connection is established, the queries are lightning fast (so the problem is solely in the connect area).
I assume it can be the TNS listner. Running tcp sniffers we can see the connection is trying to be established. The client sends a TCP message "ACK TOO LONG" then Oracle responds with keep alive. Nothing happens for about 6-10 seconds before Oracle asks to retransmit and at that time a new connect is sent.
Does anyone know what can be wrong? Could it help to setup a seperate TNSListner for this? Is there an ultimate setup? Could it be something else than the TNS listner?
Any help would be appriciated greatly!
- Morten
I assume it can be the TNS listner. Running tcp sniffers we can see the connection is trying to be established. The client sends a TCP message "ACK TOO LONG" then Oracle responds with keep alive. Nothing happens for about 6-10 seconds before Oracle asks to retransmit and at that time a new connect is sent.
Does anyone know what can be wrong? Could it help to setup a seperate TNSListner for this? Is there an ultimate setup? Could it be something else than the TNS listner?
Any help would be appriciated greatly!
- Morten
are you using the oracle MTS - the shared server mode for the DB connections, if that is the case then inital connection takes a bit longer than the dedicated server mode (which is oracle default)
As arun04 suggested above, if you are using the MTS or even connection pooling, you will experience delays in connecting. The "ultimate" setup for you would be to have dedicated connections and do away with MTS. But obviously you would need more resources on the server to manage this. Alternatively you can create a separate entry in tnsnames file for dedicated connections and use them for "special" cases.
Ali
Ali
Few things:
If you login the HP box, connect to database there using sql*plus (sqlplus usrname/passwd@tns_alias),
Can you post the version of your
OS (HP-UX 11?) and Oracle (8.1.7?). Also
if the OS is 64bit and Oracle is 32bit?
Can you check if /etc/hosts has the
permission -rw-r--r--?
If you login the HP box, connect to database there using sql*plus (sqlplus usrname/passwd@tns_alias),
Can you post the version of your
OS (HP-UX 11?) and Oracle (8.1.7?). Also
if the OS is 64bit and Oracle is 32bit?
Can you check if /etc/hosts has the
permission -rw-r--r--?
Are you using names resolution using an Oracle names server or DNS etc? Use a plain IP address in your tnsnames.ora file and see if that helps.
Is the network you mentioned a LAN or a WAN
sora
Is the network you mentioned a LAN or a WAN
sora
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY. Moderators Computer101 or Netminder will return to finalize these if still open in seven days. Please post closing recommendations before that time.
Question(s) below appears to have been abandoned. Your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response. This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database. If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
--> Post comments for expert of your intention to delete and why
--> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.
For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process. https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.
Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added. When you grade the question less than an A, please comment as to why. This helps all involved, as well as others who may access this item in the future. PLEASE DO NOT AWARD POINTS TO ME.
To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20252743.html
https://www.experts-exchange.com/questions/Q.20252278.html
https://www.experts-exchange.com/questions/Q.20284615.html
https://www.experts-exchange.com/questions/Q.20289697.html
https://www.experts-exchange.com/questions/Q.20289800.html
***** E X P E R T S P L E A S E ****** Leave your closing recommendations if this item remains inactive another seven (7) days. If you are interested in the cleanup effort, please click this link https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed here -> https://www.experts-exchange.com/commspt/Q.20277028.html
Moderators will finalize this question if in @7 days Asker has not responded. This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
Thank you everyone.
Moondancer
Moderator @ Experts Exchange
Question(s) below appears to have been abandoned. Your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response. This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database. If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
--> Post comments for expert of your intention to delete and why
--> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.
For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process. https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.
Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added. When you grade the question less than an A, please comment as to why. This helps all involved, as well as others who may access this item in the future. PLEASE DO NOT AWARD POINTS TO ME.
To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20252743.html
https://www.experts-exchange.com/questions/Q.20252278.html
https://www.experts-exchange.com/questions/Q.20284615.html
https://www.experts-exchange.com/questions/Q.20289697.html
https://www.experts-exchange.com/questions/Q.20289800.html
***** E X P E R T S P L E A S E ****** Leave your closing recommendations if this item remains inactive another seven (7) days. If you are interested in the cleanup effort, please click this link https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed here -> https://www.experts-exchange.com/commspt/Q.20277028.html
Moderators will finalize this question if in @7 days Asker has not responded. This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
Thank you everyone.
Moondancer
Moderator @ Experts Exchange
Morten,
Please close this question if you are through.
--- k_murli_krishna
Please close this question if you are through.
--- k_murli_krishna
Finalized by Moondancer - EE Moderator