7 Replies Latest reply on Sep 22, 2016 4:22 PM by Vadim Tropashko-Oracle

    autotrace is not getting cancelled when cancel button is pressed

    Andrei Kübar

      Hi

       

      I am trying to use autotrace in sqldeveloper Version 4.1.3.20  (basically i got the latest).

      I have enabled Thick client (using oracle 11.2 instant client)

       

      I have a problem cancelling long running queries if it's running through autotrace.

       

      Problem is only with autotrace. I hit the "cancel task" red 'X' button and sqldevelper reacts to that, reporting query as cancelled. But in the background, query is still running. I can see it in the v$session and also I am not able to run any other query in sqldeveloper, since it keeps waiting for autotrace to finish.

       

      I am able to cancel query which I execute NOT through autotrace.

       

      Is there any solution to this? Or is it planned to implement the cancel functionality for autotrace?

       

      thanks

        • 1. Re: autotrace is not getting cancelled when cancel button is pressed
          Gary Graham-Oracle

          Issues with production releases (and, yes, 4.1.3 is the most recent) are best reported and dealt with via MOS. The Autotrace cancellation may be an issue, but I only looked at the combined run times which may vary for many reasons. 

           

          For example, the following runs with Run Statement (Ctrl+Enter) in about 18 seconds normally, but in 33 seconds via Autotrace (F6).

          select w.object_type, w.status, count(*)

          from all_objects s, all_objects t, all_objects u, all_objects v, all_objects w

          where s.object_name = t.object_name and s.object_type = t.object_type

            and t.object_name = u.object_name and t.object_type = u.object_type

            and u.object_name = v.object_name and u.object_type = v.object_type

            and v.object_name = w.object_name and v.object_type = w.object_type

          group by w.object_type, w.status

          order by w.object_type, w.status;

          Reversing the steps, however, and adding a quick View > Task Progress cancellation on the Autotrace, the Run Statement takes 31 seconds.  Without more testing it is uncertain whether the statement being autotraced runs to completion in the background or if the cancel operation eventually does work.

          • 2. Re: autotrace is not getting cancelled when cancel button is pressed
            Vadim Tropashko-Oracle

            I challenge your assertion that you can reliably cancel the statement in SQL Worksheet (while failing to do so in autotrace).. For example, if you run (not autotrace) the following SQL

             

                 select sum(1) from all_objects, all_objects, all_objects;

             

            and cancel it then, indeed, the task is finished, but the connection is still busy. The culprit is a failure of JDBC statement.cancel() to execute as evidenced by the following test:

             

            Connection conn = DriverManager.getConnection(...);

            final PreparedStatement stmt = conn.prepareStatement("select sum(1) from all_objects, all_objects, all_objects");

            (new Thread(){

                @Override

                public void run() {

                    try {

                        Thread.sleep(10000);

                        stmt.cancel();

                        PreparedStatement stmt = conn.prepareStatement("select -1 from dual");

                        ResultSet rs = stmt.executeQuery();

                        rs.next();

                        System.out.println(rs.getObject(1));

                    } catch (Exception e) {

                    }

                }

            }).start();

            ResultSet rs = stmt.executeQuery();

            rs.next();

            System.out.println(rs.getObject(1));

             

            In 4.2 this issue is resolved via killing the session. The other 4.2 enhancement is that sqldeveloper gathers partial statistics every minute during long statement execution. Then, upon canceling the statement it retains partial statistics, unlike 4.1, where the canceled statement doesn't render even partial information.

            • 3. Re: autotrace is not getting cancelled when cancel button is pressed
              Gary Graham-Oracle

              Keep in mind the OP is using OCI/Thick, which works fairly well for cancelling SQL run via Run Statement in both 4.1.3 and 4.2 (even your SQL statement).

               

              Regarding Autotrace, it seems 4.2 does a much better job of realizing that Cancel does not stop the Autotrace.  It keeps the connection blocked, unlike in 4.1.3 where any additional SQL statements get queued up behind the still running Autotrace.

              • 4. Re: autotrace is not getting cancelled when cancel button is pressed
                Andrei Kübar

                Hi,

                 

                I can definitely cancel a long running query in sql worksheet and immediately run a new query after that. I just tested also with your query - it works. I had to enable the OCI/Thick driver for this to work.

                Whereas for the same query through Autotrace (pressing F6), cancel button doesn't stop the execution, I can see the session still being ACTIVE. And no further queries are returning anything. After killing the session with alter session kill, only then I can again use sqldeveloper.

                 

                regards

                Andrei

                • 5. Re: autotrace is not getting cancelled when cancel button is pressed
                  Andrei Kübar

                  Can I download 4.2? I don't see it anywhere.

                  • 6. Re: autotrace is not getting cancelled when cancel button is pressed
                    Gary Graham-Oracle

                    Typically a new release gets one or more EA (early adopter) downloads posted before the production release. Stay tuned for any mention of the 4.2 EA availability, especially since OOW is coming up soon in September.  

                    • 7. Re: autotrace is not getting cancelled when cancel button is pressed
                      Vadim Tropashko-Oracle

                      Here is a document that describes 4.2 autotrace amendments.