RE: ADO.Net Connection Pooling Problem with Oracle
- From: "Matthew Holton" <MatthewHolton@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 5 Apr 2005 09:21:01 -0700
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" wrote:
> 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
>
>
.
- Follow-Ups:
- References:
- Prev by Date: Binding a DataRelation to a Datagrid??
- Next by Date: Re: SQLConnection connection pool issue
- Previous by thread: ADO.Net Connection Pooling Problem with Oracle
- Next by thread: Re: ADO.Net Connection Pooling Problem with Oracle
- Index(es):
Relevant Pages
|
