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.
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 ...