6

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();  
                }  
            }  
        }   
    }
Vincent
  • 61
  • 1
  • 1
  • 4
  • I believe we addressed some issues with this error in later versions, so you should go to Nuget and make sure you have the latest. (Sorry for commenting on such an old post, but I didn't notice the time stamp until it was too late) – Christian Shay Apr 12 '18 at 21:30
  • I am having the same problem today, just converted an app from Oracle.DataAccess to the latest ManagedDataAccess 2.18.3, getting intermittent timeouts as Vincent described. Any ideas? – gallivantor Dec 10 '18 at 08:52
  • same as @gallivantor here... anything?? please?? – D Ie Feb 19 '19 at 11:46

1 Answers1

2

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.

Platedslicer
  • 45
  • 1
  • 6