Lingering Idle Oracle Connections? Persistent connections?

Discussion in 'ASP .Net' started by jobs, Nov 15, 2007.

  1. jobs

    jobs Guest

    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!
    jobs, Nov 15, 2007
    #1
    1. Advertising

  2. 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)


    "jobs" wrote:

    > 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!
    >
    =?Utf-8?B?YnJ1Y2UgYmFya2VyIChzcWx3b3JrLmNvbSk=?=, Nov 16, 2007
    #2
    1. Advertising

  3. jobs

    jobs Guest

    On Nov 15, 7:01 pm, bruce barker (sqlwork.com)
    <> wrote:

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



    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?
    jobs, Nov 16, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. hansiman
    Replies:
    0
    Views:
    464
    hansiman
    May 19, 2005
  2. Roy
    Replies:
    5
    Views:
    700
    Daniele Futtorovic
    Feb 12, 2008
  3. gk
    Replies:
    7
    Views:
    965
    Tom Anderson
    Oct 12, 2010
  4. shefali

    DataGrid Paging - an event lingering awaiting postback

    shefali, Jul 13, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    191
    shefali
    Jul 13, 2004
  5. Feyruz
    Replies:
    4
    Views:
    2,157
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page