I have a process that basically updates ~30,000 rows of a single Oracle table.
I do this concurrently in Sempahore restricted batches of 20 at a time. I currently have my OracleManagedDataAccess connection pool set to 20 min pool size and 200 max pool size. I monitor the database during the run and there are always 20 open connections it never goes greater than that. However, I'm still getting Connection Request timeouts and it's not predictable, sometimes I get time outs, sometimes I don't. I have my timeout currently set to 30 seconds. I can't figure out why I'm getting timeouts. Below is what I'm doing.
List<Task> tasks = new List<Task>();
List<Meters> meters = GetMeters();
int meterCount = meters.Count;
using (SemaphoreSlim ss = new SemaphoreSlim(20))
{
for (int i = 0; i < meterCount; i++)
{
ss.Wait();
string id = meters[i].ID;
tasks.Add(
Task.Run(() =>
{
try
{
Meter m = _ammService.GetStatusInfo(id);
if (!string.IsNullOrWhiteSpace(m.Status))
{
UpdateMeterStatus(m.ID, m.Status, m.StatusDate);
updatedCount++;
}
else
{
Program.appLogger.logWarning($"No Meter Status found for: {m.ID}");
}
}
finally
{
ss.Release();
}
})
);
}
Task.WaitAll(tasks.ToArray());
}//End using Sempaphore Slim
My UpdateMeterStatus and Database Access code are below
public int UpdateMeterStatus(string id, string status, DateTimeOffset statusTime)
{
OracleParameter[] oParams = new OracleParameter[]
{
new OracleParameter("id", id),
new OracleParameter("status", status),
new OracleParameter("statusTime", OracleDbType.TimeStampTZ, statusTime, ParameterDirection.Input)
};
return executeStoredProcedure(oParams, InterfacePackage + "Update_Meter_Status");
}
protected int executeStoredProcedure(OracleParameter[] oParams, string procName)
{
procName = schema + procName;
try
{
using (OracleConnection con = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(procName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.Parameters.AddRange(oParams);
cmd.Connection.Open();
return cmd.ExecuteNonQuery();
}
}
}
catch (OracleException)
{
throw;
}
}
Anyone able to help figure out why it's throwing Connection Request Timed Out errors? I'm using Oracle.ManagedDataAccess 4.121.1.0