Connection pool growing to 4 even though only one connection is in use after upgrade

[madelson]

Unclear if this is a bug or "by design", but I noticed this odd behavior after upgrading to Oracle.ManagedDataAccess.Core 23.6.1. Basically, I have some code that repeatedly opens and closes the same OracleConnection instance. Under the hood, I would expect this to use a single pooled connection. However, with the newer version it seems to work itself up to 4 connections!

Here is a bit of code which reproduces the issue reliably:

static void Main()
{
	const string ApplicationName = "cKtYtSAs6h4kp6cyLLFyeIB7R5ezQsFuGddsHAt7mtnbg1MvS1hCU4UKnQf0Owxt";
	using OracleConnection namedConnection = new(ConnectionString);
	namedConnection.ConnectionOpen += c => c.Connection.ClientInfo = ApplicationName;
	
	while (true)
	{
		namedConnection.Open();
		
		using (var command = namedConnection.CreateCommand())
		{
			command.CommandText = "SELECT COUNT(*) FROM v$session";
			command.ExecuteScalar();
		}
		
		// On 3.21.160, this prints 1 every time.
		// On 23.6.1, this prints 1, 1, 2, 3, 4, 4, 4, ...
		Console.WriteLine(CountActiveSessions(ApplicationName));
		
		namedConnection.Close();
		
		Thread.Sleep(1000);
	}
}

static int CountActiveSessions(string applicationName)
{
	using var connection = new OracleConnection(ConnectionString);
	connection.Open();
	using var command = connection.CreateCommand();
	command.CommandText = "SELECT COUNT(*) FROM v$session WHERE client_info = :applicationName AND status != 'KILLED'";
	command.Parameters.Add("applicationName", applicationName);
	return (int)(decimal)command.ExecuteScalar();
}

[alexkeh]

This looks like a bug (37367802). The ODP.NET team will review.

[alexkeh]

The ODP.NET dev team analyzed the test case and can explain the behavior change.

In ODP.NET 23ai, the provider resets the CLIENT_INFO only when it can piggyback on a DB round trip on the SAME connection or 15 seconds, whichever comes first. In ODP.NET 21c, the provider resets the CLIENT_INFO with a separate round trip. The 23ai implementation performs better as it reduces the number of unique round trips. However, you won't get the most updated CLIENT_INFO aggregated stats.

When they looked at actual DB connections, there were 7 in both ODP.NET 23 and 21 cases. CountActiveSessions used one connection and namedConnection used a second. As the connections were toggled through, the closed connections would be placed back in the pool, but not have their CLIENT_INFO reset until they were dispensed back and made a round trip. That is why you see a consistent 4 sessions with the CLIENT_INFO. 3 connections were in the pool waiting for another RT (or 15 seconds) to reset CLIENT_INF. The fourth one was being used by an instance of namedConnection which sets the CLIENT_INFO again.

[madelson]

Thanks for the detailed explanation and investigation!