Oracle:批量收集性能 [英] Oracle: Bulk Collect performance

问题描述

您能帮我理解这句话吗?

没有批量绑定，PL/SQL将一条SQL语句发送到SQL引擎 对于每个插入，更新或删除的记录，导致 上下文切换会影响性能.

解决方案

在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;

然后每次执行提取操作时，我都会

• 执行从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万个上下文移位，这很可能占我的代码经过时间的一部分.另一方面，如果我执行BULK COLLECT且LIMIT为100，则每次我通过从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, col2, ... , colN )
        VALUES( l_arr(i).col1, l_arr(i).col2, ... , l_arr(i).colN );
  END LOOP;
END;

现在，每次执行提取操作时，我都会通过一组上下文移位将100行数据检索到我的集合中.每次执行FORALL插入操作时，我都会插入100条带有单组上下文转换的行.如果source_table具有100万行，这意味着我已经从400万个上下文转换变为40,000个上下文转换.例如，如果上下文转移占了我的代码使用时间的20％，那么我就消除了19.8％的使用时间.

您可以增加LIMIT的大小以进一步减少上下文转移的次数，但是很快就会遇到收益递减的规律.如果您使用的LIMIT为1000而不是100，则将消除99.9％的上下文偏移，而不是99％.但这意味着您的收藏使用的PGA内存增加了10倍以上.在我们的假设示例中，这只会减少0.18％的经过时间.您很快就会达到这样一个程度:正在使用的额外内存会通过消除额外的上下文转移而比您节省的时间更多.一般来说，LIMIT可能在100到1000之间.

当然，在此示例中，消除所有上下文转换并在单个SQL语句中执行所有操作仍然会更有效

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

首先，如果您要对源表中的数据进行某种形式的操作，而这些操作是您无法在SQL中合理实现的，那么首先请诉诸PL/SQL.

此外，我在示例中有意使用了显式游标.如果使用隐式游标，则在Oracle的最新版本中，您会得到BULK COLLECT的优点，其中LIMIT隐式为100.还有另一个StackOverflow问题，讨论相对的 解决方案

Within Oracle, there is a SQL virtual machine (VM) and a PL/SQL VM. When you need to move from one VM to the other VM, you incur the cost of a context shift. Individually, those context shifts are relatively quick, but when you're doing row-by-row processing, they can add up to account for a significant fraction of the time your code is spending. When you use bulk binds, you move multiple rows of data from one VM to the other with a single context shift, significantly reducing the number of context shifts, making your code faster.

Take, for example, an explicit cursor. If I write something like this

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;

then every time I execute the fetch, I am

• Performing a context shift from the PL/SQL VM to the SQL VM
• Asking the SQL VM to execute the cursor to generate the next row of data
• Performing another context shift from the SQL VM back to the PL/SQL VM to return my single row of data

And every time I insert a row, I'm doing the same thing. I am incurring the cost of a context shift to ship one row of data from the PL/SQL VM to the SQL VM, asking the SQL to execute the INSERT statement, and then incurring the cost of another context shift back to PL/SQL.

If source_table has 1 million rows, that's 4 million context shifts which will likely account for a reasonable fraction of the elapsed time of my code. If, on the other hand, I do a BULK COLLECT with a LIMIT of 100, I can eliminate 99% of my context shifts by retrieving 100 rows of data from the SQL VM into a collection in PL/SQL every time I incur the cost of a context shift and inserting 100 rows into the destination table every time I incur a context shift there.

If can rewrite my code to make use of bulk operations

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;

Now, every time I execute the fetch, I retrieve 100 rows of data into my collection with a single set of context shifts. And every time I do my FORALL insert, I am inserting 100 rows with a single set of context shifts. If source_table has 1 million rows, this means that I've gone from 4 million context shifts to 40,000 context shifts. If context shifts accounted for, say, 20% of the elapsed time of my code, I've eliminated 19.8% of the elapsed time.

You can increase the size of the LIMIT to further reduce the number of context shifts but you quickly hit the law of diminishing returns. If you used a LIMIT of 1000 rather than 100, you'd eliminate 99.9% of the context shifts rather than 99%. That would mean that your collection was using 10x more PGA memory, however. And it would only eliminate 0.18% more elapsed time in our hypothetical example. You very quickly reach a point where the additional memory you're using adds more time than you save by eliminating additional context shifts. In general, a LIMIT somewhere between 100 and 1000 is likely to be the sweet spot.

Of course, in this example, it would be more efficient still to eliminate all context shifts and do everything in a single SQL statement

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

It would only make sense to resort to PL/SQL in the first place if you're doing some sort of manipulation of the data from the source table that you can't reasonably implement in SQL.

Additionally, I used an explicit cursor in my example intentionally. If you are using implicit cursors, in recent versions of Oracle, you get the benefits of a BULK COLLECT with a LIMIT of 100 implicitly. There is another StackOverflow question that discusses the relative performance benefits of implicit and explicit cursors with bulk operations that goes into more detail about those particular wrinkles.

这篇关于Oracle:批量收集性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！