.NET Oracle managed data access connection pooling

问题:

I recently noticed that when our application does an SQL query to the Oracle database, it always takes at least 200 ms to execute. It doesn't matter how simple or complex the query is, the minimum time is about 200 ms. We're using the Oracle Managed Data Access driver for Oracle 11g.

I then created a simple console application to test the connection. I noticed that if I create the connection like in the example below, then every cmd.ExecuteReader method takes the extra 200 ms (opening the connection)?

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

The connection state is always Closed when creating the connection like this (shouldn't it be open if the connections are pooled?).

If I open the connection at the start of the program and then pass the opened connection to the method, the cmd.ExecuteReader takes about 0-5 ms to return. I've tried to add Pooling=true to the connection string but it doesn't seem to do anything (it should be the default anyway).

Does this mean that the connection pooling is not working as it should? Or could there be any other reason why the cmd.ExecuteReader takes the extra 200 ms to execute?

The problem is almost the same as in this issue, except that we're using Oracle Connection pooling is slower than keeping one connection open

回答1:

Is your database remote and the delay caused by network? In this case connection pooling works but the problem is that there is always a TCP communication roundtrip (and not even TNS packet). Unfortunately this happens with every Open call.

Managed data access implementation communicates in different way so the overhead takes place only at the very first Open call, then the Open method is free.

回答2:

After a lot of testing and research I finally figured out where the extra 200ms comes from: my virtual computer's network adapter. I'm using VMWare Player and the connection was configured to "NAT" mode. When I changed the connection to "Bridged" mode the latency was removed.