It’s been a while since the last time i’ve write tutorials in my blog, but i found a very interesting case that perhaps helped a lot of people. Today im trying to create a simple test case to simulate a connection leak and how to search for it.
First is creating a simple unclosed connection query using java,
package com.edw;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
System.out.println("starting --------");
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "USERNAME", "password");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM INVALID"); // example table name is INVALID
while(resultSet.next()) {
System.out.println(resultSet.getString(1));
System.out.println(resultSet.getString(2));
System.out.println(resultSet.getString(3));
System.out.println("=============");
}
System.out.println("ending --------");
while(true) {
Thread.sleep(10000);
System.out.println("testing --------");
}
}
}
It will create an unclosed query that will hanging on my oracle connection.
And next, is my oracle query to find the hanging queries,
select LAST_CALL_ET, SQL_TEXT, username, machine, to_char(logon_time, 'ddMon hh24:mi') as login,
SQL_HASH_VALUE, PREV_HASH_VALUE, status
from v$session, v$sql
where username='USERNAME' and HASH_VALUE = PREV_HASH_VALUE
order by last_call_et desc;
it will show result like this,
LAST_CALL_ET SQL_TEXT USERNAME MACHINE LOGIN SQL_HASH_VALUE PREV_HASH_VALUE STATUS
------------ ---------------------- ------------- -------------- -------------------- -------------- --------------- --------
91 SELECT * FROM INVALID USERNAME edw 03Mar 11:53 0 621168917 INACTIVE
As you can see the result on the first column, “SELECT * FROM INVALID” query has run for more than 90 seconds.
So i can easily find the java class culprit who run that query.
FYI, this is my Oracle version,
select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
I Hope this tutorial can help others, have fun
Google+