NickName:Ewerton Ask DateTime:2012-04-12T23:20:16

To close or not to close an Oracle Connection?

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?

Copyright Notice：Content Author:「Ewerton」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/10126623/to-close-or-not-to-close-an-oracle-connection

Answers

Christian Shay 2012-04-14T20:02:06

Here is a slide deck containing Oracle's recommended best practices:\n\nhttp://www.oracle.com/technetwork/topics/dotnet/ow2011-bp-performance-deploy-dotnet-518050.pdf\n\nYou 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.\n\nPlease see the ODP.NET online help for details on connection pooling. Pool settings are added to the connection string.\n\nAnother 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.\n\nThis particular issue will be mitigated in Oracle's fully managed provider (a beta will be out shortly)\n\n(EDIT: ODP.NET, Managed Driver is now available.)\n\nChristian Shay\n\nOracle",

Cezar Schwartz 2012-04-12T16:58:13

The ODP.NET is a data provider for ADO.NET.\nThe best practice for ADO.Net is Open, Get Data (to memory), close, use in memory data.\nFor example using a OracleDataReader to load data in a DataTable in memory and close connection.\n\n[]'s",