如何对Oracle的select查询进行时间优化？

Question

如何最好地找出 Oracle Select 语句花费的时间？我有以下查询需要查找时间，但由于此查询带来了四千条记录并且需要时间在屏幕上显示这些 4,000 条记录，所以所述的经过时间可能不正确。

是否有一种方法可以将其包装为光标(cursor)，然后从 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';

- learn_plsql

---

你目前是如何获取“经过时间”的？ - Paul Sonier

我只是通过在运行查询之前执行“set timing on”来在sqlplus上运行它。这会在屏幕上显示记录，然后给我一个经过的时间。我认为在屏幕上绘制记录也被视为经过的时间。 - learn_plsql

Answer 1

在 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

这将为每个语句单独报告时间信息。

另一个选项是设置单独的计时器：

SQL> TIMING START mytimer
SQL> ... run all my scripts ...
SQL> TIMING STOP
timinig for: mytimer
Elapsed: 00:00:08.32

你甚至可以嵌套这些单独的计时器 - TIMING STOP 会从堆栈中弹出最近的计时器。

- Jeffrey Kemp

---

抱歉，我没有看到你的评论已经在处理这个了。噢，那么这个答案也许对其他人有帮助。 - Jeffrey Kemp

Answer 2

我可以想到几种方法。

通常我会用 CREATE TABLE AS SELECT.... 将其放入表格中来完成此类操作，这意味着我的模式中经常会出现许多名为 MIKE_TEMP_1 的表。

另一种选择是在 SQL*Plus 中使用 SET AUTOTRACE TRACEONLY，这将运行所有查询但抑制结果的打印。

- Mike Meyers

---

请注意，执行CREATE TABLE AS SELECT操作将会生成一个与底层SELECT语句不同的查询计划。因此，这并不总是一个特别好的测试。但SET AUTOTRACE TRACEONLY选项值得一试。 - Justin Cave

是的。自动跟踪仅跟踪胜利。 - learn_plsql

@justin 我从事数据仓库工作，所以CTAS计划通常是我想要的。但我接受你的观点。 - Mike Meyers

CTAS会增加物理写入的额外成本/时间。我会选择TRACEONLY选项。 - Gary Myers

Answer 3

我想到的选项如下：

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) 把它放在脚本中，从命令行运行并将输出重定向到文件。 c) 打开 spool 并关闭 termout （不确定）。 d) set autotrace traceonly（被 @MikeyByCrikey 先我一步实现了）。

- Alex Poole

---

是的，(c) 可以工作。当然，SQL Plus 将输出到文件需要一些有限的时间，但它与输出到屏幕相比并不重要。 - Tony Andrews

Answer 4

您可以进入V$SQL，其中包含以下列：

APPLICATION_WAIT_TIME
CONCURRENCY_WAIT_TIME
CLUSTER_WAIT_TIME
USER_IO_WAIT_TIME
PLSQL_EXEC_TIME
CPU_TIME
ELAPSED_TIME

但它们是该SQL的所有执行的聚合。如果没有其他人在运行SQL，您可以进行先/后快照并计算差异。

- Gary Myers

Answer 5

只需不显示查询结果

SET TERMOUT OFF

- Asper