oracle 为什么 PLSQL 比 SQL*Plus 慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1682339/
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
Why is PLSQL slower than SQL*Plus
提问by Brad Bruce
I have several Oracle queries that perform well when run through SQL*PLUS. However when they are executed as a part of a PL/SQL package, they take MUCH longer.
我有几个 Oracle 查询在通过 SQL*PLUS 运行时表现良好。然而，当它们作为 PL/SQL 包的一部分执行时，它们需要更长的时间。
Our DBA has watched these queries take 10 minutes through PLSQL and 10 seconds through SQL*Plus.
我们的 DBA 观察到这些查询通过 PLSQL 需要 10 分钟，通过 SQL*Plus 需要 10 秒。
Does anybody have any pointers on where to look for the misconfiguration?
有没有人有任何关于在哪里寻找错误配置的指示？
Client - Windows 2000 Server - Linux (Oracle Enterprise)
客户端 - Windows 2000 Server - Linux (Oracle Enterprise)
Thanks
谢谢
--
——
Resolution:
解析度：
I wish I could have accepted everyone's answers. Several of them were quite helpful.
我希望我能接受每个人的答案。其中一些非常有帮助。
- The query was converting data types.
- The execution plans didn't match. (Hints fixed that.)
- The DBA was looking at the time the cursor was open instead of the query time.
- 查询正在转换数据类型。
- 执行计划不匹配。（提示解决了这个问题。）
- DBA 正在查看游标打开的时间而不是查询时间。
回答by Tony Andrews
Use SQL trace to see what the execution plans are in each case. One possibility that springs to mind (from experience): is the package binding the wrong type of values to the query? It could be that in SQL Plus you are running:
使用 SQL 跟踪查看每种情况下的执行计划。想到的一种可能性（根据经验）：包是否将错误类型的值绑定到查询？可能是在 SQL Plus 中您正在运行：
select * from mytable where id = '1234';
but in PL/SQL you are running:
但在 PL/SQL 中，您正在运行：
select * from mytable where id = p_id;
with p_id being defined as a number. That will force a TO_NUMBER on the ID column and prevent Oracle using the index.
p_id 被定义为一个数字。这将在 ID 列上强制使用 TO_NUMBER 并阻止 Oracle 使用该索引。
回答by Quassnoi
Most probably, it's not the queries that run longer but the overhead to process them in PL/SQL.
最有可能的不是查询运行时间更长，而是在PL/SQL.
When you process the query results in a PL/SQLscript, a context switch occurs. It requires to pass loads of data between Oracleprocesses and is quite slow.
在PL/SQL脚本中处理查询结果时，会发生上下文切换。它需要在Oracle进程之间传递大量数据并且速度很慢。
Like this code:
像这样的代码：
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;
runs for more than 3seconds on my machine, while this one:
3在我的机器上运行超过几秒钟，而这个：
SELECT SUM(1)
FROM dual
CONNECT BY
level <= 1000000
completes in only 0.5seconds.
只需0.5几秒钟即可完成。
The context switch also occurs when you call PL/SQLfrom SQL, like this:
当您调用PL/SQLfrom时也会发生上下文切换SQL，如下所示：
SELECT plsql_function(column)
FROM mytable
or when a trigger fires.
或者当触发器触发时。
回答by jva
Our DBA has watched these queries take 10 minutes through PLSQL and 10 seconds through PL/PSQL.
我们的 DBA 观察到这些查询通过 PLSQL 需要 10 分钟，通过 PL/PSQL 需要 10 秒。
I could understand if DBA wouldn't want to solve this issue for you but if your DBA really has seen both occurences and has not provided you with explain plans for both cases yet, then he really is not a very good DBA.
我可以理解 DBA 是否不想为您解决这个问题，但是如果您的 DBA 确实已经看到了这两种情况并且还没有为您提供这两种情况的解释计划，那么他真的不是一个很好的 DBA。
There probably is no misconfiguration, I've had it happen on myself - all bind variables, no constants, no hints. Run it directly - good performance. Put it inside BEGIN..END - bam, slow as hell. Turned out that sometimes queries just use different execution plans from within PL/SQL (that was Oracle 9.2).
可能没有配置错误，我自己也遇到过这种情况——所有绑定变量，没有常量，没有提示。直接运行它 - 良好的性能。把它放在 BEGIN..END 里面 - 呸，慢得要命。事实证明，有时查询只是在 PL/SQL（即 Oracle 9.2）中使用不同的执行计划。
My solution - used hints until PL/SQL version used the same plan as SQL.
我的解决方案 - 使用提示，直到 PL/SQL 版本使用与 SQL 相同的计划。
Other possible issues:
其他可能的问题：
- SQL*Plus returns only first 100 or so rows and then waits for you to ask for more, but PL/SQL has to process them all without asking. Trivial issue but sometimes overlooked.
- You use constants for SQL*Plus and bind variables for PL/SQL. Sometimes using constants allows optimizer to check for skewed data and it could use some other index.
- SQL*Plus 只返回前 100 行左右，然后等待您请求更多行，但 PL/SQL 必须在不询问的情况下处理它们。微不足道的问题，但有时被忽视。
- 对 SQL*Plus 使用常量，对 PL/SQL 使用绑定变量。有时使用常量允许优化器检查倾斜的数据，它可以使用其他一些索引。
回答by Sebastien
We faced a similar issue. An update query was running very slowly 17 Minutes when used in PL/SQL block and executing very fast (less than 2 seconds) when used outside PL/SQL.
我们遇到了类似的问题。更新查询在 PL/SQL 块中使用时运行非常缓慢 17 分钟，而在 PL/SQL 之外使用时执行速度非常快（少于 2 秒）。
We discovered that the execution plan used in PL/SQL was different.
我们发现 PL/SQL 中使用的执行计划是不同的。
Using "alter system flush shared_pool" solved the issue for us. It seemed to force PL/SQL to reconsider the execution plan to use.
使用“alter system flush shared_pool”为我们解决了这个问题。它似乎迫使 PL/SQL 重新考虑要使用的执行计划。
回答by David Aldridge
Are you truly comparing like-for-like here? Are you executing raw SQL statements in PL/SQL (the optimum case) or are you using explicit or implicit cursors to return values and then process them? There's a big difference.
你真的在这里比较同类吗？您是在 PL/SQL 中执行原始 SQL 语句（最佳情况）还是使用显式或隐式游标来返回值然后处理它们？有很大的不同。
回答by David Aldridge
To quote and extend Quassnoi:
引用和扩展 Quassnoi：
Most probably, it's not the queries that run longer but the overhead to process them in PL/SQL.
最有可能的不是查询运行时间更长，而是在 PL/SQL 中处理它们的开销。
When you process the query results in a PL/SQL script, a context switch occurs. It requires to pass loads of data between Oracle processes and is quite slow.
当您在 PL/SQL 脚本中处理查询结果时，会发生上下文切换。它需要在 Oracle 进程之间传递大量数据并且速度很慢。
Like this code:
像这样的代码：
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;
runs for more than 3 seconds on my machine, while this one:
在我的机器上运行超过 3 秒，而这个：
SELECT SUM(1)
FROM dual
CONNECT BY
level <= 1000000
completes in only 0.5 seconds.
仅在 0.5 秒内完成。
The context switch also occurs when you call PL/SQL from SQL, like this:
从 SQL 调用 PL/SQL 时也会发生上下文切换，如下所示：
SELECT plsql_function(column)
FROM mytable
or when a trigger fires.
One way to solve the context switch problem is to use a BULK COLLECT. If you are collecting a lot of rows, using BULK COLLECT INTO a collection of some type can dramatically speed up SQL in PL/SQL statements.
解决上下文切换问题的一种方法是使用 BULK COLLECT。如果您正在收集大量行，使用 BULK COLLECT INTO 某种类型的集合可以显着加快 PL/SQL 语句中的 SQL。
回答by darreljnz
DML (e.g. SELECT, UPDATE, DELETE) issued through SQLPlus is issued directly to Oracle's SQL engine whereas DML in a PLSQL procedure is first processed by PL/SQL (e.g to do variable bindings) and then sent to the SQL engine.
通过 SQLPlus 发布的 DML（例如 SELECT、UPDATE、DELETE）直接发布到 Oracle 的 SQL 引擎，而 PLSQL 过程中的 DML 首先由 PL/SQL 处理（例如进行变量绑定），然后发送到 SQL 引擎。
For the most part the same statement in PL/SQL will perform the same as SQL and both ways will usually produce the same execution plan. In my experience (usually when binding of variables is required) it can cause very different performance. I have seen times where a SELECT issued in SQL Plus takes a fraction of a second while a SELECT issued through PL/SQL takes 1-2 minutes.
大多数情况下，PL/SQL 中的相同语句将执行与 SQL 相同的语句，并且两种方式通常会产生相同的执行计划。根据我的经验（通常在需要绑定变量时）它会导致非常不同的性能。我见过在 SQL Plus 中发出的 SELECT 需要几分之一秒，而通过 PL/SQL 发出的 SELECT 需要 1-2 分钟的时间。
I recommend you tune your statement so it works just as well in PL/SQL as it does in SQL. Focus on binding variables correctly (using FORALL and BULK COLLECT) but also examine the execution plans and do unit tests.
我建议您调整您的语句，使其在 PL/SQL 中的效果与在 SQL 中的效果一样好。专注于正确绑定变量（使用 FORALL 和 BULK COLLECT），但也要检查执行计划并进行单元测试。
