What is SET TIME ON and SET TIMING ON in Oracle?
When you work with Oracle databases using SQL*Plus, SQLcl, or even tools like SQL Developer, you often need to track when your queries run and how long they take. Oracle provides two simple commands for this:
- SET TIME ON
- SET TIMING ON
In this post, let’s quickly understand what they do and why you should use them.
What Does SET TIME ON Do?
The SET TIME ON command displays the current system time before each SQL prompt.
This is useful when you want to know exactly when you ran a particular command, especially when troubleshooting or monitoring activities.
How to Use It
Simply type:
SET TIME ON;Example Output
16:30:55 SQL> SELECT * FROM employees;Now, the time appears automatically before the SQL prompt.
What Does SET TIMING ON Do?
The SET TIMING ON command shows the elapsed time taken by each SQL command after it finishes running.
This is very helpful for performance tuning because you can instantly see how long each query takes.
How to Use It
Type the following:
SET TIMING ON;Example Output
14 rows selected.
Elapsed: 00:00:00.03Here, Oracle shows that the query took 0.03 seconds to complete.
Other Useful Related Commands
Along with SET TIME ON and SET TIMING ON, here are a few more useful commands:
| Command | Purpose |
|---|---|
| SET SERVEROUTPUT ON | Displays output from PL/SQL programs. |
| SET FEEDBACK ON | Shows the number of rows affected by a query. |
| SET SQLPROMPT | Customizes the SQL prompt text. |
| SET ECHO ON | Shows the command before executing it (helpful in scripts). |
These commands can make your Oracle SQL sessions much more informative and easier to manage.
Final Words
- Use SET TIME ON when you want to see the exact time your commands are executed.
- Use SET TIMING ON when you need to measure how long your queries take.
Both are simple but very powerful tools, especially when you are tuning performance or troubleshooting issues!