locked
OracleClient Connection Pool is not releasing Inactive Connections RRS feed

  • Question

  • User-2096414271 posted

    Hi,

    We have an ASP.NET 2.0 Project,
    and we are connecting Oracle 10g database using OracleClient 2.0 (System.Data.OracleClient)

    Our Application is hosted on WINDOWS SERVER 2003 R2 ENTERPRISE x64 Edition IIS 6.0.

    Connection string is in web.config with following config
        "Data Source=ind;User ID=uid;password=pwd;Persist Security Info=False;Pooling=True;Min Pool Size=30;Max Pool Size=170;"

    We are facing following problems at Oracle Database Side:--
        - Some times connection goes beyond 170 like 245, 283 and so on.
        - Active Connection  - upto 20 only
        - Inactive connections are not getting free (most of the connection are inactive).

    So DBA team says Application Connection Pool is not behaving properly, has suggested us to stop using connection pooling.
    We tried to stop connection pool by "Pooling=False", but then application is not able to multiple open connections.

    Plz guide us and provide suggestions.



    Thanks
    Naween

    Friday, September 9, 2011 4:40 AM

Answers

  • User-224007220 posted

    Sorry, but the example I posted above is actually a fix for ORA-01000: Too many open cursors :)... I yust remembered.

    As for the connection pooling problem (from this blog post): 

    If you see that Oracle holds Connections of your application with invalid status check: 

    1) That you are using Oracle ODP.NET provider (version 102 or higher) and not Microsoft provider for oracle. This bug solved just in ODP.NET

    2) Ensure that you close all your open connections by closing or disposing OracleConnection. 

    3) Pay attention to those two connection string options: "Decr pool size" and "Incr pool size". "Decr pool size" set the Number of connections that are closed when an excessive amount of established connections are unused. "Decr pool size" default value is 1. ". "Incr pool size" set the Number of connections established when all connections in pool are used. "Incr pool size" defult value is 5. As you can see those default values can cause the pool to reach it maximum connection pool size default value (Default - 100). 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 9, 2011 7:02 AM

All replies

  • User-224007220 posted

    Sounds like the problem I had recently, try this... explicitly call Rollback after every SELECT query/call to kill the session, example:

                ..........
                OracleTransaction trans = oraConn.BeginTransaction();
    
                using (OracleCommand oraCmd = new OracleCommand(.....))
                {
                    try
                    {
    			// SELECT query or procedure call.
                    }
                    finally
                    {
                        try
                        {
                            trans.Rollback();
                        }
                        catch
                        {
                            throw new Exception("Rollback failed!");
                        }
                    }
                }
                ..........
    Friday, September 9, 2011 5:48 AM
  • User-224007220 posted

    Sorry, but the example I posted above is actually a fix for ORA-01000: Too many open cursors :)... I yust remembered.

    As for the connection pooling problem (from this blog post): 

    If you see that Oracle holds Connections of your application with invalid status check: 

    1) That you are using Oracle ODP.NET provider (version 102 or higher) and not Microsoft provider for oracle. This bug solved just in ODP.NET

    2) Ensure that you close all your open connections by closing or disposing OracleConnection. 

    3) Pay attention to those two connection string options: "Decr pool size" and "Incr pool size". "Decr pool size" set the Number of connections that are closed when an excessive amount of established connections are unused. "Decr pool size" default value is 1. ". "Incr pool size" set the Number of connections established when all connections in pool are used. "Incr pool size" defult value is 5. As you can see those default values can cause the pool to reach it maximum connection pool size default value (Default - 100). 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 9, 2011 7:02 AM