Some times I feel like I’ve missed out on a whole chunk on functionality in Oracle products. One little nugget is the “timing” function in SQL*Plus. This allows you to time groups of operations.

Obviously turning on is achieved with the “set timing on” operation. i.e

SQL > set timing on

SQL > select count(1) from all_objects;

COUNT(1)
----------
68653

Elapsed: 00:00:03.95

SQL>

Which is great but what if want to time mulitiple operations. Use the timing function and simply give the timer a name, in this case statement timer.

SQL> timing start statement_timer
SQL> select count(1) from all_objects;

COUNT(1)
----------
68653

SYS@orcl > /

COUNT(1)
----------
68653

SQL> timing show statement_timer;
timing for: statement_timer
Elapsed: 00:00:30.85
SQL>

Which times anything that went on in between the timer starting and finishing. In this case also my typing of the commands. Its a fantastic utility for timing stages in a batch job including call outs to os operations.