db connection

Connection pooling can be a great way to increase performance and scalability in your ODP.NET applications; however, it is also a feature that can be confusing to figure out as there are several parameters used in the connection string that control the behavior of the feature. These parameters are all fully documented in the documentation that ships with the data provider. In brief, the primary parameters used to control connection pooling are (see documentation for additional parameters):

• Pooling - Enables or disables the connection pooling feature
• Min Pool Size - Specifies the minimum number of connections that can be in the pool
• Max Pool Size - Specifies the maximum number of connections that can be in the pool
• Incr Pool Size - Specifies the increment value (the number of connections) to be added if the connection pool needs to grow in size
• Decr Pool Size - Specifies the maximum number of connections that may be removed from the pool in a single "downsizing" operation
• Connection Lifetime - Specifies the amount of time that a connection must be active after which the connection will not be returned to the pool (i.e. it will be disposed)
• Connection Timeout - Specifies the amount of time that the provider will wait for an available connection from the pool

In ODP.NET each connection in the pool is represented by a physical connection to the database. There is a one-to-one relationship. If there are 4 connections in the pool there will be 4 corresponding connections in the database. This is another way of saying that ODP.NET connection pooling does not implement multiplexing (or sharing) of connections. Another important characteristic is that a connection pool is associated with a client process. This means that if you implement connection pooling in an application and there are 8 copies of that application simultaneously running at any given time, there will be 8 connections pools - one for each instance of the application process.

Because a connection pool is associated with a specific instance of an application it can possibly be a bit difficult to test the impact and operation of the various connection string parameters related to connection pooling. I‘ve put together a simple project that uses threads to test connection pooling. This allows the single application process to create multiple connections (and thus multiple entries in the connection pool) to the database.

The sample code invokes a supplied PL/SQL procedure in the DBMS_LOCK package to sleep for a period of time (hard-coded as 30 seconds in the below code). The allows the connection to be "held active" and helps with testing the impact of the Connection Lifetime parameter. In order for this to work, the database user must be granted execution permission on the PL/SQL package. For example (as a DBA user via SQL*Plus):

grant execute on dbms_lock to orademo;

After starting the specified number of threads (and connections) the application holds the connection as active for the determined period of time, and then disposes of the connection object placing it back into the connection pool (depending on how you set the parameters!). In order to monitor the connections in the database, I executed the following query as a DBA user via SQL*Plus:

SELECT   SID, 
         SERIAL#, 
         USERNAME, 
         STATUS, 
         to_char(LOGON_TIME, ‘DD-MON-YY HH24:MI‘) LOGON_TIME 
FROM     V$SESSION 
WHERE    LENGTH(USERNAME) > 1 
ORDER BY 3, 4, 1;

The output from the application is as follows:

Thread 1 started... 
Thread 2 started... 
Thread 3 started... 
Thread 4 started... 
Thread 5 started... 
Thread 6 started... 
Thread 7 started... 
Thread 8 started... 
Thread 1 completing... 
Thread 2 completing... 
Thread 3 completing... 
Thread 4 completing... 
Thread 5 completing... 
Thread 6 completing... 
Thread 7 completing... 
Thread 8 completing...

Paused after threads complete. 
Monitor connections using tool of choice. 
Be sure to wait several minutes (approx. 6) for clean-up to occur.

Press Enter when finished.

It is important to execute the monitoring query while the application is "paused" otherwise the application will terminate and the connection pool will be destroyed.

Here is the output of the monitoring query after the application has reached the "pause point":

SQL> SELECT   SID, 
  2           SERIAL#, 
  3           USERNAME, 
  4           STATUS, 
  5           to_char(LOGON_TIME, ‘DD-MON-YY HH24:MI‘) LOGON_TIME 
  6  FROM     V$SESSION 
  7  WHERE    LENGTH(USERNAME) > 1 
  8  ORDER BY 3, 4, 1;

       SID    SERIAL# USERNAME                       STATUS   LOGON_TIME 
---------- ---------- ------------------------------ -------- --------------- 
       135          2 ORADEMO                        INACTIVE 28-FEB-08 18:22 
       136          2 ORADEMO                        INACTIVE 28-FEB-08 18:22 
       137          2 ORADEMO                        INACTIVE 28-FEB-08 18:22 
       138          4 ORADEMO                        INACTIVE 28-FEB-08 18:22 
       139          6 ORADEMO                        INACTIVE 28-FEB-08 18:22 
       142          3 ORADEMO                        INACTIVE 28-FEB-08 18:22 
       143          3 ORADEMO                        INACTIVE 28-FEB-08 18:22 
       164          6 ORADEMO                        INACTIVE 28-FEB-08 18:22 
       134          2 SYSTEM                         ACTIVE   28-FEB-08 18:22

9 rows selected.

This shows the 8 connections from the 8 threads (adjustable) in the application and my SQL*Plus connection.

If you simply let the application sit paused for a period of time, the connections will automatically be cleaned up by ODP.NET (again, depending on the parameter values, but the values as provided in the below code allow this to occur). On my system it took approximately 6 minutes for the connection pool to be "cleaned" (i.e. reduced to the minimum of one connection based on the values I used in the connection string).

[ after waiting for about 6 minutes ]

SQL> /

       SID    SERIAL# USERNAME                       STATUS   LOGON_TIME 
---------- ---------- ------------------------------ -------- --------------- 
       164          6 ORADEMO                        INACTIVE 28-FEB-08 18:22 
       134          2 SYSTEM                         ACTIVE   28-FEB-08 18:22

2 rows selected.

This shows the connection pool has been "downsized" to the minimum number (one) I specified.

I encourage you to experiment with the different parameters and values to see how they operate and what impact they have on your system.

The Code

using System; 
using System.Threading; 
using System.Data; 
using Oracle.DataAccess.Client; 
using Oracle.DataAccess.Types;

namespace Miscellaneous 
{ 
  class Program 
  { 
    static void Main(string[] args) 
    { 
      // change connection string as appropriate and experiment with different values 
      const string constr = "User Id=orademo; " + 
                            "Password=oracle; " + 
                            "Data Source=orademo; " + 
                            "Enlist=false;" + 
                            "Pooling=true;" + 
                            "Min Pool Size=1;" + 
                            "Max Pool Size=8;" + 
                            "Incr Pool Size=1;" + 
                            "Decr Pool Size=8;" + 
                            "Connection Lifetime=60;" + 
                            "Connection Timeout=5";

      // set to number of threads / connections to use 
      const int numberOfThreads = 8;

      // create arrays for class instances and events 
      ConnectionThread[] threadArray = new ConnectionThread[numberOfThreads]; 
      ManualResetEvent[] doneEvents = new ManualResetEvent[numberOfThreads];

      // populate arrays and start threads 
      for (int i = 0; i < numberOfThreads; i++) 
      { 
        // initialize each event object in the array 
        doneEvents[i] = new ManualResetEvent(false); 
        // create a new instance of the ConnectionThread class 
        ConnectionThread ct = new ConnectionThread(i + 1, constr, doneEvents[i]); 
        // assign the new instance to array element 
        threadArray[i] = ct; 
        // Queue the thread for execution and specify the method to execute 
        // when thread becomes available from the thread pool 
        ThreadPool.QueueUserWorkItem(ct.ThreadPoolCallback); 
      }

      // wait until all threads have completed 
      WaitHandle.WaitAll(doneEvents);

      // keep application from terminating while monitoring connections in database 
      // if the application / process terminates all connections will be removed 
      Console.WriteLine(); 
      Console.WriteLine("Paused after threads complete."); 
      Console.WriteLine("Monitor connections using tool of choice."); 
      Console.WriteLine("Be sure to wait several minutes (approx. 6) for clean-up to occur."); 
      Console.WriteLine(); 
      Console.Write("Press Enter when finished."); 
      Console.ReadLine(); 
    } 
  }

  public class ConnectionThread 
  { 
    // private class members 
    private int _threadNumber; 
    private string _constr; 
    private ManualResetEvent _doneEvent;

    // parameterized constructor 
    public ConnectionThread(int threadNumber, string connectionString, ManualResetEvent doneEvent) 
    { 
      _threadNumber = threadNumber; 
      _constr = connectionString; 
      _doneEvent = doneEvent; 
    }

    // this will be called when the thread starts 
    public void ThreadPoolCallback(Object threadContext) 
    { 
      Console.WriteLine("Thread {0} started...", _threadNumber);

      // do some database work that holds the connection open 
      DoWork();

      Console.WriteLine("Thread {0} completing...", _threadNumber);

      // signal that this thread is done 
      _doneEvent.Set(); 
    }

    public void DoWork() 
    { 
      // create and open connection 
      OracleConnection con = new OracleConnection(_constr); 
      con.Open();

      // command to do the database work (simply hold connection open for 30 seconds) 
      // NOTE: execute privilege must be granted to user on the dbms_lock package 
      OracleCommand cmd = con.CreateCommand(); 
      cmd.CommandText = "begin dbms_lock.sleep(30); end;";

      // execute the anonymous pl/sql block to does nothing but sleep to hold the connection 
      cmd.ExecuteNonQuery();

      // clean up and return connection to pool (depending on connection string settings) 
      cmd.Dispose(); 
      con.Dispose(); 
    } 
  } 
}