SQL*Plus Timing

David Clement

September 2004

Oracle versions 7-8

SQL*Plus has a useful feature that reports how long a statement takes. To turn this feature on, enter

          set timing on

at the SQL*Plus prompt. If you then enter a SQL statement, you will see a timing result added to the normal output.

With versions of SQL*Plus for Unix and Linux, the timing result appears in hours, minutes, seconds, and fractions of seconds, like this.

          Elapsed: 00:00:09.14

With versions of SQL*Plus for Windows, the timing result appears in milliseconds, like this.


                  real: 1507

To turn timing off, enter

          set timing off

at the SQL*Plus prompt.

Of course, you can enable this feature in your login.sql script if you like.

SQL*Plus timing can also use timer names. You could use this feature, for example, to track how long a script takes as a whole at the same time as you track how long each of the script's component SQL statements takes. Here is an example.


						            SQL> timing start outer
          SQL> timing show
          timing for: outer
          Elapsed: 00:00:02.35
          SQL> timing start inner
          SQL> timing show
          timing for: inner
          Elapsed: 00:00:01.90
          SQL> timing stop
          timing for: inner
          Elapsed: 00:00:07.33
          SQL> timing show
          timing for: outer
          Elapsed: 00:00:15.35
          SQL> timing stop
          timing for: outer
          Elapsed: 00:00:20.04
          SQL> spool off

"Outer" and "inner" in the sample above are just arbitrary names; I could have typed "abc" and "xyz."