How do I forcefully close a connection from a connection pool when it's taking too much time to close?

Question

There are times closing connections takes a lot of time, like more than 10 minutes upto 1 hour, or worse, even for indefinite time, depending on how heavy or slow the query was.

In a situation where the client cancels the query because it has been taking too much time, I would want to free up the underlying connection used as soon as possible.

I tried cancelling the PreparedStatement, closing it, then closing the resultset, and then finally closing the connection. Cancelling took almost instantly. Closing the PreparedStatement and ResultSet took too much time that I had to wrap it in a Callable with timeout to skip that process in due time and proceed with closing the connection itself. I haven't got any much luck on what else to try out.

How do I deal with this? I can't simply let the connections unclosed and I can't let the users wait for 10 minutes before they can make another similar query.

Also, what's causing the closure of connection to take too much time? Is there anything else I could do? Do you think Oracle query hints would help?

I'm using Oracle JDBC via thin type of driver by the way.

UPDATE:

Apparently, it's possible to close the connection forcefully by configuring TimeToLive property in the connectionCacheProperties which closes the connection for a specific amount of time. However, what I need is on as-needed basis. This is worth mentioning because this proves that it is possible to forcefully close it as the Connection Pool just did. In fact, I even got the following message on my logs.

ORA-01013: user requested cancel..

Beet

I have never heard of a scenario like this were a connection takes time to close. – Qua

@OldProgrammer, try querying something complex enough that it takes too much time to even get the first few records from the result set. Let's not get into the performance aspect of this problem. I noticed, once the query gets the few rows, it becomes quick to close. I'm guessing the query has gone into a phase where it isn't closable (but not really, reaching TimeToLive can close it) before going to another phase where it starts sending out the records which is closable. – Beet

You can try to abort it from another Oracle SPID (let's call it your app admin connection), using the kill statement. – Selfopinionated

@access_granted, could you please translate that as an answer and use programmatical java solution using JDBC? I'll accept it if that works. – Beet

Answer 1

Main function:

  String g_sid = "";

Thread 1:

  String sql = ...;
  Connection conn = ...your connection func...;

  Statement stmt = conn.createStatement();
  ResultSet rset = stmt.executeQuery( "SELECT sid from v$mystat");
  if (rset.next()) g_sid = rset.getString("sid");
  rset.close();
  // now to the actual long-running SQL
  ResultSet rset = stmt.executeQuery( sql );
  // 
  stmt.close();

Thread 2:

  String serialN = "";
  Connection conn = ...your admin connection func...

  Statement stmt = conn.createStatement();
  ResultSet rset = stmt.executeQuery( "SELECT serial# serialN from v$session where sid=" + g_sid );
  if (rset.next()) {
    serialN = rset.getString("serialN"); 
    stmt.execute("alter system kill session '" + g_sid + "," + serialN + "'");
  }
  stmt.close();
  // probably keep the admin connection open for further maintenance
  //

Selfopinionated

There is no JSP here, this is pure Java. – Dead

so just share the variable "SID" between the threads in such case – Selfopinionated

Now this looks like it will require higher privilege access. I'll give it a try. I'll really prefer non-privileged way of doing this. – Beet

well your DBA would either need to allow you to alter system or will have to compile a small SYS procedure doing it implicitly. Once Oracle hatched upon the running process there's really either a system change or killing the Unix spid (owned by oracle uid nonetheless) would release it (and I just edited the stmt with quotes) – Selfopinionated

@access_granted, actually, both number should be enclosed together in a single quote pair. I knew I'd get "SQL Error: ORA-01031: insufficient privileges". I guess I have to modify my question. – Beet

@access_granted, if ever the DBA would actually create that, i'm thinking how would he create some checks that it won't be called against sessions I don't own? It might be tough to implement. – Beet

I just figured out that we actually have firm-wide policy not to create such stored procedure and commands that kill sessions will have to go through a special process. Still+1 for providing an alternative solution though not the ideal one. – Beet

Answer 2

This is what we use at my POW instead of "grant alter system to $UID" as a SYS-owned procedure (simplified working version):

CREATE OR REPLACE procedure SYS.kill_session(in_sid varchar2)
as
  l_serial number;
  l_runsql varchar2(1000) := 'alter system kill session ''$1,$2'' immediate';
begin
  begin
  select serial# into l_serial from v$session where username =
  (
    SELECT USER FROM DUAL
  ) and sid=in_sid and rownum<=1;
  exception when no_data_found then
    raise_application_error( -20001, 'Kill candidate not found');
  end;
  l_runsql := replace( l_runsql, '$1', in_sid);
  l_runsql := replace( l_runsql, '$2', l_serial);
  execute immediate l_runsql;
end;
/

This way you can only kill your own sessions.

Selfopinionated

I just figured out that we actually have firm-wide policy not to create such stored procedure and commands that kill sessions will have to go through a special process. Still+1 for providing an alternative solution though not the ideal and workable solution in my current situation. – Beet