Geek Answers Handbook

Oracle ODP.NET cursor leak?

I ran into the problem of restricting an open cursor using the following code. The open cursor limit on oracle db is set to about 1000. The following code seems to hold on cursors, even if I have everything in the using statement (I think) that requires it. (Note, I do not need to read anything from outRefCursor2)

Am I missing using or any other cleanup using ODP.net?
The exception occurs sequentially at iteration 596.

 static List<Thing> GetDetailsForItems(List<string> items) { DateTime start = DateTime.UtcNow; var things = new List<Thing>(); var spname = "SP_GET_THING_DETAILS"; var outRefCursorName1 = "p_ref_cursor1"; var outRefCursorName2 = "p_ref_cursor2"; // Create params var pInput1 = new OracleParameter("p_input1", OracleDbType.Varchar2, ParameterDirection.Input); pInput1.Value = ""; // Input 2 can be blank var pInput2 = new OracleParameter("p_input2", OracleDbType.Varchar2, ParameterDirection.Input); pInput2.Value = ""; var outRefCursor1 = new OracleParameter(outRefCursorName1, OracleDbType.RefCursor, ParameterDirection.Output); var outRefCursor2 = new OracleParameter(outRefCursorName2, OracleDbType.RefCursor, ParameterDirection.Output); int count = 0; using (var conn = new OracleConnection(CONN_STR)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.Parameters.Add(pInput1); cmd.Parameters.Add(pInput2); cmd.Parameters.Add(outRefCursor1); cmd.Parameters.Add(outRefCursor2); cmd.CommandText = spname; cmd.CommandType = CommandType.StoredProcedure; foreach (string value in items) { count++; cmd.Parameters[pInput1.ParameterName].Value = value; var execVal = cmd.ExecuteNonQuery(); using (var refCursor = (Types.OracleRefCursor) cmd.Parameters[outRefCursorName1].Value) { using (var reader = refCursor.GetDataReader()) { while (reader.Read()) { // read columns things.Add(reader["COLUMN_A"].ToString()); } } // close reader } // close cursor } // end foreach } // close command } // close connection int seconds = (DateTime.UtcNow - start).Seconds; Console.WriteLine("Finished in {0} seconds", seconds); return things; }

I use this snippet found on the Internet to monitor DB cursors. I can watch how cursors add up when I go through the code. And they just keep adding cmd.ExecuteNonQuery() lines. I never see a fall after closing any instruction.

 select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and machine='MY COMPUTER' group by s.username, s.machine order by 1 desc;
+4
source share
4 answers

None of the suggestions have worked so far. So in desperation, I finished building the GC GC every 200 iterations. With the following code.

 if (count % 200 == 0) { GC.Collect(); }

What is strange is that when calling this method from unit test, the GC.Collect() manual does not release any cursors. But when calling a method from the business layer, it really works, and I see that open cursors are freed up by controlling the oracle DB.

0
source

Even if you are not using outRefCursor2 , you still need to extract it and close it if it returns the correct cursor. ODP.net does not allocate resources as well as in the .Net version, so you need to make sure that you delete everything that is returned by ODP.net commands. As an extra step, you might want to get in the way of calling .Close() explicitly on cursors to make sure you actually close them (although dispose should take care of this).

+5
source

You need to get rid of the parameters:

  • yes, I confirmed this before showing my own resource leaks (virtual working set) when we didn’t

  • I prefer to manage the parameters during the life of the connection, to prevent any problems when reflex cursors need / want to use the connection to accommodate (superstition, perhaps)

    static List GetDetailsForItems (list items) {DateTime start = DateTime.UtcNow; var things = new List (); var spname = "SP_GET_THING_DETAILS"; var outRefCursorName1 = "p_ref_cursor1"; var outRefCursorName2 = "p_ref_cursor2";

     try { int count = 0; using (var conn = new OracleConnection(CONN_STR)) try { conn.Open(); // Create params var pInput1 = new OracleParameter("p_input1", OracleDbType.Varchar2, ParameterDirection.Input); pInput1.Value = ""; // Input 2 can be blank var pInput2 = new OracleParameter("p_input2", OracleDbType.Varchar2, ParameterDirection.Input); pInput2.Value = ""; var outRefCursor1 = new OracleParameter(outRefCursorName1, OracleDbType.RefCursor, ParameterDirection.Output); var outRefCursor2 = new OracleParameter(outRefCursorName2, OracleDbType.RefCursor, ParameterDirection.Output); using (var cmd = conn.CreateCommand()) { cmd.Parameters.Add(pInput1); cmd.Parameters.Add(pInput2); cmd.Parameters.Add(outRefCursor1); cmd.Parameters.Add(outRefCursor2); cmd.CommandText = spname; cmd.CommandType = CommandType.StoredProcedure; foreach (string value in items) { count++; cmd.Parameters[pInput1.ParameterName].Value = value; var execVal = cmd.ExecuteNonQuery(); using (var refCursor = (Types.OracleRefCursor) cmd.Parameters[outRefCursorName1].Value) { using (var reader = refCursor.GetDataReader()) { while (reader.Read()) { // read columns things.Add(reader["COLUMN_A"].ToString()); } } // close reader } // close cursor } // end foreach } // close command } // close connection finally { pInput1.Dispose(); pInput2.Dispose(); outRefCursorName1.Dispose(); outRefCursorName2.Dispose(); } } int seconds = (DateTime.UtcNow - start).Seconds; Console.WriteLine("Finished in {0} seconds", seconds); return things;

    }

+3
source

I would not go to GC.collect () ... This is a bust ... http://blogs.msdn.com/b/scottholden/archive/2004/12/28/339733.aspx

But make sure that recycling the command object worked for me. Easy to use

Something like that:

 using(DbCommand command = dbConn1.CreateCommand()) { command.CommandText = sql; using (var dataReader = command.ExecuteReader()) { dbRows = ToList(dataReader); } mvarLastSQLError = 0; }
+1
source

More articles:


All Articles