Connection Leak Detection on Applications Servers

Connection leak detection on a 3-tier application servers present a challenge as the connecting database user name would be same for all the connections. Therefore when a connection leak happens unless the usecase is known, or if it's a something recently started then leak is probably due to some new deployment it's diffcult to detect from the database site. One way is finding the number of session with the same sqlid for previous sql

select inst_id,machine,username, prev_sql_id,count(*) from gv$session where username='ASANGA' group by inst_id,machine,username, prev_sql_id order by 1,2,3,4

SQL id with the highest count is a suspect but may not always be the case.

Use of logon triggers would be of no use if a connection pool is used as when connections are handed out from the pool logon trigger doesn't fire.

To identify the exact point in the code where the leak happening is to populate the CLIENT_INFO column with line and class name before handing over the connection. One way this could be accomplished is by overrding the getConnection method.

Throwable throwable = new Throwable();
StackTraceElement[] stackTraceElements = throwable.getStackTrace();
StackTraceElement element = stackTraceElements[stackTraceElements.length > 0 ? 1 : 0];
CallableStatement clm = con.prepareCall("begin DBMS_APPLICATION_INFO.set_client_info(?); end;");
// String x = ste.getClassName() + ":" + ste.getMethodName() + ":" + ste.getLineNumber();
// client info column has a limit of varchar2(64),String is truncated to be 63 chars
StringBuilder y = new StringBuilder(ste.getClassName()).append(":").append(ste.getMethodName()).append(":").append(ste.getLineNumber());
clm.setString(1, (y.length() > 63 ? y.substring(0,63).toString(): y.toString()) );
clm.execute();
clm.close();

The above code snippet will populate the CLIENT_INFO column with the class name, method name and line number.Quering the v$session will show these values

select username,client_info from v$session where username is not null;

USERNAME CLIENT_INFO
-------- ---------------------------
ASANGA   com.Test : main : 86
ASANGA   com.Test : main : 79
ASANGA   com.Test : main : 79
ASANGA   com.Test : main : 79
ASANGA   com.Test : main : 85

Update 02 October 2012
There's another way to populate the v$session columns without making a database call. This is done by setting end to end metrics on the connection. Above code which makes a call using DBMS_APPLICATION_INFO could be changed to use end to end metric (this will populate client_identifier column instead of client_info column as above)

Throwable throwable = new Throwable();
StackTraceElement[] stackTraceElements = throwable.getStackTrace();
StackTraceElement element = stackTraceElements[stackTraceElements.length > 0 ? 1 : 0];
String metrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
// client_identifier has a limit of varchar2(64),String is truncated to be 63 chars
StringBuilder y = new StringBuilder(ste.getClassName()).append(":").append(ste.getMethodName()).append(":").append(ste.getLineNumber());
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = y.length() > 63 ? y.substring(0, 63).toString() : y.toString();//ste.getClassName() + " : " + ste.getMethodName() + " : " + ste.getLineNumber();
((OracleConnection) con).setEndToEndMetrics(metrics, (short) 0);

This way there's no need to make upfront db call to populate the columns in v$session. Beside end to end client id index other constants that could be used are

OracleConnection.END_TO_END_ACTION_INDEX :- populate action column
OracleConnection.END_TO_END_MODULE_INDEX :- populate module column

Columns are not populated as soon as the metrics are set on the connection, a sql query must be executed for the columns to get populated. This is explained in Bug 3735857 - V$SESSION.OSUSER not populated for JDBC clients [ID 3735857.8]

Update 05 October 2012
How to Set the value for the column "PROGRAM" of View V$SESSION from a Universal Connection Pool (UCP) [ID 1152523.1] explain somewhat similar procedure. Main difference is earlier information to populate the v$session was set at connection level whereas on this note using UCP it is done at connection pool level meaning all connections will have the same information.

Update 16 January 2013
Beside the method for setting class name, method name and line number there must be a mechanism to clear this information when the jdbc connection is closed. This information is not cleared when connection close method is called. Therefore it won't be possible to distinguish between connections that are opened but not closed vs closed connection. OracleConnectionWrapper could be used to wrap the close method and clear the text set in the getConnection. Following is an example where text "closed" is set on client_info column for closed connections.

@Override
    public void close() throws SQLException {

        CallableStatement clm = super.prepareCall("begin DBMS_APPLICATION_INFO.set_client_info(?); end;");
        clm.setString(1,"closed");
        clm.execute();
        clm.close();
        super.close();

    }

Clearing cannot be done using end to end metrics as they require an SQL to be executed for the columns to get populated. Also both PL/SQL (for clearing) and end to end metric (for setting) cannot be used together as they populate two different columns.

Useful metalink notes
Simple EndToEndMetrics Demonstration [ID 1264780.1]
Bug 3735857 - V$SESSION.OSUSER not populated for JDBC clients [ID 3735857.8]
No more data to read from socket" is Thrown When End-to-end Metrics is Used [ID 1081275.1]