O'Reilly logo

Oracle SQL*Plus: The Definitive Guide, 2nd Edition by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Using SQL*Plus Timers

SQL*Plus comes with a crude, built-in timing facility that allows you to measure the elapsed time of a script or any portion of a script. You can even have SQL*Plus report the elapsed execution time of every SQL query and PL/SQL block automatically after each statement has executed. Timers can be nested so you can time the overall execution of a script, as well as the execution time of each individual statement.

Timings can be useful in spotting trends. It may be helpful to know, for example, if a script is taking longer to run. Timings can help compare the relative efficiency of two SQL statements. If you have two statements that return equivalent results and one consistently runs faster than the other, that's the one you probably want to go with.

Take timing with a grain of salt, however. The timer measures elapsed time, sometimes called wall-clock time, not CPU time, and many factors can throw it off. The network throughput might vary between the execution of two queries. The load on the server might vary as well. For example, one query might run more slowly than another because many other users simultaneously happened to hit the database. Be skeptical of one-time results. Look for consistency over several timings.

The SET TIMING Command

You can have SQL*Plus automatically report the elapsed time it takes to execute every query by issuing the SET TIMING ON command:

SET TIMING ON

Now, whenever you execute a query, SQL*Plus will report the elapsed time. With ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required