ADO.Net Connection Pooling Problem with Oracle

[Bird]

Howdy,

I recently implemented an ASP.Net application and am having trouble
with running out of database connections. I've read many articles on
the web concerning pooling, but have yet to find one that really
describes in detail how pooling is supposed to behave. My problem is
that over time, the number of database sessions in Oracle seem to
increase to the point where we start getting the "Maximum number of
sessions exceeded". I've already increased the SESSIONS init parameter
in Oracle from 150 to 300, but we're coming pretty close to this limit.
I've been monitoring the sessions using the V$SESSION view in Oracle
and there are sessions with the same connection string (same USERNAME).
For instance, one user may have over 100 database sessions. Here's a
sample of what my connection strings look like:
"Pooling=true;Data Source=mydb;Password=pass;User ID=fred"

I believe the default max pool size is 100, so why do I see over 100
database sessions for a particular user, not to mention there can be
200 or more for the whole db?

I use a shared component for all database executions (queries or
updates). I've triple checked this component and I'm closing the
database connections everywhere they're used. Everywhere I close them,
it's done in the Finally portion of a Try-Catch block. For example:

Dim connOracle As OracleClient.OracleConnection
Dim cmdOracle As New OracleCommand
Dim adpOracle As OracleClient.OracleDataAdapter
Dim dsOracle As New DataSet
Dim sConnectionString
Dim sSQL

Try
sConnectionString = "Pooling=true;Data
Source=mydb;Password=pass;User ID=fred"

sSQL = "SELECT Username FROM Users"

'Open a connection - get from pool if already one in pool
connOracle = New OracleConnection(sConnectionString)
connOracle.Open()

cmdOracle.Connection = connOracle
cmdOracle.CommandType = CommandType.Text
cmdOracle.CommandText = sSQL

adpOracle = New OracleDataAdapter(cmdOracle)
adpOracle.Fill(dsOracle)
Finally
If connOracle.State <> ConnectionState.Closed Then
connOracle.Close()
End If
adpOracle = Nothing
connOracle = Nothing
cmdOracle = Nothing
End Try

When I first discovered the pooling problem, I thought it was because I
wasn't closing the db connections. I was using DataReaders and it
wasn't easy to close the connections using a shared component, so I
changed everything to use DataSets. This seemed to improve the
situation, but it didn't completely solve it. I've talked with Oracle
Support and they say this is not a database issue since the database
doesn't close the connections. They leave it up to the application. If
connections are orphaned, you have to implement what they call the Dead
Connection Detector if you want Oracle to clean them up. I did this,
but ran into some very serious problems as a result (CPU utilization
was hitting 99% after 3 to 4 hours). I don't think Oracle should have
to clean up every database connection that is created from ADO.Net, so
I'm now going to the root of the problem - the application.

I've also done some testing with non-pooled connections. I was seeing
multiple Oracle sessions for the loading of one ASP.Net page. I ran the
code through the debugger and verified that the db connections were
being closed. I was confused at first, but then realized this was
because the garbage collector didn't clean things up yet. I added a
call to System.GC.Collect and sure enough, no database sessions were
left open (in V$SESSION table) after loading the page. It's not
feasible to collect the garbage everytime I close a database connection
and establishing new connections everytime an SQL statement runs kills
performance. Isn't that what connection pooling is for????? Why isn't
this working?????

For our environment, we're using version 1.1.4322 of the .Net
Framework, Microsoft's OracleClient ADO.Net Provider and version
9.2.0.6 of the Oracle database. We're running this on Windows Server
2003 WITHOUT service pack 1, but all of the latest critical patches.

I would really appreciate anybody's help in solving this problem.

Bird

 

[Guest]

Bird,

I don't have your answer as far as OracleClient. However I have some
thoughts and questoins on your issue.

First do you have a development environment to work with? Can you replicate
the problem there? If so then try using OLEDB instead to see if the problem
still replicates.

What happens when you turn connection pooling off? What I'm wondering is if
the CLR is releasing the connection to your database. I would think that the
CLR is not releasing, Oracle has no idea of what is going on and provides a
new connection because the last one is still in use.

What happens when you move connOracle.Close up to the try section of your
try block, leaving the existing code in place?

Has your code been promoted to production or is it still in debug mode?

 

[Bird]

Matthew,

I do have a development environment, but why would I want to use OLEDB
which I thought added another layer to the data access interface? I'm
trying to solve the problem with the OracleClient.

When I turn off connection pooling, then the database gets overloaded
with database connections as soon as people start accessing the
application. I don't think .Net releases the connections until the
garbage collector cleans them up. I've experimented with this and
that's essentially what I saw. See the 2nd last paragraph of my
original message.

Moving connOracle.close up to the try section of the try block does
nothing different. The finally part of the try block always gets
executed whether there is an exception or not unless the exception is
within the code within the finally section.

This code is in production.

Can anyone give an example of a pooled database connection's life
cycle? I haven't read anything that tells me what causes a connection
to be put into the pool and when does a connection get taken from the
pool. Is the connection immediately put into the pool upon executing
the .close method of the connection object or does it wait until after
the connection object is cleaned up by the garbage collector? Does .Net
always get a pooled connection if one exists for the same connection
string or are there exceptions? How does Oracle interact with .Net?
Does .Net pool just one connection for each unique connection string or
does it do something similar to what OLEDB did in ADO 2.5-2.6, where it
creates n+nbr processors connections?

If .Net will not use connections that have been closed but have not
been garbage collected, then what the hell good is pooling? Or should I
be collecting garbage everytime I close a database connection? Isn't
garbage collection an intensive process, hence the reason it runs in
the background? Problem with calling System.GC.Collect() explicitly is
that I'm open multiple connections for each web page that's loaded. I
have a data access component that returns datasets and closes the
connections beforehand so I don't have to worry about closing
connections on the calling side.

Thanks,
Bird

 

[Bird]

I think I've solved the problem (at least one problem). I have a COM+
component that I use for database updates. This component's Transaction
Attribute is set to TransactionOption.Required. I have other COM+
components for my data layer that perform queries and updates. At least
one of these components has its Transaction Attribute set to
TransactionOption.Required. Since I didn't associate transactions with
queries, some of the exposed methods on these components are not
calling ContextUtil.SetComplete or ContextUtil.SetAbort (I know, stupid
mistake. I always called SetComplete or SetAbort with VB6 and MTS). If
I do not call SetComplete or SetAbort within a particular method, it
will create a new database connection for every new COM+ transaction.
For example, I have component (class library) A which has an Execute
method that calls the OracleCommand.Execute method to run a stored
procedure. Component B has a Validate method which in turn calls
component A's Execute method. Both components have a Transaction
Attribute of TransactionOption.Required. Everytime I call Component B
from an ASP.Net page, it will create a new database connection because,
I guess, it was not a valid pooled connection because of the open COM+
transaction.