Re: Very slow initial connection on Oracle 8i

Dispatchers (e.g. MTS or connection pooling) isn't the answer. Also the wait is at the Windows process level. By this I mean say ... Process 51 starts a connection (goes through the initial wait). As long as the connection is open everything is quick. If process 68 starts a connection it must also go through the same initial wait (e.g. 8+ seconds).

An initial startup wait is normal, but my thought was this one was a little on the long side (hence my thought about the dispatchers). Maybe this length of time is normal for Windows (after all it is a PC operating system, where true multi-threading has been an after thought).

One of the reasons why the OAS keeps connections open (shares connections) to the database (e.g. through a DAD) is because of the cost to open a new connection at the process/thread level. Maybe this delay is SOP. As I say, I never really thought about it till you brought it up.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote in message news:vuNH9.910$Sg3.884717740_at_newssvr12.news.prodigy.com...
> Harry,
>
> I'm also having the same problem on Oracle 9i (9.2.0.1) and Windows XP
> Professional SR 1. The system is a 512 MB (360 MB in use) and 1 GHz
laptop.
>
> I also took the suggestion of changing to IP addresses, which didn't help.
> Just as you, I see the problem with SQL*PLUS and the OCI (in my case
> SQL/REXX). The first connection takes 8+ seconds and subsequent ones are
> instantanously! If I drop all connections and then start a new
connection,
> the time again is 8+ seconds for the first one (even though I just had a
> connection going).
>
> I never really thought much about this problem till reading your note.
I'm
> wondering if the Oracle server is the bottle neck (e.g. maybe we need to
> have it start and keep dispatchers around on Windows). My guess is the
> problem comes from the server needing to initialize something (e.g.
> dispatcher) for the first time. The pieces (as I see them) fit, but till
I
> try stuff out I won't know. The solution could already be in Metalink or
> via Google. I've just been finding other things to work on than 8 seconds
> here and there. Guess, it is time to start looking at the issue.
>
> Bertram Moshier
> Oracle Certified Professional 8i and 9i DBA
>
> http://www.bmoshier.net/bertram
>
>
>
> "Harry" <a_at_abc.com> wrote in message
> news:a6fuuu4jk1sjj4h3jma32edjcejuk4670s_at_4ax.com...
> > Using Oracle 8i enterprise on win 2000 (sp3)
> >
> > Installed the standard configuration & whenever I make a connection it
> > takes about 10 secs. It's running on a P1900 with 1gb Ram so no reason
> > there for slowness.
> >
> > Once I'm connected the queries work pretty much instantanously but to
> > connect using SQLPLUS, Toad, ODBC, OLEDB all take about 10 secs. I
> > connect using OLEDB from Visual Basic development environment & after
> > the 1st connection (still in the dev env), subsequent connections are
> > instant.
> >
> > Where/what/how should I start - I'm totally baffled! - read somewhere
> > that these symtoms could be DNS related but what/how do I tell & how
> > to fix them?
> >
> > I've included my TNSNAME.ORA & LISTENER.ORA files in the vain hope it
> > might be something simple in these - the name of my database (only use
> > the one) is dev
> >
> >
> > My TNSNAME.ORA file
>
> --------------------------------------------------------------------------
> ----------------------------------------
> > # TNSNAMES.ORA Network Configuration File:
> > i:\oracle\ora81\network\admin\tnsnames.ora
> > # Generated by Oracle configuration tools.
> >
> > DEV =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS = (PROTOCOL = TCP)(HOST = p1900)(PORT = 1521))
> > )
> > (CONNECT_DATA =
> > (SERVICE_NAME = dev)
> > )
> > )
> >
> > INST1_HTTP =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS = (PROTOCOL = TCP)(HOST = p1900)(PORT = 1521))
> > )
> > (CONNECT_DATA =
> > (SERVER = SHARED)
> > (SERVICE_NAME = dev)
> > (PRESENTATION = http://admin)
> > )
> > )
> >
> > EXTPROC_CONNECTION_DATA =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
> > )
> > (CONNECT_DATA =
> > (SID = PLSExtProc)
> > (PRESENTATION = RO)
> > )
> > )
>
> --------------------------------------------------------------------------
> ----------------------------------------
> >
> >
> > My LISTENER.ORA file
>
> --------------------------------------------------------------------------
> ----------------------------------------
> > # LISTENER.ORA Network Configuration File:
> > i:\oracle\ora81\network\admin\listener.ora
> > # Generated by Oracle configuration tools.
> >
> > LISTENER =
> > (DESCRIPTION_LIST =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
> > )
> > (ADDRESS_LIST =
> > (ADDRESS = (PROTOCOL = TCP)(HOST = p1900)(PORT = 1521))
> > )
> > )
> > (DESCRIPTION =
> > (PROTOCOL_STACK =
> > (PRESENTATION = GIOP)
> > (SESSION = RAW)
> > )
> > (ADDRESS = (PROTOCOL = TCP)(HOST = p1900)(PORT = 2481))
> > )
> > )
> >
> > SID_LIST_LISTENER =
> > (SID_LIST =
> > (SID_DESC =
> > (SID_NAME = PLSExtProc)
> > (ORACLE_HOME = i:\oracle\ora81)
> > (PROGRAM = extproc)
> > )
> > (SID_DESC =
> > (GLOBAL_DBNAME = dev)
> > (ORACLE_HOME = i:\oracle\ora81)
> > (SID_NAME = dev)
> > )
> > )
>
> --------------------------------------------------------------------------
> ----------------------------------------
> >
> >
> > Many thanks
> >
> > harry
>
>
Received on Thu Dec 05 2002 - 23:09:49 CST