ODP.NET Slow Opening of Connection Pool

  • Thread starter Thread starter Scott Logan
  • Start date Start date
S

Scott Logan

Hello!

I am using ODP.NET 9.2.0.4.01 and .NET 2003 to create a connection to
an Oracle 8.1.7 database running on Windows NT.

My problem is that it takes 32 seconds to create a new connection pool
to the database. I have a few questions if you can possibly help me (I
posted on OTN ODP.NET newsgroup 7 days ago and haven't gotten any
response)...

1) Is 32 seconds normal?
2) Is there some setting, code, or piece of hardware that I can get to
speed this up?

Many Thanks,
Scott

My code to open a connection follows :

OracleConnection conn = new OracleConnection();
conn.ConnectionString = "Data Source=prod1;User
ID=xxxxxx;Password=yyyyyy;"
conn.open();

I put on debug mode and got the following output if this helps...

TIME:2004/ 6/10-17: 5: 6:218 TID: 5d0 (ENTRY) GetRegTraceInfo()
TIME:2004/ 6/10-17: 5: 6:218 TID: 5d0 (EXIT) GetRegTraceInfo():
RetCode=0 Line=445
TIME:2004/ 6/10-17: 5: 6:218 TID: 5d0 (ENTRY)
OracleConnection::OracleConnection(1)
TIME:2004/ 6/10-17: 5: 6:218 TID: 5d0 (EXIT)
OracleConnection::OracleConnection(1)
TIME:2004/ 6/10-17: 5: 6:250 TID: 5d0 (ENTRY) OracleConnection::Open()
TIME:2004/ 6/10-17: 5: 6:250 TID: 5d0 (ENTRY) OpsConAllocValCtx()
TIME:2004/ 6/10-17: 5: 6:250 TID: 5d0 (EXIT) OpsConAllocValCtx():
RetCode=0 Line=129
TIME:2004/ 6/10-17: 5: 6:296 TID: 5d0 (ENTRY) OpsConOpen()
TIME:2004/ 6/10-17: 5:38:578 TID: 5d0 (EXIT) OpsConOpen(): RetCode=0
Line=1354
TIME:2004/ 6/10-17: 5:38:671 TID: 5d0 (ENTRY) OpsConAllocValCtx()
TIME:2004/ 6/10-17: 5:38:671 TID: 5d0 (EXIT) OpsConAllocValCtx():
RetCode=0 Line=129
TIME:2004/ 6/10-17: 5:38:671 TID: 5d0 (POOL) New connection pool
created for: "Data Source=prod1;User Id=xxxxxx;" (id: 1896611229)
TIME:2004/ 6/10-17: 5:38:671 TID: 5d0 (POOL) Total number of
connection pools: 1
TIME:2004/ 6/10-17: 5:38:671 TID: 5d0 (POOL) Total number of
connections for pool (id: 1896611229) : 1
TIME:2004/ 6/10-17: 5:38:671 TID: 5d0 (EXIT) OracleConnection::Open()
TIME:2004/ 6/10-17: 5:38:687 TID: 5d0 (ENTRY)
OracleConnection::Close()
 
Scott said:
Hello!

I am using ODP.NET 9.2.0.4.01 and .NET 2003 to create a connection to
an Oracle 8.1.7 database running on Windows NT.

My problem is that it takes 32 seconds to create a new connection pool
to the database. I have a few questions if you can possibly help me (I
posted on OTN ODP.NET newsgroup 7 days ago and haven't gotten any
response)...

1) Is 32 seconds normal?
Click to expand...

No. I get delays of a second or so mostly.
2) Is there some setting, code, or piece of hardware that I can get to
speed this up?
Click to expand...

How are your TSN names stored? The oracle connection object first has to
resolve (or better, the client has to resolve) the TSN name specified to a
server. This might cause the delay.

FB
 
Frans,

Thanks for the reply! Response time is good (under a second) when a
connection pool does NOT have to be created, but when one has to be
created, it takes around 32 seconds :(

My gut tells me it's not the TNSNames because I use the same one on my
development machine as I do on the production web server. My
TNSNAMES.ORA is stored locally on the web server and the production
entry is as follows :

PROD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx-yyy-zzz)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PR13)
)
)

Thanks in advance for any help!

Scott
 
Scott said:
Frans,

Thanks for the reply! Response time is good (under a second) when a
connection pool does NOT have to be created, but when one has to be
created, it takes around 32 seconds :(

My gut tells me it's not the TNSNames because I use the same one on my
development machine as I do on the production web server. My
TNSNAMES.ORA is stored locally on the web server and the production
entry is as follows :

PROD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx-yyy-zzz)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PR13)
)
)

Thanks in advance for any help!
Click to expand...

It's not the syntaxis but perhaps the route the webserver needs to follow to
connect to the db server :) But I don't have any more answers for you.
Perhaps you can try the support forums at otn.oracle.com.

FB
 
Frans,

Thanks for your help.

If it were the route that the webserver was taking I would imagine
that it would always take 32 seconds to grab a connection, not take 32
seconds just when a connection pool had to be created.

Unfortunately, I have tried to post to OTN with no response for 10
days, so if anyone else has an idea, I'd be happy to see your post
here.

Thanks,
Scott
 
Back
Top