Use the TIMING START command to start a timer. If you like, you can give the timer a name, but you don’t have to. Timing starts the moment the command is executed. The following example starts a new timer, and gives it a name of for_testing:

SQL> TIMING START for_testing

You stop the timer and display its final value by issuing the TIMING STOP command as follows:

SQL> TIMING STOP
timing for: for_testing
 real: 56460

In this case, the timer ran for a total elapsed time of 56.460 seconds.

You can display the value of a timer without stopping it. This is useful if your script is executing several SQL queries and you want to see the cumulative elapsed time after each one. For example:

SQL> TIMING START for_show
SQL> TIMING SHOW
timing for: for_show
 real: 2250
SQL> TIMING SHOW
timing for: for_show
 real: 3790
SQL> TIMING SHOW
timing for: for_show
 real: 5380
SQL> TIMING SHOW
timing for: for_show
 real: 6920

You can see from this example that once I got going, it took me a tad more than 1 1/2 seconds to type each TIMING SHOW command.

Timers can be nested, allowing you to time a group of operations, while simultaneously timing each individual operation within the larger group. The following example shows a timer being started, and while that’s running, two more timers are started and stopped. Finally, the first timer is also stopped.

SQL> TIMING START first
SQL> TIMING START second
SQL> TIMING STOP
timing for: second
 real: 2630
SQL> TIMING START third
SQL> TIMING STOP
timing for: third
 real: 2360
SQL> TIMING STOP
timing for: first
 real: 19160

The important thing to notice here is that the first timer kept running during this entire example. The total elapsed time was a bit over 19 seconds, while each of the intermediate operations took a bit over two seconds.

The following example shows how this nesting feature could be used. It runs a script to delete data and reports the elapsed time for each DELETE statement, as well as the total elapsed time for the script as a whole.

SET ECHO ON
TIMING START entire_script

--Delete project hours data and time the operation.
TIMING START delete_project_hours
DELETE FROM project_hours;
TIMING STOP

--Delete project data and time the operation.
TIMING START delete_projects
DELETE FROM project;
TIMING STOP

--Delete employee data and time the operation.
TIMING START
DELETE FROM employee;
TIMING STOP

COMMIT;

--Show the overall elapsed time for the entire script.
TIMING STOP

Here is the output from running the above script:

SQL> TIMING START entire_script
SQL> 
SQL> --Delete project hours data and time the operation.
SQL> TIMING START delete_project_hours
SQL> DELETE FROM project_hours;
SQL> TIMING STOP
timing for: delete_project_hours
 real: 1100
SQL> 
SQL> --Delete project data and time the operation.
SQL> TIMING START delete_projects
SQL> DELETE FROM project;
SQL> TIMING STOP
timing for: delete_projects
 real: 160
SQL> 
SQL> --Delete employee data and time the operation.
SQL> TIMING START
SQL> DELETE FROM employee;
SQL> TIMING STOP
 real: 220
SQL> 
SQL> COMMIT;
SQL> 
SQL> --Show the overall elapsed time for the entire script.
SQL> TIMING STOP
timing for: entire_script
 real: 1750

You can see that the elapsed time was displayed for each statement and for the script as a whole.

The TIMER command by itself will cause SQL*Plus to report the number of timers that are currently active. The following example shows how the count goes up each time you start a timer and back down each time you stop one:

SQL> TIMING START
SQL> TIMING
1 timing element in use
SQL> TIMING START
SQL> TIMING
2 timing elements in use
SQL> TIMING STOP
 real: 3510
SQL> TIMING
1 timing element in use
SQL> TIMING STOP
 real: 9170
SQL> TIMING
no timing elements in use

You can stop and delete all timers at once with the CLEAR TIMING command. As each timer is stopped, its final value is displayed. Here’s an example:

SQL> TIMING START first
SQL> TIMING START second
SQL> TIMING START third
SQL> CLEAR TIMING
timing for: third
 real: 2300
timing for: second
 real: 7250
timing for: first
 real: 10160