Lingering Idle Oracle Connections? Persistent connections?

  • Thread starter Thread starter jobs
  • Start date Start date
J

jobs

I've got an asp.net 2.0 site with class methods that pop in and out of
Oracle as follows.

Function GetNewOrderId() As Integer
If p_cnn.State = ConnectionState.Closed Then
p_cnn.Open()
End If
Dim cmd As New OracleCommand
With cmd
.CommandType = CommandType.Text
.CommandText = "select sq_order.NextVal from dual"
.CommandTimeout = 0
.Connection = p_cnn
End With
cmd.ExecuteScalar()
Dim result As Integer
result = cmd.ExecuteScalar()
cmd.Dispose()
p_cnn.Close()

Return result
End Function

I've reviewed every such method and all seem to be closing
connections.

We don't have any process that takes more than a few seconds. However,
when I look at session on the database I see several open for 15+
minutes.

I've asked the DBA to kill idle sessions after 10 minutes and I've
adjusted the IIS application pool to kill idle session after 10
minutes as well.

A few questions.

1. Any chance asp.net inline datasources could be causing this? Do
those close automatically after use?

2. How can I tell what an inactive connection was doing? So as to tie
it back to my code?
11 592D402C 54 13077 2255851 5929A3B8 85 PP1010 0 2147483644
INACTIVE DEDICATED 85 MYDB NETWORK?SERVICE 536:2732 MYCOMPANY
\MYSERVER01 MYSERVER01 w3wp.exe USER 00 0 5CD531E8 635300234 w3wp.exe
0 0 4444409 -1 0 0 0 11/15/2007 6:14:58 PM 185 NO NONE NONE NO
DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0

Here's one sample connection:



3. My connection string looks like this:


<add name="pp1010Connection" connectionString="Data
Source=MYDATABASE;Persist Security Info=True;User
ID=myuser;Password=mypassword;Unicode=True"
providerName="System.Data.OracleClient"/>

Should Persist Security Info be false?


Thanks in advance for any help or information!
 
use performance monitor (on the iis box) to see connection pool sizes (.net
clr data).

also your sample code does not release a connection on errors, this could
cause a leak if you get an error (say a deadlock)

use try/catch/finally blocks and close the connection in the finally.

-- bruce (sqlwork.com)
 
use try/catch/finally blocks and close the connection in the finally.
Click to expand...


Thanks.

Question, If I use try/catch and want the exception error to happen so
that my global.asax application_error still fires and my error page
still happens where i have logging writing errors to a database, how
can I do that? do i need to raise an error again?

Also, any way to close all connections for that session when an error
occurs in the Application_Error sub of the global.asax? Keeping in
mind the instance for p_cnn might be unknown in the global.asax? Would
that be an okay approach?
 
Back
Top