11 Replies Latest reply on May 24, 2013 2:42 AM by thatJeffSmith-Oracle

    How to cancel long query?

      Hello I have one quick question today.
      Assume I have some quere and the execution takes long, so I want manually to cancel it. How?
      What I did was to open View -> Task Progress and clicked the Cancel Task button. Unfortunatelly this doesn't cancel the query, it is still running on the background, so I can't do any DB operation. I even can't logoff and quit SQL Developer...
        • 1. Re: How to cancel long query?
          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.
          • 2. Re: How to cancel long query?
            Gary Graham-Oracle

            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:
            3.1EA1 Can not Cancel PLSQL Block

            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.

            SQL Developer Team

            Edited by: Gary Graham on Mar 26, 2012 6:42 PM
            Fixed some typos.
            • 3. Re: How to cancel long query?
              Hello Gary,
              unfortunately this is not my case. jdbc.library variable is set like this:

              jdbc.library     /C:/Programy/SQL Developer/sqldeveloper64-
              • 4. Re: How to cancel long query?
                Gary Graham-Oracle

                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:
                Re: SQL Developer: Not able to view tables

                2. A similar bug has been logged for SQL Developer which is in turn waiting on a fix from the JDBC group.

                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 or SQL Developer ships with the 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?

                • 5. Re: How to cancel long query?
                  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:


                  oracle.exe 9311 Console 0 5,072 K

                  c:>taskkill /pid 9311

                  SUCCESS: The process with PID 9311 has been terminated.
                  • 6. Re: How to cancel long query?

                    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...

                    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
                    • 7. Re: How to cancel long query?
                      Don Kleppinger
                      I have to use that setting when running on windows

                      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.
                      • 8. Re: How to cancel long query?
                        Gary Graham-Oracle
                        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,
                        this low level network bug is instead causing an error to occur that puts the connection into an unusable state
                        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,
                        • 9. Re: How to cancel long query?
                          Don Kleppinger
                          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
                          and this
                          • 10. Re: How to cancel long query?
                            Gary Graham-Oracle
                            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.


                            Edited by: Gary Graham on May 23, 2013 6:06 PM
                            Fixed spelling errors (bounds -> band). Juggling too many things today!
                            • 11. Re: How to cancel long query?
                              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.