Oracle:批量收集性能

Question

你能帮我理解这句话吗？

如果没有大容量绑定，PL/SQL将为插入、更新或删除的每个记录向SQL引擎发送SQL语句，从而导致影响性能的上下文切换。

Answer 1

在Oracle中，有一个SQL虚拟机( VM )和一个PL/SQL。当您需要从一个VM迁移到另一个VM时，您需要支付上下文转移的成本。单独而言，这些上下文转移相对较快，但是当您进行逐行处理时，它们可以占代码花费时间的很大一部分。当您使用大容量绑定时，使用一个上下文移位将多行数据从一个VM移动到另一个VM，显着地减少了上下文移位的数量，从而使您的代码更快。

例如，以显式游标为例。如果我写这样的东西

DECLARE
  CURSOR c 
      IS SELECT *
           FROM source_table;
  l_rec source_table%rowtype;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO l_rec;
    EXIT WHEN c%notfound;

    INSERT INTO dest_table( col1, col2, ... , colN )
      VALUES( l_rec.col1, l_rec.col2, ... , l_rec.colN );
  END LOOP;
END;

然后每次我执行抓取，我就

• 执行从PL/SQL到SQL的上下文转移
• 请求SQL执行游标以生成下一行数据。
• 执行从SQL返回到PL/SQL的另一个上下文转移，以返回我的单个数据行

每次我插入一行时，我都在做同样的事情。我要承担上下文转移的成本，将一行数据从PL/SQL传送到SQL，要求SQL执行INSERT语句，然后将另一个上下文转移的成本返回到PL/SQL。

如果source_table有100万行，那么就会有400万个上下文移位，这很可能占我代码运行时间的合理比例。另一方面，如果我使用的BULK COLLECT为LIMIT为100，则可以消除99%的上下文移位，方法是每次发生上下文移位时，将100行数据从SQL检索到PL/SQL中的集合，并将100行插入到目标表中。

如果可以重写我的代码来使用大容量操作

DECLARE
  CURSOR c 
      IS SELECT *
           FROM source_table;
  TYPE  nt_type IS TABLE OF source_table%rowtype;
  l_arr nt_type;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_arr LIMIT 100;
    EXIT WHEN l_arr.count = 0;

    FORALL i IN 1 .. l_arr.count
      INSERT INTO dest_table( col1, col2, ... , colN )
        VALUES( l_arr(i).col1, l_arr(i).col2, ... , l_arr(i).colN );
  END LOOP;
END;

现在，每次执行fetch时，我都使用一组上下文转移将100行数据检索到我的集合中。而且，每次执行FORALL插入时，我都要插入100行，其中包含一组上下文移位。如果source_table有100万行，这意味着我已经从400万次上下文转移到了40,000次上下文转移。如果上下文转移占代码运行时间的20%，那么我已经消除了19.8%的运行时间。

您可以增加LIMIT的大小以进一步减少上下文转移的数量，但是您很快就达到了收益递减的规律。如果您使用的是1000而不是100的LIMIT，那么您将消除99.9%的上下文移位，而不是99%。这意味着您的集合将使用更多10倍的PGA内存。在我们假设的例子中，它只会减少0.18%的运行时间。通过消除额外的上下文转移，您很快就可以使用额外的内存来增加比节省的时间更多的时间。一般来说，一个介于100到1000之间的LIMIT很可能是最佳选择。

当然，在本例中，消除所有上下文转移并在单个SQL语句中执行所有操作将更加有效。

INSERT INTO dest_table( col1, col2, ... , colN )
  SELECT col1, col2, ... , colN
    FROM source_table;

如果您要对源表中的数据进行某种操作，而您不能在SQL中合理地实现这些操作，那么首先诉诸PL/SQL才有意义。

此外，我故意在示例中使用显式游标。如果使用隐式游标，那么在最近版本的甲骨文中，您将得到BULK COLLECT的好处，其LIMIT值为100。还有另一个StackOverflow问题，它讨论的是相对的具有大容量操作的隐式和显式游标的性能优势，它更详细地讨论了这些特定的皱纹。

Answer 2

据我所知，其中涉及两个引擎，PL/SQL引擎和SQL引擎。执行一次使用一个引擎的查询比在两者之间切换更有效。

示例：

  INSERT INTO t VALUES(1)

由SQL引擎处理，而

  FOR Lcntr IN 1..20

  END LOOP

由PL/SQL引擎执行。

如果将上述两条语句组合在一起，将INSERT放入循环，

FOR Lcntr IN 1..20
  INSERT INTO t VALUES(1)
END LOOP

Oracle将在两个引擎之间切换，用于每个(20)次迭代。在本例中，建议在执行过程中使用PL/SQL引擎的大容量插入。