Re: connection time issues

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

>I have two databases on the same unix box. AIX5L
>
>logging into instance #1 10.2.0.2 EntEd using 10.2.0.1 sqlplus windows
>cilent takes > 10 seconds.
>
>logging into instance #2 9.2.0.5 EntEd using 10.2.0.1 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 9.2.0.5 session's trace file, but it isn't in
>10.2.0.2 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.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA