在Oracle中,有一个sql虚拟机(VM)和一个PL / sql VM.当您需要从一个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;

那么每次我执行fetch,我都是

>执行从PL / sql VM到sql VM的上下文切换
>要求sql VM执行游标以生成下一行数据
>执行从sql VM返回到PL / sql VM的另一个上下文切换以返回我的单行数据

每次我插入一行,我都在做同样的事情.我承担了将一行数据从PL / sql VM发送到sql VM的上下文转换的成本,要求sql执行INSERT语句,然后将另一个上下文转换的成本转回PL / sql.

如果source_table有100万行,那就是400万个上下文移位,这可能占我代码经过时间的合理分数.另一方面,如果我使用LIMIT为100进行BULK COLLECT,那么每次产生成本时,我可以通过从sql VM中检索100行数据到PL / sql集合中来消除99％的上下文转换每次我在那里进行上下文转换时,上下文转换并将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,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的大小以进一步减少上下文转换的数量,但您很快就会达到收益递减规律.如果您使用的是LIMIT 1000而不是100,那么您将消除99.9％的上下文转换而不是99％.这意味着你的系列使用了10倍以上的PGA内存.在我们的假设示例中,它只会消耗0.18％的经过时间.通过消除额外的上下文转换,您很快就可以达到使用额外内存所节省的时间.一般来说,在100到1000之间的极限可能是最佳点.

当然,在这个例子中,消除所有上下文转换并在单个sql语句中执行所有操作仍然会更有效

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

如果您正在对源表中的数据进行某种操作(在sql中无法合理地实现),那么首先使用PL / sql是有意义的.

另外,我故意在我的例子中使用了一个显式游标.如果您使用隐式游标,则在最新版本的Oracle中,您可以隐式地获得LIMIT为100的BULK COLLECT的好处.还有另一个StackOverflow问题讨论了关于那些特定皱纹的更详细的相关performance benefits of implicit and explicit cursors with bulk operations.