Menu
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;
243
Answer: For seeing the elapsed time for an individual query, you can see individual query response time in SQL*Plus with the "set timing on" command. For DBA's, Oracle has several tools for measuring system-wide response time using v$sysmetric, v$active_session_history, v$sqlarea and v$sysmetric_summary.
It essentially records the clock time before and after the SQL command execution, then displays the run time difference. There are two fundamental shortcomings with this technique. First, its scope is a single command, so for a script of commands, the DBA adds up those numbers himself.
The SQL execute elapsed time Oracle metric is the amount of elapsed time SQL statements are executing. Note that for SQL select statements this also includes the amount of time spent performing fetches of query results. Also see these important notes on measuring SQL ordered by elapsed time and Oracle DB time.
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
