如何为 oracle 选择查询计时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4206623/
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
how to time an oracle select query
提问by learn_plsql
what is the best way to find out how much time an oracle select statement takes. I have the following query for which I want to find out the time, however, since this query brings four thousand records and it takes time to display those 4 thousand records on the screen, the elapsed time stated might not be correct.
找出 oracle select 语句花费多少时间的最佳方法是什么。我有以下查询，我想找出时间，但是，由于此查询带来了 4000 条记录，并且在屏幕上显示这 4000 条记录需要时间，因此所述经过的时间可能不正确。
Is there a way I can wrap this into a cursor and then run it from sql plus so that I get the correct time it takes to execute this?
有没有办法可以将它包装到一个游标中，然后从 sql plus 运行它，以便我获得执行它所需的正确时间？
SELECT a.code, NVL(a.org, ' '), NVL(a.office_number, ' '), SUBSTR(a.code, 0, 2)
FROM PARTICIPANT a WHERE a.type_code = 'PRIME';
采纳答案by Mike Meyers
There are a couple of ways I can think of.
我能想到的有几种方法。
I normally do this sort of thing by running it into a table with CREATE TABLE AS SELECT...., which means I often litter my schema with many tables named MIKE_TEMP_1.
我通常通过将它运行到一个表中来做这种事情CREATE TABLE AS SELECT....，这意味着我经常用许多名为MIKE_TEMP_1.
Other option is in SQL*Plus to use SET AUTOTRACE TRACEONLYwhich should run all the query but suppress the printing of the results.
另一个选项是在 SQL*Plus 中使用SET AUTOTRACE TRACEONLY，它应该运行所有查询但禁止打印结果。
回答by Jeffrey Kemp
In SQL*Plus you can also use the simple TIMING option:
在 SQL*Plus 中，您还可以使用简单的 TIMING 选项：
SQL> SET TIMING ON
SQL> SELECT bla FROM bla...
...
Elapsed: 00:00:00:01
SQL> SELECT bar FROM foo...
...
Elapsed: 00:00:23:41
SQL> SET TIMING OFF
This will report timing information for each statement individually.
这将单独报告每个语句的时间信息。
Another option is to set up individual timers:
另一种选择是设置单独的计时器：
SQL> TIMING START mytimer
SQL> ... run all my scripts ...
SQL> TIMING STOP
timinig for: mytimer
Elapsed: 00:00:08.32
You can even nest these individual timers - the TIMING STOPpops the most recent timer off a stack.
您甚至可以嵌套这些单独的计时器 -TIMING STOP从堆栈中弹出最近的计时器。
回答by Alex Poole
Options that spring to mind:
想到的选项：
a) use an outer select, which may not be entirely accurate if the optimizer mangles it but can give a good idea:
a) 使用外部选择，如果优化器对其进行破坏，则可能不完全准确，但可以提供一个好主意：
SELECT COUNT(*) from (
SELECT a.code, NVL(a.org, ' '), NVL(a.office_number, ' '), SUBSTR(a.code, 0, 2)
FROM PARTICIPANT a WHERE a.type_code = 'PRIME'
);
b) put it in a script, run it from the command line and redirect the output to a file.
b) 把它放在一个脚本中，从命令行运行它并将输出重定向到一个文件。
c) turn spool on and termout off (not sure about that one).
c）打开线轴并关闭终端（不确定那个）。
d) set autotrace traceonly(which @MikeyByCrikey beat me to).
d）set autotrace traceonly（@MikeyByCrikey 击败了我）。
回答by Gary Myers
You can go to V$SQL where you have the following columns :
你可以去 V$SQL 那里你有以下列：
APPLICATION_WAIT_TIME
CONCURRENCY_WAIT_TIME
CLUSTER_WAIT_TIME
USER_IO_WAIT_TIME
PLSQL_EXEC_TIME
CPU_TIME
ELAPSED_TIME
but they are an aggregate for all executions of that SQL. You can do a before/after snapshot and work out the difference if no-one else is running the SQL.
但它们是该 SQL 的所有执行的聚合。如果没有其他人在运行 SQL，您可以做一个之前/之后的快照并计算出差异。
回答by Asper
Just do not display query results
只是不显示查询结果
SET TERMOUT OFF
