.NET Oracle manages data access connection not working or slow

Question

I recently noticed that when our application executes an SQL query against an Oracle database, it always takes at least 200 ms to execute. No matter how simple or complex the request, the minimum time is about 200 ms. We are using the Oracle Managed Data Access driver for Oracle 11g.

Then I created a simple console application to test the connection. I noticed that if I create a connection, as in the example below, then each cmd.ExecuteReader method takes an additional 200 ms (opening a connection)?

  using (OracleConnection con = new OracleConnection(connStr)) { con.Open(); OracleCommand cmd = con.CreateCommand(); ... } 

The state of the connection is always Closed when creating such a connection (should it not be open if the connections are joined?).

If I open the connection at the beginning of the program and then pass the open connection to the method, cmd.ExecuteReader will take about 0-5 ms to return. I tried adding Pooling=true to the connection string, but it does nothing (by default it should be the default).

Does this mean that the connection pool is not working as it should? Or maybe there is some other reason cmd.ExecuteReader takes an extra 200 ms to execute?

The problem is almost the same as in this problem, except that we use Oracle Connection Pool slower than saving one connection

Answer 1

After a lot of tests and research, I finally figured out where the extra 200 ms came from: my network adapter for a virtual computer. I am using VMWare Player and the connection has been configured to "NAT" mode. When I changed the connection to Bridge mode, the latency was removed.

Answer 2

Is your database remote and network latency? In this case, the connection pool works, but the problem is that there is always a TCP exchange connection (and even a TNS packet). Unfortunately, this happens with every Open call.

The implementation of controlled access to data interacts differently, so the overhead occurs only with the very first call to Open , then the Open method is free.