SQL*Plus Timers

SQL*Plus TIMING is a handy little utility that you can use to do a performance analysis on any commands or blocks run during a period. It is different from the SET TIMING command which is used to display timing data after each SQL command or PL/SQL block you run.

Here is an example:

TIMING SHOW lists the current timer’s name and timing data:

SQL> timing show
SP2-0325: no timing elements to show

TIMING START sets up a timer and makes t1 (or whatever timer name you like) the name of the timer:

SQL> timing start t1
SQL> timing show
timing for: t1
Elapsed: 00:00:06.95

Another call to TIMING SHOW displays the elapsed time since the start of the timer:

SQL> timing show
timing for: t1
Elapsed: 00:00:16.01

You can have more than one active timer by STARTing additional timers before STOPping the first; SQL*Plus nests each new timer within the preceding one. The timer most recently STARTed becomes the current timer:

SQL> timing start t2

t2 is the current timer now:

SQL> timing show
timing for: t2
Elapsed: 00:00:02.25

TIMING STOP lists the current timer’s name and timing data, then deletes the timer. If any other timers are active, the next most recently STARTed timer becomes the current timer, in this example it’s t1:

SQL> timing stop
timing for: t2
Elapsed: 00:00:18.81
SQL> timing show
timing for: t1
Elapsed: 00:01:14.18
SQL> timing stop
timing for: t1
Elapsed: 00:01:22.09
SQL> timing show
SP2-0325: no timing elements to show

TIMING with no clauses lists the number of active timers:

SQL> timing
no timing elements in use    
SQL> timing start t1
SQL> timing
1 timing element in use
SQL> timing start t2
SQL> timing
2 timing elements in use

CLEAR TIMING deletes all timers:

SQL> clear timing
timing for: t2
Elapsed: 00:22:38.36
timing for: t1
Elapsed: 00:22:53.96

Sources and Resources:

• SQL*Plus Timing by David Clement
• SQL Timing utility by Dominic Giles

Filed in Oracle, Tips on 24 Aug 10 | Tags: sqlplus

---