473,327 Members | 2,007 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 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 14474
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 each. But when I wait for approx. 5 minutes and try...
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 from the MySQL tables takes a hell of a long time...
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 Windows XP pro, SP2 IIS is installed and 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 connections in a specific port, so there is a Loop ....
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 reason (IT are updating, etc) then my site won't...
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, dont have the code here but the jist is very...
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 running on a P1900 with 1gb Ram so no reason there...
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 earlier portion of this thread, changing 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 fast. Followings are my app/hardware configs: ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.