Oracle.ManagedDataAccess Connection request timed out

Question

I'm having trouble with threaded code which I'm using with the Oracle ManagedDataAccess.dll. To simulate the issue I have created a small test application that opens a connection, does a query and closes the connection.

The thing that happens is that randomly a "Connection request timed out" (ODP-1000) occurs in the conn.open() statement when I run this code. Now I have done plenty of Googeling out there and it tells me that the pool size is too small, however this is not the case here because then I would get a "Pool connection timed out" exception (ODP-1012). I have played around with the connection string properties as well, and in the test application I can get it to war flawlessly when I set a greater "connection timeout" property, but the point is that this doesn't help me with the application I'm working on.

Any help or insight would be greatly appreciated!

 class Program  
    {  
        static readonly object _object = new object();  
        static string connectionstring = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oralinux.contoso.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DB)));User Id=system;Password=xxxxxx;Pooling=True;Min Pool Size=;Max Pool Size=20;Incr Pool Size=10;Decr Pool Size=1;Connection Lifetime=0;Connection Timeout=1;Self Tuning=false";  
        static string query = "select to_char(max(end_time),'Mon-DD-YYYY HH24:MI:SS') \"SPFILE\" from V$RMAN_STATUS where object_type='SPFILE' and status='COMPLETED'";  
        static void Main(string[] args)  
        {  
            for (int i = 0; i < 1000; i++)  
            {  
                Thread myNewThread = new Thread(DoWork);  
                Console.WriteLine(i.ToString());  
            }  
        }  

        static void DoWork()  
        {  

            lock (_object)  
            {  

                DataTable dt = new DataTable();  

                using (OracleConnection conn = new OracleConnection(connectionstring))  
                {  
                    conn.Open();  

                    using (OracleCommand cmd = new OracleCommand(query, conn))  
                    {  

                        using (OracleDataAdapter adap = new OracleDataAdapter(cmd))  
                        {  
                            adap.Fill(dt);  
                        }  

                        while (conn.State != ConnectionState.Closed) conn.Close();  
                    }  

                    conn.Dispose();  
                }  
            }  
        }   
    }

Answer 1

I had a similar scenario, with connections being opened and closed rapidly in a lightly threaded application, and got random timeouts when creating a connection.

In my case the default minimum connection pool size was 1, and the managed component seems to be slow in creating new ones. What I did, following this, was change the MinPoolSize property in my OracleConnectionStringBuilder to a number somewhat greater than the maximum number of concurrent calls I was expecting (15 MinPoolSize to 10 maximum concurrency), just to be on the safe side.

Seems to have worked a charm so far.