ODP.Net Connection request timed out in multithread

_E_T_ · March 2019

Hi,

Sometimes we encounter exceptions like this on our WebApi application with Oracle Poolmanager :

à OracleInternal.ConnectionPool.PoolManager3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, CriteriaCtx criteriaCtx, String affinityInstanceName, Boolean bForceMatch)

à OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, CriteriaCtx criteriaCtx, String affinityInstanceName, Boolean bForceMatch)

à OracleInternal.ConnectionPool.OracleConnectionDispenser3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, CriteriaCtx criteriaCtx)

à Oracle.ManagedDataAccess.Client.OracleConnection.Open()

This problem is reproducible using ODP.Net 2.2 in .net 4.5.1 and .net Core 2.2.

Here is sample code to reproduce it :

private static void ParallelConnection(int nbConnection, string connectionString) { var watch = Stopwatch.StartNew(); Console.WriteLine($"Testing {nbConnection} Connections"); var taskList = new List<Task<string>>(); for (int i = 0; i < nbConnection; i++) { taskList.Add(OpenConnectionSimple(i + 1, connectionString)); } Task.WaitAll(taskList.ToArray()); taskList.ForEach(t => Console.WriteLine(t.Result)); Console.WriteLine($"Total elapsed {watch.ElapsedMilliseconds}"); Console.ReadLine(); } private static async Task<string> OpenConnectionSimple(int index, string connectionString) { return await Task<string>.Factory.StartNew(() => { var error = string.Empty; var startTime = DateTime.Now; var watch = Stopwatch.StartNew(); var date = DateTime.MinValue; long timeCreated = -1; long timeOpen = -1; try { using (var cnx = new OracleConnection(connectionString)) { timeCreated = watch.ElapsedMilliseconds; cnx.Open(); timeOpen = watch.ElapsedMilliseconds; using (var cmd = cnx.CreateCommand()) { cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "SELECT SYSDATE FROM DUAL"; date = (DateTime)cmd.ExecuteScalar(); } } } catch (Exception ex) { error = ex.Message; } watch.Stop(); var state = (string.IsNullOrWhiteSpace(error)) ? "OK" : $"KO :{error}"; return $"[{index}]\t start: {startTime:HH:mm:ss.fff} duration:{watch.ElapsedMilliseconds}ms\t Ctor Connection:{timeCreated}ms \t open Connection:{timeOpen}ms \t SYSDATE Oracle: {date}\t State:{state}"; }); }

As you can see, calling ParallelConnection method with 20 connections make the 4 first threads in Timeout over default 15 seconds, and connection time of other threads are not reasonable !

The common workaround found on the web is to make connection timeout bigger, but in our case, response time of our web api is not reasonable.

Does someone can Explain why opening first connection in ODP.Net take this enormous time in multithread ?

Is there a workaround to avoid this connection timeout exception in Web Api application ?

Thanks in advance.

_E_T_ · April 2019

Hi,

Can someone reproduce this behaviour ?

is there a workaround to make my WebApi working under load ?

Thanks,

Alex Keh-Oracle · April 2019

It's possible you are encountering a connection storm scenario.

One way to minimize the effects of a connection storm is to make sure your pool has enough connections ahead of time ready to dispense. You do this by connecting to the DB ahead of the storm to prime the pool and set the Min Pool Size high enough so that ODP.NET can dispense enough existing connections from the pool rather than create new ones, which is much more expensive.

Another way to limit connection is to use the RATE_LIMIT parameter in the listener configuration so that the DB does not get overwhelmed with connection requests.

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Quick Links

Categories

ODP.Net Connection request timed out in multithread

Answers