SQL*Plus Feature: TIMING and SET TIMING command

SET TIMING—one of the most frequently used commands—displays timing statistics on each SQL command or PL/SQL block.

SET TIMING ON
DROP TABLE t PURGE;

Table dropped.

Elapsed: 00:00:00.03

CREATE TABLE t AS
SELECT mod(rownum,41)+1 n
  FROM dual
CONNECT BY level <= 10000;

Table created.

Elapsed: 00:00:00.92

What if you want to collect timing statistics for multiple group of statements?

TIMING command becomes handy in such situation—one of things that I knew and have forgotten over time. It can be used to collect data to do a performance analysis on any commands or blocks run during the period. It records timing data for an elapsed period of time, lists the current timer’s name and timing data, or lists the number of active timers. 

Syntax

• TIMI[NG] [START text | SHOW | STOP]

Options

• START text:  Sets up a timer and makes text the name of the timer. 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.
• SHOW: Lists the current timer’s name and timing data.
• 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.
• TIMING: with no clauses to list the number of active timers.
• CLEAR TIMING: removes all the timers.

Below code block demonstrates all the above-mentioned options:

SQL> TIMING
no timing elements in use
SQL> TIMING SHOW
SP2-0325: no timing elements to SHOW
SQL> TIMING START time_for_all
SQL> DROP TABLE t PURGE;

Table dropped.

SQL> TIMING
1 timing element in use
SQL> TIMING START time_for_create
SQL> CREATE TABLE t AS
  2  SELECT mod(rownum,41)+1 n
  3    FROM dual
  4  CONNECT BY level <= 10000;

Table created.

SQL> TIMING
2 timing elements in use
SQL> TIMING STOP
timing for: time_for_create
Elapsed: 00:00:00.07
SQL> TIMING SHOW
timing for: time_for_all
Elapsed: 00:00:00.10
SQL> CLEAR TIMING
timing for: time_for_all
Elapsed: 00:00:00.12

Let’s walk through the steps:

• Line 1: No timing elements defined
• Line 3: Returns SP2-0325 error as no timing element defined
• Line 5: Creates a timer named time_for_all
• Line 10: Shows that one timing element is defined and in use
• Line 12: Creates a nested timer named time_for_create
• Line 20: Shows that two timing elements are defined and in use
• Line 21: Stops the current timer, time_for_create, and shows the elapsed time as of current statement execution
• Line 25: Shows the elapsed time for current timer,time_for_all, as of current statement execution
• Line 28: Removes all the timers and shows the elapsed time of all the timers as of current statement execution