Show the time at the SQL prompt

The following is application development tip #6 from "30 tips in 30 minutes," brought to you by the IOUG. Return to the main page for more tips on this topic.

You want to know how long a SQL query ran? You want to know how long a PL/SQL job took to complete? You can fetch the system date and format in before and after the SQL*Plus operation you want to perform. A better way to check the time is to set a SQL*Plus variable named time. If you type this at the SQL*Plus prompt:

set time on

then the completion of each command will display a prompt with the current time. You can figure out the amount of time your procedure ran by subtracting the two times displayed at the SQL*Plus prompt.

14:30:10 SQL > execute mylongprocedure();
PL/SQL procedure successfully completed.
14:50:10 SQL >

Your procedure took 20 minutes to run.

Get more tips in minutes! Return to the main page.

About the author: Kenny Smith has been working with Oracle technology on HP servers for over a decade. He specializes in Oracle database architecture, database administration and development. He has presented at numerous Oracle conferences on two continents. He has published many articles describing Oracle solutions and has co-authored "Oracle backup and recovery 101" from Oracle Press.

IOUG: Become a member of the IOUG to access the paper referenced here and a repository of technical content created for Oracle users by Oracle users.