Memory leak

[woehling]

Hi,

I found a memory when I use a quite special SQL statement multiple times.
I tried this out with some older version and the latest beta version (5.55) of the dotConnect for Oracle provider.
Here's the code that causes the problem:
(Sorry, I found no shorter version that shows the memory leak)

private static void TestDevart()
{
var conn = new Devart.Data.Oracle.OracleConnection("Data Source=;User Id=;Password=");
conn.Open();
Devart.Data.Oracle.OracleCommand cmd;
try
{
cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE MY_MAIN";
cmd.ExecuteNonQuery();
}
catch { }

try
{
cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE MY_FOREIGN";
cmd.ExecuteNonQuery();
}
catch { }

cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MY_MAIN (ID varchar2(36), IDPARENT varchar2(36))";
cmd.ExecuteNonQuery();

cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MY_FOREIGN (ID varchar2(36), IDREF varchar2(36))";
cmd.ExecuteNonQuery();

cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT :PVALUE1 AS F1, :PVALUE1 AS F2, :PVALUE1 AS F3,
NULL AS F4, NULL AS F5, NULL AS F6
FROM ( SELECT * FROM MY_MAIN
WHERE IDPARENT = :PARENTID OR (IDPARENT IS NULL AND :PARENTID IS NULL)
)
UNION ALL
SELECT NULL, NULL, NULL, :PVALUE2, :PVALUE2, :PVALUE2
FROM ( SELECT * FROM
(
( SELECT ID FROM MY_MAIN
WHERE IDPARENT = :PARENTID OR (IDPARENT IS NULL AND :PARENTID IS NULL)
)
T0 INNER JOIN MY_FOREIGN T1 ON T0.ID = T1.IDREF
)
)";
var paramParent = cmd.Parameters.Add("PARENTID", Devart.Data.Oracle.OracleDbType.VarChar, 36);
cmd.Parameters.Add("PVALUE1", DBNull.Value);
cmd.Parameters.Add("PVALUE2", DBNull.Value);

string[] idList = {
Guid.NewGuid().ToString(),
Guid.NewGuid().ToString(),
Guid.NewGuid().ToString(),
Guid.NewGuid().ToString(),
Guid.NewGuid().ToString(),
Guid.NewGuid().ToString(),
Guid.NewGuid().ToString(),
Guid.NewGuid().ToString(),
Guid.NewGuid().ToString()
};

foreach (var id in idList)
{
var cmdInsert = conn.CreateCommand();
cmdInsert.CommandText = "INSERT INTO MY_MAIN (ID, IDPARENT) VALUES ('" + id + "','" + id + "')";
cmdInsert.ExecuteNonQuery();

for (int i = 0; i < 5000; i++)
{
cmdInsert = conn.CreateCommand();
cmdInsert.CommandText = "INSERT INTO MY_FOREIGN (ID, IDREF) VALUES ('" + id + "','" + id + "')";
cmdInsert.ExecuteNonQuery();
}
}

for (int i = 0; i < 100; i++)
{
foreach (var id in idList)
{
paramParent.Value = id;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
}
}
}
}
conn.Close();
}

I hope you can reproduce and fix this

Regards,
Markus

[StanislavK]

We couldn't reproduce a memory leak.

OracleDataReader increases its reading buffer, going up to 30 Mb when reading 5000 records. You may limit this buffer by setting up the cmd.FetchSize property, which specifies the maximal number of records retrieved at once. For example, setting cmd.FetchSize = 50, the buffer takes about 1.2 Mb of memory. Of course, such limitation may affect the execution time.

[woehling]

Hi,

my source problem is not that I'm running out of (overall) memory. It seems like the provider requests too much memory at once (maybe 500MB or something like that), then an OutOfMemoryException is thrown. The sample tries to isolate the core problem, I didn't manage to create a small sample that really causes an OutOfMemoryException.

If you modify my sample so that you replace the last :PVALUE2 parameter with NULL, then you'll probably see what my problem is.

When I'm using this SQL fragment:
...
SELECT NULL, NULL, NULL, :PVALUE2, :PVALUE2, :PVALUE2
...
memory consumpting is about 185MB (after all objects are disposed).

When I'm using this SQL fragment:
...
SELECT NULL, NULL, NULL, :PVALUE2, :PVALUE2, NULL
...
memory consumpting is about 100MB (after all objects are disposed).

Where are the 85MB gone?

Regards,
Markus

[StanislavK]

We reproduced the problem. We will investigate it and inform you about the results.

As a workaround, you may connect in the Direct mode, if its limitations are not critical for your tasks:
http://www.devart.com/dotconnect/oracle ... tMode.html

[StanislavK]

The memory leak occurs in OCI rather than in Devart code, and we are unable to fix this problem. Please address to the Oracle support.

Also, you may use the Direct mode, the problem should not occur with it.