Oracle 查询执行时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3559189/
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
Oracle query execution time
提问by user429743
I would like to get the query execution time in Oracle. I don't want the time Oracle needs to print the results - just the execution time.
我想在 Oracle 中获取查询执行时间。我不想要 Oracle 需要打印结果的时间 - 只是执行时间。
In MySQL it is easy to get the execution time from the shell.
在 MySQL 中，很容易从 shell 获取执行时间。
How can I do this in SQL*Plus?
如何在SQL*Plus 中执行此操作？
回答by Adam Musch
One can issue the SQL*Plus command SET TIMING ONto get wall-clock times, but one can't take, for example, fetch time out of that trivially.
可以发出 SQL*Plus 命令SET TIMING ON来获取挂钟时间，但不能简单地从中获取时间。
The AUTOTRACE setting, when used as SET AUTOTRACE TRACEONLYwill suppress output, but still perform all of the work to satisfy the query and send the results back to SQL*Plus, which will suppress it.
AUTOTRACE 设置，当使用 as 时SET AUTOTRACE TRACEONLY将抑制输出，但仍执行所有工作以满足查询并将结果发送回 SQL*Plus，后者将抑制它。
Lastly, one can trace the SQL*Plus session, and manually calculate the time spent waiting on events which are client waits, such as "SQL*Net message to client", "SQL*Net message from client".
最后，可以跟踪 SQL*Plus 会话，并手动计算等待客户端等待事件所花费的时间，例如“SQL*Net 消息到客户端”、“SQL*Net 消息来自客户端”。
回答by OMG Ponies
Use:
用：
set serveroutput on
variable n number
exec :n := dbms_utility.get_time;
select ......
exec dbms_output.put_line( (dbms_utility.get_time-:n)/100) || ' seconds....' );
Or possibly:
或者可能：
SET TIMING ON;
-- do stuff
SET TIMING OFF;
...to get the hundredths of seconds that elapsed.
...获得经过的百分之几秒。
In either case, time elapsed can be impacted by server load/etc.
在任何一种情况下，经过的时间都会受到服务器负载/等的影响。
Reference:
参考：
回答by gavenkoa
select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elapsed from v$sql
order by LAST_LOAD_TIME desc
More complicated example (don't forget to delete or to substitute PATTERN):
更复杂的例子（不要忘记删除或替换PATTERN）：
select * from (
select LAST_LOAD_TIME, to_char(ELAPSED_TIME/1000, '999,999,999.000') || ' ms' as TIME,
MODULE, SQL_TEXT from SYS."V_$SQL"
where SQL_TEXT like '%PATTERN%'
order by LAST_LOAD_TIME desc
) where ROWNUM <= 5;
回答by Gary Myers
I'd recommend looking at consistent gets/logical reads as a better proxy for 'work' than run time. The run time can be skewed by what else is happening on the database server, how much stuff is in the cache etc.
我建议将一致的获取/逻辑读取视为比运行时更好的“工作”代理。运行时间可能会因数据库服务器上发生的其他事情、缓存中的内容等而产生偏差。
But if you REALLY want SQL executing time, the V$SQL view has both CPU_TIME and ELAPSED_TIME.
但是如果你真的想要 SQL 执行时间，V$SQL 视图有 CPU_TIME 和 ELAPSED_TIME。
