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;
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>
set timing on will print the time for each statement, this question is about getting the time for the entire script.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
Timing command is obsolete.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)
PL/SQL procedure successfully completed. and not the time :), is it possible to use the systimestamp to have date and etc. also? The script will at some point take several hours and run during the night, so the date will be important!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.
date ; echo exit | sqlplus "/ as sysdba" @catalog.sql > ~/catalog.out ; dateWhat 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
