Get execution time of sql script in oracle sqlplus

Question

I have a script that i use to load my data into my tables in Oracle (by a list of insert statements). How can i get the execution time of the entire loading process? I have tried with set timing on, but that gives me a duration for each insert statement and not the entire process. The script is shown below:

spo load.log
prompt '**** load data ****'
set termout off
@@inserts.sql
commit;
set termout on
prompt '**** done ****'
spo off
exit;

Answer 1

Try this, add the following at the beginning and it remembers the current time:

set serveroutput on
variable n number
exec :n := dbms_utility.get_time

Add this at the end and it calculates the time elapsed:

exec :n := (dbms_utility.get_time - :n)/100
exec dbms_output.put_line(:n)

Answer 2

If you are on Unix, you can also do it like that:

time sqlplus @inserts.sql

It will print:

real    0m9.34s
user    0m2.03s
sys     0m1.02s

The first line gives the total execution time.

Answer 3

Not sure why everybody is making it so complex. Simple as:

SQL> set timing on
SQL> select 1 from dual;

         1
----------
         1

1 row selected.

Elapsed: 00:00:00.00
SQL> 

Answer 4

It old question but i have found easy way to measure time of running a script in sqlplus. You just have to add this on the beginning

timing start timing_name

And this on the end of a script

timing stop

More information about this command can be found at Oracle's SQL*Plus® User's Guide and Reference: Collecting Timing Statistics

Answer 5

What you're describing is essentially a way to audit the script's execution. Whether it's an elapsed time, or specific start and end times you're capturing you want to log them properly to see if things went well (or if not, why not).

Here's a template similar to what we use for capturing and logging all database activity we are implementing. We use it via sqlplus.exe for all DDL updates (e.g. CREATE TABLE) and for inserts into setup tables.

--Beginning of all SQL scripts:
set serveroutput on feedback on echo on verify on sqlblanklines on timing on define on
col time new_v v_time
col name new_v v_name
col user new_v v_user

select name, USER, to_char(sysdate, 'YYYYMMDD-HH24MISS') time  from v$database;

--Creates a new log file every time the script is run, and it's immediately
--obvious when it was run, which environment it ran in, and who ran it.
spool &v_time._&v_name._&v_user..log 

--Run the select again so it appears in the log file itself
select name, USER, to_char(sysdate, 'YYYYMMDD-HH24MISS') time  from v$database;

Place the body of your work here.

--End of all SQL scripts:
select name, USER, to_char(sysdate, 'YYYYMMDD-HH24MISS') time  from v$database;
spool off