Timer for sql script or Elapsed time

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 stop

 

You 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

declare
rec_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

Read Full Post »