472,353 Members | 2,236 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

Very slow initial connection on Oracle 8i

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
Jul 19 '05 #1
3 14347
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@abc.com> 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

Jul 19 '05 #2
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" <a@abc.com> 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


Jul 19 '05 #3
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" <a@abc.com> 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



Jul 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Kevin Vogt | last post by:
When initially loaded, my web application (below) takes ~120000 milliseconds to open its connection. But repeated executions take 0 milliseconds...
11
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data...
6
by: B B | last post by:
Okay, here is what's happening: I have a reasonably fast laptop (1.4 GHz Mobile M, so comparable to 2.5GHz P4) doing .net development. Running...
5
by: PH | last post by:
Hi guys; I got a single processor computer, running an application that launches 2 threads. Each of these threads listens for incoming...
0
by: Kevin Blount | last post by:
I have a site that uses a connection to an Oracle server, and I'm looking to make it intelligent enough so that if the database is offline for any...
9
by: dan | last post by:
within a loop i am building a sql insert statement to run against my (programatically created) mdb. it works but it seems unreasonably SLOW! Sorry,...
3
by: Harry | last post by:
Using Oracle 8i enterprise on win 2000 (sp3) Installed the standard configuration & whenever I make a connection it takes about 10 secs. It's...
2
by: =?Utf-8?B?SmltIE93ZW4=?= | last post by:
Hi John, Hopefully this post will find its way back to you - or perhaps be answered by someone else. As I mentioned in my last post on the...
5
by: Med | last post by:
Hi, Initial load of my pre-compiled ASP.NET app takes more than 50 seconds to load after site has been idle for a while. Then it become fairly...
1
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.