.NET Oracle managed data access connection pooling

2019-07-11 20:03发布

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

2条回答
三岁会撩人
2楼-- · 2019-07-11 20:04

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.

查看更多
做个烂人
3楼-- · 2019-07-11 20:09

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.

查看更多
登录 后发表回答