Oracle PL/SQL过程的运行速度比SQL慢 [英] Oracle PL/SQL procedure runs slower than SQL

问题描述

似乎我已经找到答案回答我自己的问题:

Seems I have found answer my own question :

首先-让我改写我的问题:

First - let me reword my question :

在完全相同的环境和条件下"-一些用户抱怨，当相同的SQL成为PL/SQL过程或程序包的一部分时，在几秒钟内运行的同一SQL会花费一个多小时.

"Under exactly same environment and conditions" - Some users are complaining that same SQL which runs in few seconds takes over an hour, when same SQL is made part of a PL/SQL procedure or package.

这里的关键词是相同的条件和环境". Oracle版本是12.1.0.2.0.对于SQL和PL/SQL，表，行数，操作，统计信息等都是相同的.

The key words here are "same conditions and environment". The Oracle Version is 12.1.0.2.0. The table, row count, operation, statistics etc. are same for both (SQL and PL/SQL).

这是一个快速测试.

我选择的陈述是直接更新的-因此几乎没有网络或终端显示延迟

The statement I choose is straight update - so practically no network or terminal display delay

1. 创建一个包含200,000行的表. (没有索引等.)
2. 在服务器级别打开SQL_TRACE = ON

SQL: 一种.更新统计 b.设置时间

SQL : a. update stats b. set timing on

Update TABLE_1 set Character_Col = 'XXXX';

PL/SQL:

a.更新统计信息

create or replace procedure upd_tab as
begin
   update TABLE_1 set Character_Col = 'XXXX';

   dbms_output ( .. print SQL%ROWCOUNT etc. );
end;

比较TRACE时，在trace的Execute部分发现了显着差异

When comparing the TRACE, the significant difference is found in the Execute part of trace

SQL:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute      1      2.19       2.40          0       1902     257114      200000

PL/SQL:

call     count       cpu    elapsed       disk      query    current        rows
Execute      1      2.13       5.94          0       1884     256912      200000

所以-我能理解的是，当PL/SQL向SQL发送语句时，发生了上下文切换.当将语句直接作为SQL执行时，则不涉及上下文切换.

So - what I can understand is that there is CONTEXT-SWITCHING taking place when PL/SQL sends statement to SQL. When statement is directly executed as SQL, then no context switching is involved.

看来我的最终用户正在使用游标一次将一个UPDATE语句从存储过程发送到数据库.对于十万多行，这几秒钟很快就累加了.

It seems my end-users are using cursors to send one UPDATE statement at a time to database from a stored procedure. And for 100,000+ rows, these few seconds are quickly adding up.

这让我想起了汤姆·凯特(Tom Kyte)的著名词(不完全是；希望有人可以找到链接)

It reminds me of Tom Kyte's famous words ( not exactly; hope somebody can find the link )

如果您想做某事，请用SQL进行，

if you want to do something, do it in SQL,

如果SQL不适合，则在PL/SQL中进行

if SQL is not suitable, then do it in PL/SQL,

如果PL/SQL不适合，则使用Java，

if PL/SQL is not suitable, then do it as Java,

如果Java不适合，则按照外部C过程进行操作，

if Java is not suitable, then do it as External C procedure,

如果外部C不适合，请考虑-为什么我们必须首先使用它.

if External C is not suitable, then think - why we have to do it in first place.

推荐答案

不同的提示，会话参数，资源限制，上下文，绑定变量，错误..最好是比较执行计划.

Different hints, session parameters, resurce limit, context, bind variables, bug.. Best is to compare execution plans.

这篇关于Oracle PL/SQL过程的运行速度比SQL慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！