Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: connection time issues

Re: connection time issues

From: <>
Date: Wed, 25 Jul 2007 22:52:53 +0200
Message-ID: <>

On Wed, 25 Jul 2007 12:16:59 -0700, Ben <> wrote:

>I have two databases on the same unix box. AIX5L
>logging into instance #1 EntEd using sqlplus windows
>cilent takes > 10 seconds.
>logging into instance #2 EntEd using sqlplus windows
>client takes < 1 sec from same client machine.
>Where do I begin? I have an SR opened with Oracle but they are taking
>forever. I've generated sqlnet.ora trace files for both and a listener
>trace file for both logons. I don't know much about reading the trace
>files though. I did notice that a sql statement that I ran as a test
>is displayed in the session's trace file, but it isn't in
> trace file.

I would first make sure it isn't network related. You would do that by running ping and/or traceroute (tracert on Winblows) from your client machine to the database server. Then I would make sure the same connection mechanisms are involved. For what it's worth, in the past connecting using SID= was slower than connecting using service_name= in tnsnames.ora. You may also may want to make sure the number of processes which can be started from the Unix oracle account is adequate (assuming you are using dedicated server).
I don't think there is any relevant information in listener trace files. You might be able to see 'process startup' in the top 5 events in a statspack report, but that is more or less a wild guess (I do have that on 9i, and I know for a fact the app connects for every individual transaction).
As there is a limit to the number of simultaneous connection requests a listener can handle you may consider including (queuesize=<n>) right behind (port=....) in listener.ora

also: lsnrctl status will show you how many connection request have been handled and rejected, and the netstat command (AIX) has option which I don't remember exactly right now, to show which ports are being used. Every individual connection will show in this report.


Sybrand Bakker
Senior Oracle DBA
Received on Wed Jul 25 2007 - 15:52:53 CDT

Original text of this message