oracle 获取oracle sqlplus中sql脚本的执行时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8401270/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get execution time of sql script in oracle sqlplus
提问by aweis
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:
我有一个脚本，用于将数据加载到 Oracle 中的表中（通过插入语句列表）。如何获取整个加载过程的执行时间？我试过set timing on，但这给了我每个插入语句的持续时间，而不是整个过程。脚本如下所示：
spo load.log
prompt '**** load data ****'
set termout off
@@inserts.sql
commit;
set termout on
prompt '**** done ****'
spo off
exit;
回答by Jon Coulter
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>
回答by Dcortez
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
这是一个老问题，但我找到了一种简单的方法来测量在 sqlplus 中运行脚本的时间。你只需要在开头添加这个
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
有关此命令的更多信息可以在 Oracle 的 SQL*Plus? 用户指南和参考：收集计时统计信息
回答by u710480
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)
回答by BQ.
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.exefor all DDL updates (e.g. CREATE TABLE) and for inserts into setup tables.
这是一个类似于我们用于捕获和记录我们正在实施的所有数据库活动的模板。我们将它sqlplus.exe用于所有 DDL 更新（例如CREATE TABLE）和插入设置表。
--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
回答by Stamm
If you are on Unix, you can also do it like that:
如果你在 Unix 上，你也可以这样做：
time sqlplus @inserts.sql
It will print:
它会打印：
real 0m9.34s
user 0m2.03s
sys 0m1.02s
The first line gives the total execution time.
第一行给出了总执行时间。
