Re: Connecting to database takes 5 seconds

From: Wolfram Roesler <wr_at_grp.de>
Date: Fri, 26 Jul 2002 09:52:16 +0200
Message-ID: <Xns925764F371BD3wrgrpde@62.153.159.134>

"Chuck" <chuckh_at_softhome.net> wrote in
news:ahp668$smltj$1_at_ID-85580.news.dfncis.de:

Hi,

> How are you resolving the database service name to an address?
> Tnsnames.ora, oracle names, hostname, ldap?

TNSNAMES only on both client and server.

> After connecting to the database, compare the timestamps in the
> listener.log file and the v$session table for your connection? Is
> there a difference in times? If so the problem is probably the server
> launching a server thread. If not it is probably a network induced
> delay.

v$session says logon_time=9:42:38, listener.log says:

26-JUL-2002 09:42:31 * (CONNECT_DATA=(SERVICE_NAME=ORC1.grp)

(CID=(PROGRAM=C:\Programme\oracle\ora90\bin\sqlplus.exe)
(HOST=XPW17)(USER=wr))) *
(ADDRESS=(PROTOCOL=tcp)(HOST=192.0.1.17)(PORT=1323))

* establish * ORC1.grp * 0

so my 7 seconds delay is actually there. But how coult it take Oracle 7 seconds to launch a thread? The machine definitely isn't short on memory or anything else.

I tried it with an ODBC application that asks the user for both DB connection ("data source name") and username/password. The DSN dialog appears immediately, I pick my Oracle connection, then comes the 7 seconds delay, and then I'm prompted for user name and password. After entering these the connection is established immediately. According to the ODBC logs the delay is in the SQLDriverConnect function.

> Ping the server using the same hostname as Net8 is using. Is there a
> noticable delay? Try it both by host name and IP address. This may
> tell you where the problem lies. If IP address comes back faster then
> there is a problem with host name lookup.

There is no delay, neither with ping nor with tnsping, neither with IP nor with hostname, in neither direction. So it seems that the delay is in fact not caused by network problems.

Thanks for your help
W. Roesler Received on Fri Jul 26 2002 - 02:52:16 CDT