Very slow initial connection on Oracle 8i

[Harry]

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

[Bert Bear]

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" <[e-mail]> wrote in message
news:a6********************************@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

[Bert Bear]

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" <be******@NOSPAMbertbear.net> wrote in message
news:vu*********************@newssvr12.news.prodig y.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" <[e-mail]> wrote in message
news:a6********************************@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

[Bert Bear]

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" <be******@NOSPAMbertbear.net> wrote in message
news:xi*********************@newssvr11.news.prodig y.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" <be******@NOSPAMbertbear.net> wrote in message
news:vu*********************@newssvr12.news.prodig y.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" <[e-mail]> wrote in message
news:a6********************************@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