To close or not to close an Oracle Connection?

Question

My application have performance issues, so i started to investigate this from the root: "The connection with the database".

The best practices says: "Open a connection, use it and close is as soon as possible", but i dont know the overhead that this causes, so the question is:

1 -"Open, Use, Close connections as soon as possible is the best aproach using ODP.NET?"

2 - Is there a way and how to use connection pooling with ODP.NET? I thinking about to create a List to store some connections strings and create a logic to choose the "best" connection every time i need. Is this the best way to do it?

Answer 1

Here is a slide deck containing Oracle's recommended best practices:

http://www.oracle.com/technetwork/topics/dotnet/ow2011-bp-performance-deploy-dotnet-518050.pdf

You automatically get a connection pool when you create an OracleConnection. For most middle tier applications you will want to take advantage of that. You will also want to tune your pool for a realistic workload by turning on Performance Counters in the registry.

Please see the ODP.NET online help for details on connection pooling. Pool settings are added to the connection string.

Another issue people run into a lot with OracleConnections is that the garbage collector does not realize how truly resource intensive they are and does not clean them up promptly. This is compounded by the fact that ODP.NET is not fully managed and so some resources are hidden from the garbage collector. Hence the best practice is to Close() AND Dispose() all Oracle ODP.NET objects (including OracleConnection) to force them to be cleaned up.

This particular issue will be mitigated in Oracle's fully managed provider (a beta will be out shortly)

(EDIT: ODP.NET, Managed Driver is now available.)

Christian Shay

Oracle

Answer 2

The ODP.NET is a data provider for ADO.NET. The best practice for ADO.Net is Open, Get Data (to memory), close, use in memory data. For example using a OracleDataReader to load data in a DataTable in memory and close connection.

[]'s