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.