Do you want to track how long your sql script ran? Of course you can set-up your sql plus session with several options including elapsed time, dbms_utility.get_time, display time at the beginning and at the end of the script.
Do you know there is a timer functionality in SQL Plus? I recently realized and never come across before.
Here is how it works.
Syntax at sql plus…
timing start <label> SQl statement 1. sql statement 2. sql statement 3 exec proc1 @mysql.sql …. timing stopYou can have any number of sql statements (or call/exec sql scripts) between first and last line. <label> is just any name that you want to display. At the end of the script it display how long it took took for <label>
This is much convenient, I guess, to see how long the whole script took for executing including any waiting in between.
Below is the example output. I created attached files and running timer_ex.sql which calls timer_ex1.sql, timer_ex2.sql, timer_ex3.sql sql scripts. To pretend some thing is running for little longer, I used pause in between.
I waited longer than 1 min on the last pause, you can see that output says mytimer elapsed close to 2 min even I add up individual elapsed timing (enabled through timing on on sqlplus) less than what shown for mytimer.
Example output.
SQL> @timer_ex.sql SYSDATE --------- 22-FEB-13 Elapsed: 00:00:00.16 Looped through this number of iterations:1000000 PL/SQL procedure successfully completed. Elapsed: 00:00:00.20 Paused ...[enter] to proceed. SYSDATE --------- 22-FEB-13 Elapsed: 00:00:00.17 paused ...[enter] 'AB --- abc Elapsed: 00:00:00.15 paused ...[enter] <-- Here I waited longer than 1 min. 'XY --- xyz Elapsed: 00:00:00.12 timing for: mytimer Elapsed: 00:01:53.09 SQL>
So this timer functionality in sql plus is very useful if your project requires to run several sql script and want to know how long the process took.
What do you think ? Do you use this?
Scripts.
timer_ex1.sql select sysdate from dual; timer_ex2.sql declarerec_cnt number:=0;
begin
for i in 1..1000000 loop
rec_cnt := rec_cnt + 1;
end loop;
dbms_output.put_line(‘Looped through this number of iterations:’||rec_cnt);
end;
/ timer_ex3.sql select sysdate from dual;
pause paused …[enter]
select ‘abc’ from dual;
pause paused …[enter]
select ‘xyz’ from dual; timer_ex.sql timing start mytimer
@timer_ex1.sql
@timer_ex2.sql
pause Paused …[enter] to proceed.
@timer_ex3.sql
timing stop