SQL Developer

I'd like to also pose the same question.

As I understand it there is a call in JDBC for this, but it is very much dependent on compatibility between the JDBC driver and the DB versions.

Can anybody shed any light on the matter?

I end up killing SQLDev and starting a new process; not very nice.

Hi,

This is most probably due to a known JDBC driver conflict that may occur when an ORACLE_HOME environment variable is set. For the long discussion (and workaround), see the following:
2302870

But basically this is considered an installation issue related to other products, so the bug number referenced in the thread above has been closed as infeasible to fix relative to the SQL Developer product. To see if it does apply to your environment, open Help|About and look for the value of the jdbc.library variable in the Properties tab. If it ends with ojdbc5.jar you should use the workaround.

Regards,
Gary
SQL Developer Team

Edited by: Gary Graham on Mar 26, 2012 6:42 PM
Fixed some typos.

Hello Gary,
unfortunately this is not my case. jdbc.library variable is set like this:

jdbc.library /C:/Programy/SQL Developer/sqldeveloper64-3.1.07.42-no-jre/jdbc/lib/ojdbc6.jar

Hi,

In that case there are a couple more possibilities since your environment points to ojdbc6.jar as intended.

1. Are you using -Doracle.net.disableOob=true ? See why you might use it, and the negative side-effects here:
9716932

2. A similar bug has been logged for SQL Developer which is in turn waiting on a fix from the JDBC group.
Bug 12378063 - CANT DISCONNECT CONNECTION OR QUIT SQLDEV AFTER CANCELING QUERY EXECUTION

With respect to (2), the cancel does not really cancel the query execution. The JDBC group's response: it's a known Windows platform issue and not too much can be done about it. I, however, have never been able to replicate it on Windows, with the ojdbc6.jar from either 11.2.0.1.0 or 11.2.0.2.0. SQL Developer ships with the 11.2.0.2.0 version and it gets used by default if no ORACLE_HOME variable is set.

Is there any specific characteristic of your environment you can think of that might make it more susceptible to this behavior? Or specific cases when it always or never occurs?

Thanks,
Gary

As a quick review, you can kill an Oracle session which is running long query from within Oracle, but that does not always terminate the OS process. First, you get the SID and serial number of the session that you want to kill:

select spid, osuser, s.program from v$process p, v$session s where p.addr=s.paddr;

Next, you kill the session from insude SQL*Plus: alter system mill session 'mysid, myserial_no';

You can also invoke the dbms_sql.kill_session procedure to kill a Windows session from inside Oracle.

Killing a Windows Task

In Windows we have several utilities, the Oracle-centric "orakill" utility and the Windows "taskill" program. The Windows command to kill this session would be as follows.

C:\oracle9i\bin>orakill ORCL92 768

In this example, the windows thread corresponding to the Oracle session can be killed in the operating system without ever logging into the database.

You can also use the Windows taskkill utility to remove an Oracle Windows process:

c:>tasklist

oracle.exe 9311 Console 0 5,072 K

c:>taskkill /pid 9311

SUCCESS: The process with PID 9311 has been terminated.

Hello,

Gary:
I don't think I have anything special. I will keep my eye on it and let you know if I find out some rule when it always happens...

user12838363:
Thanks for advice, but I really don't want to do this :)
When a query takes long to finish, I simply want to cancel it from Developer and not to kill the session and win process

I have to use that setting when running on windows
-Doracle.net.disableOob=true

It allows me to cancel a running query and execute a different one.
I'm not sure about the other thread that said "doing so will affect the ability to cancel an executing statement"
I found the opposite to be true. I can execute another query. I don't know for sure if the database stops working on the old query but it appears to as I can execute a different query afterwards.

Running on Linux connected to the same database doesn't exhibit the same problem and I don't need that setting.
I blogged about this here http://dkleppinger.blogspot.com/2012/11/how-to-cancel-long-running-query-from.html
We're running on Exadata platform.

Hi Don,

I'm not sure about the other thread that said "doing so will affect the ability to cancel an executing statement"
I found the opposite to be true.

Let me clarify a bit...

1. Before Out of Band breaks became available (and the default), the server had to check for In Band breaks from the client. According to documentation, checking via frequent polling increased overhead. Using Out of Band breaks allows asynchronous checking with reduced overhead. And possibly it may allow cancellation to occur in some cases (I am not certain) where server code might not yield or check frequently enough. But whether on or off, cancellation can still generally occur.

2. In the case of the Out of Band breaks bug mentioned in the other thread, the client perceives this as a Closed Connection issue. Possibly (again, I am not certain), the statement continues to run on the server.

So in light of these two points, I suppose that's how one might interpret that affect the ability to cancel comment. A cancellation bug specifically affecting SQL Developer 3.1, unrelated to OOB, was caused by the use of a new API available in ojdbc6 when we mistakenly picked up ojdbc5 (the 11g client ships with both ojdbc5 and ojdbc6 jar files). That got fixed in the 3.2 release.

Thanks for your comments,
Gary

Thanks for the reply.

I'm not a DBA so don't understand where this gets set. I'm curious to know if this setting is set in the database or if this a connection parameter passed by the database driver. If it works when I add the java setting to disable oob breaks does that mean the database is always polling to check for in bound breaks and thus reducing performance? Since I don't need the parameter when running on linux, that tells me that out of band breaks are working from the database side but is the database also looking for in bound breaks and I should somehow turn that checking off to increase performance? Is my assumption correct that the database stops working on the query when I break since I can immediately execute another query on the same session?

The only docs I have found are this in Oracle 7
http://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/network.htm#1559
and this
http://docs.oracle.com/cd/B28359_01/network.111/b28317/sqlnet.htm#BIIGDBDF

The links you provide are good. It is a connection property/parameter, so...
1. For SQL*Net, use DISABLE_OOB=on in the sqlnet.ora file.
2. For Java JDBC, use the -D... parameter syntax from the command line console or AddVMOption -D... in the sqldeveloper.conf file.

Disabling OOB will introduce extra overhead in managing the connection, but in band / out of band breaks are mutually exclusive, so only worry about the extra overhead that goes with in band if OOB is disabled. I guess the connection property to disable OOB was first introduced in response to a JDBC bug involving code that did not handle asynchronous processing very well.

Keep in mind that a single connection in SQL Developer is shared by default, but processing is serialized. If you need multiple connections to the same database user/schema, you can define multiple connection names with the same details or click on the unshared connection icon in the worksheet's toolbar. If you want to see if a cancel actually succeeded on the server-side, you can use Tools -> Monitor Sessions, connect with a user having sufficient privileges, and see everything that is currently running.

Cheers,
Gary

Edited by: Gary Graham on May 23, 2013 6:06 PM
Fixed spelling errors (bounds -> band). Juggling too many things today!

Not to muddy the conversation further, but using OCI/thick for your connection will give you better cancellation support from the database side as well.