Re: Very slow initial connection on Oracle 8i

Harry,

I take it back the dispatchers might be the solution!

I changed my 9.2.0.1 system to use dispatchers (MTS for those prior to 9i and connection pooling for those at 9i). Took down the database and brought it back up. I even let the database configuration assistant do it once to insure it got done as Oracle required.

The time delay stayed the same 8+ seconds. Hence, my thought using MTS/Connection pooling (disptachers) doesn't help with the issue. My gut still told me dispatchers are the solution.

I rebooted my system.

I'm NOW getting in and able to do a query via REXX/SQL in 5 seconds on the first (and only) connection.

I'm guessing restarting the database is NOT enough for Windows systems. Maybe you need to restart the Oracle database services to get connection pooling to work? Maybe it takes a complete reboot to get connection pooling to work?

So, you might try turning MTS on for your 8i database!

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote in message news:xiWH9.308$2F7.268994691_at_newssvr11.news.prodigy.com...
> 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 Sun Dec 08 2002 - 20:28:10 CET