The best way to detect Oracle connection leak?

Question

A program written in C# Oracle client that proved to have "Connection leak" which it is not closing all database connections and so after some time it can no longer connect to the database as there are too many open connections.

I wrote the following helper function (quite expansive):

        private static int tryFindConnCount(){
            var connstk = new Stack<Oracle.ManagedDataAccess.Client.OracleConnection>();
            try
            {
                for (var i = 0; i < 10000; ++i)
                {
                    var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(
                        myDatabaseConnection);
                    conn.Open();
                    connstk.Push(conn);
                }                
            }
            catch(Exception e)
            {
                foreach (var conn in connstk)
                {
                    conn.Close();
                }

            }
            return connstk.Count;
        }

Here is the code in a test case that uses the above:

            var co = tryFindConnCount();
            CodeThatMayLeakConnection();
            var cn = tryFindConnCount();

            Assert.That(cn, Is.EqaulTo(co));

It helped me identify at least one case that have connection leak.

The problem of tryFindConnCount is that it should never be used in production. And I think there should be some way to obtain the same value much cheaper.

How can I do this in the code so I can monitor this value in production?

Answer 1

Trying to find places where connections where not closed is a difficult task.

If you leave the program and forget to close the connection the last sql which was executed is stored in column SQL_ID in v$session (gv$session for RAC). You can search v$session for idle/dead sessions. You can then use v$sql to find the SQL text which may tell you more about what was done last. By this you may get a hint where to search in your code.

select a.sid, a.username, a.program, a.machine, a.sql_id, b.sql_fulltext
  from v$session a, v$sql b
 where b.sql_id(+) = a.sql_id
   and a.username is not null  -- filter system processes, maybe filter more stuff
;