{"id":3129,"date":"2025-04-26T22:27:12","date_gmt":"2025-04-26T22:27:12","guid":{"rendered":"https:\/\/w3buddy.com\/?p=3129"},"modified":"2026-01-15T12:44:24","modified_gmt":"2026-01-15T07:14:24","slug":"what-is-set-time-on-and-set-timing-on-in-oracle","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/what-is-set-time-on-and-set-timing-on-in-oracle\/","title":{"rendered":"What is SET TIME ON and SET TIMING ON in Oracle?"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">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:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>SET TIME ON<\/strong><\/li>\n\n\n\n<li><strong>SET TIMING ON<\/strong><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">In this post, let&#8217;s quickly understand what they do and why you should use them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What Does SET TIME ON Do?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>SET TIME ON<\/strong> command displays the <strong>current system time<\/strong> before each SQL prompt.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is useful when you want to know exactly <strong>when<\/strong> you ran a particular command, especially when troubleshooting or monitoring activities.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">How to Use It<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Simply type:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SET TIME ON;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Example Output<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">16:30:55 SQL> SELECT * FROM employees;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Now, the time appears automatically before the SQL prompt.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What Does SET TIMING ON Do?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>SET TIMING ON<\/strong> command shows the <strong>elapsed time<\/strong> taken by each SQL command after it finishes running.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is very helpful for <strong>performance tuning<\/strong> because you can instantly see how long each query takes.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">How to Use It<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Type the following:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SET TIMING ON;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Example Output<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">14 rows selected.\nElapsed: 00:00:00.03<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Here, Oracle shows that the query took 0.03 seconds to complete.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Other Useful Related Commands<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Along with <code>SET TIME ON<\/code> and <code>SET TIMING ON<\/code>, here are a few more useful commands:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Command<\/th><th>Purpose<\/th><\/tr><\/thead><tbody><tr><td>SET SERVEROUTPUT ON<\/td><td>Displays output from PL\/SQL programs.<\/td><\/tr><tr><td>SET FEEDBACK ON<\/td><td>Shows the number of rows affected by a query.<\/td><\/tr><tr><td>SET SQLPROMPT<\/td><td>Customizes the SQL prompt text.<\/td><\/tr><tr><td>SET ECHO ON<\/td><td>Shows the command before executing it (helpful in scripts).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">These commands can make your Oracle SQL sessions much more informative and easier to manage.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Final Words<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use <strong>SET TIME ON<\/strong> when you want to see the exact time your commands are executed.<\/li>\n\n\n\n<li>Use <strong>SET TIMING ON<\/strong> when you need to measure how long your queries take.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Both are simple but very powerful tools, especially when you are tuning performance or troubleshooting issues!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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: In this post, let&#8217;s quickly understand what they do and why you should use them. What Does SET TIME [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-3129","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3129","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=3129"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3129\/revisions"}],"predecessor-version":[{"id":3166,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3129\/revisions\/3166"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3129"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=3129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}