为什么 PLSQL 比 SQL*Plus 慢

Question

我有几个 Oracle 查询在通过 SQL*PLUS 运行时表现良好。然而，当它们作为 PL/SQL 包的一部分执行时，它们需要更长的时间。

我们的 DBA 观察到这些查询通过 PLSQL 需要 10 分钟，通过 SQL*Plus 需要 10 秒。

有人知道在哪里寻找错误配置吗？

客户端 - Windows 2000 服务器 - Linux (Oracle Enterprise)

谢谢

--

解决方案：

我希望我能够接受每个人的答案。其中有几个非常有帮助。

• 查询正在转换数据类型。
• 执行计划不匹配。 （提示修复了这个问题。）
• DBA 正在查看光标所在的时间 打开而不是查询时间。

Answer 1

使用 SQL 跟踪来查看每种情况下的执行计划。想到的一种可能性（根据经验）：包是否将错误类型的值绑定到查询？可能在 SQL Plus 中您正在运行：

select * from mytable where id = '1234';

但在 PL/SQL 中您正在运行：

select * from mytable where id = p_id;

p_id 被定义为数字。这将在 ID 列上强制使用 TO_NUMBER 并阻止 Oracle 使用索引。

Answer 2

最有可能的情况是，运行时间较长的不是查询，而是在 PL/SQL 中处理它们的开销。

当您在 PL/SQL 脚本中处理查询结果时，会发生上下文切换。它需要在 Oracle 进程之间传递大量数据，并且速度相当慢。

就像这段代码：

DECLARE
        cnt INTEGER := 0;
        CURSOR  cr_main IS
        SELECT  1 AS id
        FROM    dual
        CONNECT BY
                level <= 1000000;
BEGIN
        FOR res IN cr_main
        LOOP
                cnt := cnt + res.id;
        END LOOP;
        DBMS_OUTPUT.put_line(cnt);
END;

在我的机器上运行超过 3 秒，而这段代码：

SELECT  SUM(1)
FROM    dual
CONNECT BY
        level <= 1000000

仅在 0.5 秒内完成。

当您从 SQL 调用 PL/SQL 时，也会发生上下文切换，如下所示：

SELECT  plsql_function(column)
FROM    mytable

或者当触发器触发时。

Answer 3

我们的 DBA 观察到这些查询通过 PLSQL 需要 10 分钟，通过 PL/PSQL 需要 10 秒。

如果 DBA 不想为您解决这个问题，我可以理解，但如果您的 DBA 确实看到了这两种情况，并且还没有为您提供这两种情况的解释计划，那么他确实不是一个很好的 DBA。

可能没有错误配置，我自己就遇到过这种情况 - 所有绑定变量，没有常量，没有提示。直接运行——性能良好。把它放在 BEGIN..END 里面 - 砰，慢得要命。事实证明，有时查询只是使用 PL/SQL（即 Oracle 9.2）中的不同执行计划。

我的解决方案 - 使用提示，直到 PL/SQL 版本使用与 SQL 相同的计划。

其他可能的问题：

1. SQL*Plus 仅返回前 100 个或
   所以行然后等待你要求更多，但是 PL/SQL 必须处理
   他们都无需询问。微不足道的问题，但有时会被忽视。
2. 对于 SQL*Plus 使用常量，对于 PL/SQL 使用绑定变量。有时，使用常量允许优化器检查倾斜数据，并且可以使用其他索引。

Answer 4

您真的在这里进行同类比较吗？您是在 PL/SQL 中执行原始 SQL 语句（最佳情况），还是使用显式或隐式游标返回值然后处理它们？有很大的不同。

Answer 5

我们遇到了类似的问题。更新查询在 PL/SQL 块中使用时运行速度非常慢 17 分钟，而在 PL/SQL 之外使用时执行速度非常快（不到 2 秒）。

我们发现PL/SQL中使用的执行计划是不同的。

使用“alter systemlushshared_pool”为我们解决了这个问题。它似乎迫使 PL/SQL 重新考虑要使用的执行计划。

Answer 6

引用并扩展 Quassnoi：

最有可能的是，运行时间更长的不是查询，而是在 PL/SQL 中处理它们的开销。

当您在 PL/SQL 脚本中处理查询结果时，会发生上下文切换。它需要在 Oracle 进程之间传递大量数据，并且速度相当慢。

像这样的代码：

DECLARE
        cnt INTEGER := 0;
        CURSOR  cr_main IS
        SELECT  1 AS id
        FROM    dual
        CONNECT BY
                level <= 1000000;
BEGIN
        FOR res IN cr_main
        LOOP
                cnt := cnt + res.id;
        END LOOP;
        DBMS_OUTPUT.put_line(cnt);
END;

在我的机器上运行了超过 3 秒，而这个：

SELECT  SUM(1)
FROM    dual
CONNECT BY
        level <= 1000000

仅在 0.5 秒内完成。

当您从 SQL 调用 PL/SQL 时，也会发生上下文切换，如下所示：

SELECT  plsql_function(column)
FROM    mytable
or when a trigger fires.

解决上下文切换问题的一种方法是使用 BULK COLLECT。如果要收集大量行，则使用 BULK COLLECT INTO 某种类型的集合可以显着加快 PL/SQL 语句中的 SQL 速度。

Answer 7

通过SQLPlus 发出的DML（例如SELECT、UPDATE、DELETE）直接发出到Oracle 的SQL 引擎，而PLSQL 过程中的DML 首先由PL/SQL 处理（例如进行变量绑定），然后发送到SQL 引擎。

在大多数情况下，PL/SQL 中的相同语句将执行与 SQL 相同的操作，并且两种方式通常会产生相同的执行计划。根据我的经验（通常当需要绑定变量时），它可能会导致非常不同的性能。我曾见过在 SQL Plus 中发出的 SELECT 只需要几分之一秒的时间，而通过 PL/SQL 发出的 SELECT 则需要 1-2 分钟。

我建议您调整您的语句，使其在 PL/SQL 中与在 SQL 中一样有效。重点关注正确绑定变量（使用 FORALL 和 BULK COLLECT），但也要检查执行计划并进行单元测试。