High Memory Usage with Oracle.ManagedDataAccess.Core

1.1k Views Asked by At

I need a little help here.

I'm facing a problem now with Oracle.ManagedDataAccess.Core.

I have a class written for centralize oracle queries (Clases.Oracle()), which works perfectly, but with one query the memory usage rises up to 1GB, which is not a real problem, considering that the resultset has about 260.000 rows in the worst scenario. The real problem is that it never frees that memory, and if I execute that query again, rises up to 2GB, been that the higher limit so far. Diagnostic Tools

I've tried adding GC.Collect() and GC.WaitForPendingFinalizers() with no results.

My command execution function in Clases.Oracle() is:

        private DataTable ExecuteReader(string package, ref OracleParameter[] parametros, string owner)
        {
            var dt = new DataTable();
            using (var cn = new OracleConnection(_connection_string))
            {
                using var cmd = cn.CreateCommand();
                try
                {
                    cn.Open();
                    cmd.CommandText = $"{owner}.{package}";
                    cmd.CommandType = CommandType.StoredProcedure;
                    foreach (var par in parametros)
                    {
                        cmd.Parameters.Add(par);
                    }
                    using var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    dt.Load(rdr);
                }
                catch (Exception ex)
                {
                   
                    throw new Exceptions.OracleException(ex.Message);
                }
                finally
                {
                    cn?.Close();
                    cmd?.Dispose();
                    cn?.Dispose();
                }
            }
            return dt;
        }

I'm using the using clause, so the objects are disposing.

And I'm calling the connection with this function:

        public List<AuditoriaUsuarios> ObtieneAuditoriaUsuarios(long incluyeCargoRol = 0)
        {
            var ora = new Clases.Oracle();
            var param = new OracleParameter[]
            {
                ora.AddInParameter("PIN_INCLUYECARGOROL",OracleDbType.Decimal, incluyeCargoRol),
                ora.AddOutCursor("CUR_OUT"),
                ora.AddOutParameter("PON_CODE", OracleDbType.Decimal),
                ora.AddOutParameter("POV_ERROR", OracleDbType.Varchar2)
            };
            var result = ora.ExecuteReader<AuditoriaUsuarios>($"{_PCK}.p_AUDIT_USUARIOS", ref param);
            if (ora.HayError(param))
            {
                throw new Exceptions.OracleException(ora.CodigoError, ora.MensajeError);
            }
            //GC.Collect();
            //GC.WaitForPendingFinalizers();
            return result;
        }

Clases.Oracle() doesn't need to be Disposable, because all the objects that I'm using are Disposable, and are being disposed, and 2 strings for the ConnectionString and the name of the Database Owner.

This is a Memory Usage dump from VS Memory Usage

You can see an oracle related object (OracleInternal.Common.ZoneValue) using a lot of memory way long after the ExecuteReader finished and the results where returned.

Don't know if I'm doing something wrong.

Edit:
I've forgot. This is a ASP .Net Core x64 WebAPI, using .NET Core 3.1 and C#, with Visual Studio 2019 Enterprise.

Edit2:
I know it's dirty, but adding this to ObtieneAuditoriaUsuarios made things a little better. (In this case I don't care about CPU usage, because this data extraction it's supposed to be executed a few times a week, and is not part of everyday operation):

GC.Collect(GC.MaxGeneration, GCCollectionMode.Forced);
GC.WaitForPendingFinalizers();
GC.Collect(GC.MaxGeneration, GCCollectionMode.Forced);

Edit3: I've sent 8 simultaneous requests and the memory usage rised up to 3GB in some tests. But only it takes 1 request with a filter that returns less than 100rows, and the memory usage drops to less than 1GB

Memory Usage Drop

0

There are 0 best solutions below