8 Replies Latest reply on Jul 2, 2015 7:10 PM by Gary Graham-Oracle

    SQL*Plus "timing" command in SQL Developer

    1502746

      I installed SQL Developer at  version 4.1.1.19. In the sql worksheet, how can I use SQL*plus "timing" command in a script to see a SQL elapsed time? The "set timing on" does not return any info after SQL run, and the "timing start my_timer" returned "Timing command is obsolete." Please help.

       

      Thank you,

        • 1. Re: SQL*Plus "timing" command in SQL Developer
          Galo Balda

          Are you running it as a script (F5)?

          • 3. Re: SQL*Plus "timing" command in SQL Developer
            Galo Balda

            I have the same SQL Developer version and set timing works fine. Maybe you can post a screen capture showing what you're trying to do.

            • 4. Re: SQL*Plus "timing" command in SQL Developer
              1502746

              This is my script:

               

                     SPOOL 3-t.txt

                       SET TIMING ON

              EXEC pmsmmi.rpt.rpt_ethncty_race_smry (15);

                     SPOOL OFF

               

              And its returns in 3-t.txt:

               

              SQL> SET TIMING ON

              SQL> EXEC pmsmmi.rpt.rpt_ethncty_race_smry (15)

              PL/SQL procedure successfully completed.

              SQL> SPOOL OFF

              • 5. Re: SQL*Plus "timing" command in SQL Developer
                1502746
                • As you can see, there is no timing info in the script output log.

                 

                • I then searched the discussion forum, found it can be enabled in "Tool> Preference> Database > Start up script" and set the startup script as:

                   set serveroutput on 

                   set timing on

                 

                • However, 2 issues found:
                1. the timing it returned is NOT the elapsed time of my stored procedure run. It just pick any 2 points of time and its elapsed time, not the point Before and After SP run. Therefore, set timing on is not a solution to me.
                2. it did not follow the instructions in startup script. After I set timing off in startup script, shutdown & restart SQL Developer, even reboot laptop, timing still on. The only way to turn it off now is by configure NOT to use

                       any startup script.

                 

                • I then developed my own solution by coding as:

                 

                       SPOOL 3-t.txt

                         VAR bv_start_ts VARCHAR2 (30)

                         VAR bv_end_ts   VARCHAR2 (30)

                         VAR bv_elps_ts  VARCHAR2 (30)

                        

                       DECLARE

                          v_start_ts VARCHAR2 (30);

                       BEGIN

                          SELECT TO_CHAR (SYSTIMESTAMP, 'yyyymmddfxDyhh24miss.ff6') INTO v_start_ts FROM DUAL;

                          :bv_start_ts := v_start_ts;

                       END;

                       /

                       PRINT bv_start_ts

                EXEC pmsmmi.rpt.rpt_ethncty_race_smry (15);

                       DECLARE 

                          v_end_ts   VARCHAR2 (30);

                       BEGIN

                          SELECT TO_CHAR (SYSTIMESTAMP, 'yyyymmddfxDyhh24miss.ff6') INTO v_end_ts FROM DUAL;

                          :bv_end_ts := v_end_ts;

                       END;

                       /

                       PRINT bv_end_ts

                       EXEC :bv_elps_ts := TO_CHAR (TO_TIMESTAMP (:bv_end_ts, 'yyyymmddfxDyhh24miss.ff6') - TO_TIMESTAMP (:bv_start_ts, 'yyyymmddfxDyhh24miss.ff6'), 'hh:mi:ss.ff6');

                       PRINT bv_elps_ts

                      

                       SPOOL OFF

                 

                • Then I got what I want, the answer is in the "PRINT bv_elps_ts":

                SQL> VAR bv_start_ts VARCHAR2 (30)

                SQL> VAR bv_end_ts   VARCHAR2 (30)

                SQL> VAR bv_elps_ts  VARCHAR2 (30)

                SQL> DECLARE

                          v_start_ts VARCHAR2 (30);

                       BEGIN

                          SELECT TO_CHAR (SYSTIMESTAMP, 'yyyymmddfxDyhh24miss.ff6') INTO v_start_ts FROM DUAL;

                          :bv_start_ts := v_start_ts;

                       END;

                      

                /

                 

                 

                PL/SQL procedure successfully completed.

                 

                 

                Elapsed: 00:00:00.031

                SQL> PRINT bv_start_ts

                 

                 

                BV_START_TS

                -----------

                20150702Thu112617.775545

                SQL> EXEC pmsmmi.rpt.rpt_ethncty_race_smry (15)

                 

                 

                PL/SQL procedure successfully completed.

                 

                 

                SQL> DECLARE 

                          v_end_ts   VARCHAR2 (30);

                       BEGIN

                          SELECT TO_CHAR (SYSTIMESTAMP, 'yyyymmddfxDyhh24miss.ff6') INTO v_end_ts FROM DUAL;

                          :bv_end_ts := v_end_ts;

                       END;

                      

                /

                 

                 

                PL/SQL procedure successfully completed.

                 

                 

                Elapsed: 00:00:00.062

                SQL> PRINT bv_end_ts

                 

                 

                BV_END_TS

                ---------

                20150702Thu112630.805786

                SQL> EXEC :bv_elps_ts := TO_CHAR (TO_TIMESTAMP (:bv_end_ts, 'yyyymmddfxDyhh24miss.ff6') - TO_TIMESTAMP (:bv_start_ts, 'yyyymmddfxDyhh24miss.ff6'), 'hh:mi:ss.ff6')

                 

                 

                PL/SQL procedure successfully completed.

                 

                 

                SQL> PRINT bv_elps_ts

                 

                 

                BV_ELPS_TS

                ----------

                +000000 00:00:13.030241000

                SQL> SPOOL OFF

                • 6. Re: SQL*Plus "timing" command in SQL Developer
                  Gary Graham-Oracle

                  As the other poster said, this works fine for me, including putting set timing on/off in scripts.  For the worksheet, the timing results in the script output area, in the spool file, and the Script Output toolbar message area all match perfectly.  As for the timing command, it is either not currently supported or will not be supported, not sure which.

                   

                  My environment is Win 7 64-bit. What is your OS?  Either we have a port specific bug, or your installation is somehow not right.

                   

                  Edit: it seems I am wrong about this behavior when calling a procedure.

                  If I execute a procedure rather than a simple SQL statement, the toolbar message shows a slightly longer elapsed time, but the spool and script output area values match.

                  • 7. Re: SQL*Plus "timing" command in SQL Developer
                    Galo Balda

                    Gary,

                     

                    Do you get the elapsed time in the script output panel when you execute a stored procedure? I only get it when I execute queries.

                     

                    Thanks,

                    Galo

                    • 8. Re: SQL*Plus "timing" command in SQL Developer
                      Gary Graham-Oracle

                      Galo,

                       

                      I think you must be correct. I cannot reproduce what I thought I saw when running just the procedure.  SQLcl has the same issue, but SQL*Plus works.  I will log another bug.

                       

                      Thanks!