ADO.NET connections

[PRP]

Hi,

Our DBA has complained about the large number of connections from the
aspnet_wp process.

We have multiple web applications deployed in different virtual
directories. I read that the way ADO.NET in-built connection pool works
is per app domain. So in a scenario where I am using single connection
string (from web.config) and I am closing all connections as
recommended, is it correct to assume that ideal number of connections
from aspnet_wp process should be equal to the number of Asp.net web
applications that I configured?

Thanks for your help.

 

[Mark Rae]

I would suggest you implement singleton pattern for having just single
connection thoughout the application.

You have said that you are using single connnection string but there can
be
many connections open.So best practice is to have single connection open
through out the application by implementing singleton pattern

I couldn't disagree more!

Under no circumstances do that! A singleton pattern is not appropriate at
all for connections. IMO, you should only use singletons when you really
need to, e.g. for.queuing or synchronisation. Singletons can cause
concurrency/multithreading issues as well as performance bottlenecks,
especially with regards to database connections.

IMO, you should instantiate a connection only when needed, and destroy it as
soon as you're finished with it. ADO.net uses connection pooling to minimise
the creation of new connections. Keep the duration of database connections
as short as possible so that the connection is returned to the connection
pool where it can be used by the next database transaction.

A singleton pattern, by definition, uses static variables - like Highlander,
there can be only one - that's what 'singleton' means, so every user of the
website uses the same connection all the time - best hope you only have one
concurrent user of your site... In addition, a singleton pattern connection
object would not be scalable up to a web gardening or web farming
architecture.

http://www.velocityreviews.com/forums/t63537-singleton-pattern-in-aspnet-application.html

 

[p.indian001]

Thanks for the replies.

IMO, you should instantiate a connection only when needed, and destroy it as
soon as you're finished with it. ADO.net uses connection pooling to minimise
the creation of new connections. Keep the duration of database connections
as short as possible so that the connection is returned to the connection
pool where it can be used by the next database transaction.

This is exactly what we are doing in our applications. But now we are
experiencing lot of connection being open from aspnet_wp in our Oracle
database. While we identify/correct the possible long running
tasks/programming mistakes that can cause this issue, what I should be
saying to DBA as expected when he queries for v$sessions. That is
where I am confused. If I have four web apps running in IIS, connecting
to Oracle with same connection string, the ideal number of connections
at any point should be four, am I right??

 

[Mark Rae]

This is exactly what we are doing in our applications.

Glad to hear it.

But now we are experiencing lot of connection being open from aspnet_wp
in our Oracle database.

I'd imagine so - that's the whole point. Lots of very short connections as
opposed to a few long connections or, even worse, one persistent connection.

While we identify/correct the possible long running tasks/programming
mistakes
that can cause this issue, what I should be saying to DBA as expected when
he
queries for v$sessions.

I'd tell him that this is to be expected - what is his problem, AAMOI...?

That is where I am confused. If I have four web apps running in IIS,
connecting
to Oracle with same connection string, the ideal number of connections at
any
point should be four, am I right??

Er, no! The number of *connection pools* should be four.

When a connection is first opened, a connection pool is created based on an
exact matching algorithm that associates the pool with the connection string
in the connection. Each connection pool is associated with a distinct
connection string. When a new connection is opened, if the connection string
is not an exact match to an existing pool, a new pool is created.
Connections are pooled per process, per application domain, per connection
string and when using integrated security, per Windows identity.

When a pool is created, multiple connection objects are created and added to
the pool up to the minimum pool size value. Connections are added to the
pool as needed, up to the maximum pool size specified (default = 100).
Connections are released back into the pool when they are closed or
disposed.

When a SqlConnection object is requested, it is obtained from the pool if a
usable connection is available. To be usable, a connection must be unused,
have a matching transaction context or be unassociated with any transaction
context, and have a valid link to the server. I'd imagine this is the same
for Oracle, but don't know - sorry...
The connection pooler satisfies requests for connections by reallocating
connections as they are released back into the pool. If the maximum pool
size has been reached and no usable connection is available, the request is
queued. The pooler then tries to reclaim any connections until the timeout
is reached (default = 15 seconds). If the pooler cannot satisfy the request
before the connection times out, an exception is thrown.

 

[PRP]

While we identify/correct the possible long running tasks/programming

I'd tell him that this is to be expected - what is his problem, AAMOI...?

Sorry,I missed the important part; he was talking about connections
with status "INACTIVE" in v$sessions.

When a pool is created, multiple connection objects are created and added to
the pool up to the minimum pool size value. Connections are added to the
pool as needed, up to the maximum pool size specified (default = 100).
Connections are released back into the pool when they are closed or
disposed.

Thanks for the detailed explanation, and clearing my doubts.
but at what moment in this process oracle gives the status as
"Inactive"?

 

[Mark Rae]

Sorry,I missed the important part; he was talking about connections
with status "INACTIVE" in v$sessions.
Ah...


Thanks for the detailed explanation, and clearing my doubts.
but at what moment in this process oracle gives the status as
"Inactive"?

Er, when they're inactive i.e. not being used for anything... Each
connection pool has a minimum number of connections which are created when
the pool is first created.

 

[Peter Bradley]

Thanks for that. Good read.


Peter

This is exactly what we are doing in our applications.

Glad to hear it.

But now we are experiencing lot of connection being open from aspnet_wp
in our Oracle database.

I'd imagine so - that's the whole point. Lots of very short connections as
opposed to a few long connections or, even worse, one persistent
connection.

While we identify/correct the possible long running tasks/programming
mistakes
that can cause this issue, what I should be saying to DBA as expected
when he
queries for v$sessions.

I'd tell him that this is to be expected - what is his problem, AAMOI...?

That is where I am confused. If I have four web apps running in IIS,
connecting
to Oracle with same connection string, the ideal number of connections at
any
point should be four, am I right??

Er, no! The number of *connection pools* should be four.

When a connection is first opened, a connection pool is created based on
an exact matching algorithm that associates the pool with the connection
string in the connection. Each connection pool is associated with a
distinct connection string. When a new connection is opened, if the
connection string is not an exact match to an existing pool, a new pool is
created. Connections are pooled per process, per application domain, per
connection string and when using integrated security, per Windows
identity.

When a pool is created, multiple connection objects are created and added
to the pool up to the minimum pool size value. Connections are added to
the pool as needed, up to the maximum pool size specified (default = 100).
Connections are released back into the pool when they are closed or
disposed.

When a SqlConnection object is requested, it is obtained from the pool if
a usable connection is available. To be usable, a connection must be
unused, have a matching transaction context or be unassociated with any
transaction context, and have a valid link to the server. I'd imagine this
is the same for Oracle, but don't know - sorry...
The connection pooler satisfies requests for connections by reallocating
connections as they are released back into the pool. If the maximum pool
size has been reached and no usable connection is available, the request
is queued. The pooler then tries to reclaim any connections until the
timeout is reached (default = 15 seconds). If the pooler cannot satisfy
the request before the connection times out, an exception is thrown.
[/QUOTE]

 

[wfairl]

Er, when they're inactive i.e. not being used for anything... Each
connection pool has a minimum number of connections which are created when
the pool is first created.

Really? I have never seen this before, only connections open on demand.


Connection pooling works by keeping connections "established" behind
the scenes so you're going to see these Inactive sessions in v$session
but they're normal. This is the whole point of connection pooling since
it eliminates the overhead of establishing a new connection every time
you make a database call. You're also going to see at least 4 if each
of the apps has been hit at least once (since each app runs in a
separate process and connection pools are per process) but you're going
to see much more under normal use.

The connection pool mechanism in a nutshell:

1.) MyApp calls the Connection.Open().

2.) The library checks to see if there is an established but idle (not
in use by another thread in this same app) connection in the pool with
the exact same connection string and is not older than the time span
specified in the Connection Lifetime parameter of the connection string
(This is 0 by default, meaning maximum connection lifetime). If so, it
returns this connection, otherwise it establishes a new one.

3.) After you call connection.Close() the connection is still in the
pool and remains established, available for the next call to
Connection.Open()

 

[Mark Rae]

Really?

Yes really.

I have never seen this before
Nevertheless...

You're also going to see at least 4 if each of the apps has been hit at
least once

Only if each pool has a minimum pool size of 1 - almost unheard of...

 

[wfairl]

Only if each pool has a minimum pool size of 1 - almost unheard of...

I believe the default (min pool size = 0) will create no additional
connections.

 

[Mark Rae]

I believe the default (min pool size = 0) will create no additional
connections.

That's true, but why would anyone do that...?

 

[wfairl]

That's true, but why would anyone do that...?

I would ask the opposite. What good is this number unless your
application has a constant number of connections (how often is this
the case)?

 

[Mark Rae]

I would ask the opposite. What good is this number unless your
application has a constant number of connections (how often is this
the case)?

The whole point of connection pooling is the idea that incoming database
requests *don't* have to be delayed waiting for a new connection to be
created, because there's already one waiting in the pool to be used...

 

[wfairl]

The whole point of connection pooling is the idea that incoming database
requests *don't* have to be delayed waiting for a new connection to be
created, because there's already one waiting in the pool to be used...

This won't change in either scenario, the connections have to be opened
at some point. My point is that letting the application dynamically
determine the minimum size of the pool based on the usage (usually this
will vary depending on the timeframe) is ideal in many if not most
situations. Otherwise you are just introducing the overhead of
maintaining a minimum amount of connections.