14.4 How do I…Determine elapsed execution time in SQL*Plus?
Problem
How can I measure the SQL*Plus execution time of SQL statements I am writing or modifying?
Technique
Two methods can be used for timing the execution of SQL statements within SQL*Plus. Users can time the performance of a single query by setting the value of the TIMING system variable to ON. Alternatively, the SQL*Plus TIMING command enables you to set multiple timers and time groups of statements. This command allows you to give each timer a name and view timing information individually.
The command SET TIMING ON sets the SQL*Plus system variable ON so that SQL*Plus reports the duration of each SQL statement. This behavior will be in effect until the SET TIMING OFF command ends the timing of individual statements. The TIMING command has various keywords that control the use of the command. The syntax of the TIMING command is shown as follows:
TIMING START TIMERNAME
The command above creates a timer with the name specified on the command line.
Note-The SQL*Plus timer facility reports elapsed time in milliseconds. This is a real-time calculation; it does not account for system loads or CPU utilization levels in effect at the time of the query.
Steps
1. Start SQL*Plus, connect as the WAITE user and issue the SET TIMING ON command so that SQL*Plus starts timing individual SQL statements.
2. If you have not already done so for How-To’s 14.2 and 14.3, run the scripts CHP14_2.SQL and CHP14_5.SQL. Both scripts create tables similar to the common EMP and DEPT tables residing in SCOTT’s schema. The first script accomplishes this via an object enabled methodology and the second uses traditional purely relational tables. The output of CHP14_2.SQL appears in Listing 14.1 and the output of CHP14_5.SQL appears in Listing 14.2.
3. Use the START command to load and execute the script CHP14_7.SQL. This script contains two SQL statements to extract the employee numbers and names for all personnel in the chemistry department whose names contain the letter S. The script and its output appear in Figure 14.4.
The output indicates that the SELECT statement on the object embedded tables took 21 milliseconds while the functionally equivalent command on the purely relational tables required only 10 milliseconds.
4. Turn off the TIMING system variable by issuing the SET TIMING OFF command. The OFF keyword causes SQL*Plus to stop timing statements.
5. The TIMING system variable is useful should you need to time individual statements. You need the TIMING command, however, to generate the elapsed time for a script containing multiple SQL statements. It is possible to start multiple timers by starting another timer before stopping the first timer. The TIMING command does not exhibit elapsed time after Oracle8 processes each SQL statement. Instead, you must issue the TIMING SHOW or TIMING STOP command to review elapsed time since the beginning of the timing interval.
The CHP14_8.SQL script provides examples of the behavior of the TIMING command. Figure 14.5 shows the script and its output.
The first command in the script creates and starts a timer named MYTIMER. The next two commands select the name of the current user and the current date from the DUAL table. The next command shows the elapsed time since the script started the timer. The last command stops the timer and exhibits the final elapsed time value.
How It Works
Step 1 shows how to set the TIMING system variable on, so that SQL*Plus will report timing information after processing each subsequent SQL statement. Step 2 implements some sample objects and tables to feed the queries in Step 3. Step 4 shows how to disable individual SQL statement execution timing. Step 5 explains the use of the TIMING command to time collections of SQL statements.
Comments
The TIMING STOP command shows the value of the timer when it is stopped. This value is the elapsed time since the timer started. Timers cannot indicate how much of the CPU or other resources the timed SQL statements used. You cannot reference a timer after you have stopped it; Oracle8 will generate a no timing elements to show message should you attempt to do so. The TIMING command should be used for timing an entire script or a set of statements. The usual timer invocation structure suggests that the first command of the script starts the timer and the last command stops it.