Why OracleParameter is the only one that implemets IDisposable
WalterDias opened this issue · 5 comments
Moving my app from SQL to Oracle I notice that OracleParameter in Oracle.ManagedDataAccess.Client for some "peculiar" reason implements IDisposable, SqlParameter (Sql Server), NpgsqlParameter (Postgress), MySqlParameter (MySql), MySqlParameter (MariaDB), SqliteParameter (Sqlite).
What is the reason for that, and why on the exemples provided by Oracle do not dispose the parameters, example below.
The latest version provide at https://www.nuget.org/packages/Oracle.ManagedDataAccess.Core OracleParameter still implements IDisposable.
Thx ofr the help and clarification.
Some ODP.NET data types use resources on the DB server side, such as LOBs and UDTs. For example, when you select and retrieve a large LOB, you don't have to retrieve the entire LOB immediately. You can read one section of the LOB at a time and retrieve more from as the end user needs it. Or you can select and search for some text within the CLOB and retrieve just a few MB around that piece of text. That saves your app from retrieving the rest of the CLOB text. This is helpful when your LOBs are very large and/or numerous in the select list.
Because these ODP.NET parameters hold onto server resources, they require the OracleConnection, OracleCommand, and any other associated .NET object to persist until the parameter is disposed or the GC cleans it up.
Other DBs may not have this type of functionality in their complex data types, which means they don't need an OracleParameter Dispose.
I also had this question and appreciate the clarification. I have not found this documented anywhere, does it exist in the current documentation?
Does this imply that every OracleParameter should be explicitly disposed, regardless of its underlying data type? Or is this something that is otherwise cleaned up for us? Many of the samples in this repository do not dispose of parameters but they are also not long-lived applications.
In my specific use-case, I am chasing a memory leak when using EF Core and came across this class implementing IDisposable, which I was not aware of. I don't believe my memory leak is due to this, but still something I am concerned about.
@sccunningham7 It's a good practice to Close/Dispose all objects you create, not just ODP.NET ones. Some objects are more "expensive" than others. Connections fit that category, but OracleParameter can be as well since they can hold onto LOBs, UDTs, and many other non-scalar types. These non-scalar types generally defer fetching the entire data set until it needs to be read. To support that capability, they hold onto the connection object and retrieve the next set of data when the app tries to read it. Thus, if you don't dispose of the OracleParameter for one of the non-scalar types, ODP.NET won't be able to dispose of the connection object either. The .NET GC will have to do it.
The .NET GC will clean up objects not explicitly closed/disposed under non-stress conditions. In stress conditions, the GC will not be able to clean up unused objects as quickly. They will accumulate and appear to be a memory leak.
We document this as a general best practice in the ODP.NET book.
Thank you for the explanation. I will default to always disposing parameters regardless of their type.
I will again note that many of the samples in this repository do not dispose of the parameter object (or use a using statement), which is why I questioned the need for them to be disposed in the first place. I assume this is because you are only explicitly disposing when the type is non-scalar, but this was confusing because if the class implements IDisposable, I would expect the caller to have to properly dispose of it.
The linked best practices do not include OracleParameter, only OracleConnection and OracleCommand.
The newer samples do a more comprehensive job of closing and disposing. The intent of the samples is to focus on demonstrating specific ODP.NET functionality. We try to incorporate good coding practices as well but don't all the time, especially if it would make running or understanding the code more complicated.
The link says all ODP.NET objects should be closed or disposed. OracleConnection and OracleCommand are mentioned specifically, but they are pointed out as examples, not to represent a comprehensive list.