匿名PL/SQL块相对于原生SQL的性能

Question

我有一个SQL语句，它在PL/SQL块中执行时提供了非常不同的性能。

SQL非常简单。

INSERT into Existing_Table SELECT col1,col2... from Other_table where rownum < 100000

当它作为SQL执行时，它几乎立即返回。

但是，在匿名PL/SQL块中执行时，它将永远挂起：

begin
    INSERT into Existing_Table SELECT col1, col2... from Other_table where rownum < 100000;
end;
/

Answer 1

但是，在匿名PL/SQL块中执行时，它将永远挂起：

我猜有两件事：

1. 您的表(Existing_Table)对您在insert语句中使用的一个列有一个约束。
2. 您忘记在执行SQL语句和PL/SQL匿名块之间发出commit。

在SQL和PL/SQL中执行语句不应该有性能上的差异。它应该在差不多相同的时间内执行。但是由于约束或提交，它被阻塞了，因为行被锁定了。

下面是一个例子。

在第1节中，创建两个表。一个有约束，另一个没有：

create table Existing_Table 
(
  existing_column number primary key
);

create table Existing_Table_2
(
  existing_column number
);    

在同一会话上，执行以下SQL语句：

insert into Existing_Table (existing_column) values (1);

结果：

1 row inserted.

在另一个(会话2)上，执行以下PL/SQL匿名块：

begin
  insert into Existing_Table (existing_column) values (1);
end;

这将挂起，直到您在会话1中发出提交。

这是因为会话1为existing_column“保留”了"1“的值，并且在发出提交时将被”保存“。会话2只是等待会话1提交或回滚插入。

现在，当我回到会话1并发出提交时，行将被解锁。但是，由于完整性约束违规，会话2将导致错误：

Error starting at line : 1 in command -
begin
  insert into Existing_Table (existing_column) values (1);
end;
Error report -
ORA-00001: unique constraint (APPS.SYS_C001730810) violated
ORA-06512: at line 2
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

现在，另一个没有约束的表示例。

在没有提交的情况下运行会话3中的以下SQL：

insert into Existing_Table_2 (existing_column) values (1);

结果：

1 row inserted.

在会话4中的匿名PL/SQL块中运行相同的SQL：

begin
  insert into Existing_Table_2 (existing_column) values (1);
end;

结果：

PL/SQL procedure successfully completed.

即使在会话1中没有提交，它也插入得很好，因为没有违反任何约束。

请注意，在发出提交之前，会话3和4中的任何数据都不会实际保存在数据库中。

请查看有关会话阻塞的其他文章：

跟踪Oracle阻塞会话

查找阻塞会话

Answer 2

我试图重现这个问题，但没有成功。正如其他人正确指出的那样，SQL足够简单，执行SQL或anon /SQL都不应该造成这种差异。

脑海中唯一想到的是，我可能没有注意到是否有另一个会话在没有提交/回滚的情况下尝试DML；这可能导致挂起。因此，BobC和Migs提到的场景在这里可能是相关的。谢谢大家。