Oracle: производительность массового сбора

В Oracle есть виртуальная машина SQL (ВМ) и виртуальная машина PL/SQL. Когда вам нужно перейти с одной виртуальной машины на другую, вы понесете затраты на смену контекста. По отдельности эти изменения контекста происходят относительно быстро, но когда вы выполняете построчную обработку, они могут в сумме составлять значительную долю времени, затрачиваемого вашим кодом. При использовании групповых привязок вы перемещаете несколько строк данных с одной виртуальной машины на другую с помощью одного сдвига контекста, что значительно сокращает количество смен контекста и ускоряет код.

Возьмем, к примеру, явный курсор. Если я напишу что-то вроде этого

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 имеет 1 миллион строк, это 4 миллиона сдвигов контекста, которые, вероятно, составят разумную часть затраченного времени моего кода. Если, с другой стороны, я выполняю 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;

Теперь каждый раз, когда я выполняю выборку, я извлекаю 100 строк данных в свою коллекцию с помощью одного набора сдвигов контекста. И каждый раз, когда я делаю вставку FORALL, я вставляю 100 строк с одним набором сдвигов контекста. Если source_table имеет 1 миллион строк, это означает, что я перешел от 4 миллионов сдвигов контекста к 40 000 сдвигов контекста. Если на смену контекста приходится, скажем, 20 % затраченного времени моего кода, я исключил 19,8 % затраченного времени.

Вы можете увеличить размер LIMIT, чтобы еще больше уменьшить количество сдвигов контекста, но вы быстро столкнетесь с законом убывающей отдачи. Если бы вы использовали LIMIT из 1000, а не 100, вы устранили бы 99,9% сдвигов контекста, а не 99%. Однако это означало бы, что ваша коллекция использовала в 10 раз больше памяти PGA. И в нашем гипотетическом примере это устранило бы только 0,18% дополнительного времени. Вы очень быстро достигаете точки, когда дополнительная память, которую вы используете, добавляет больше времени, чем вы экономите, устраняя дополнительные сдвиги контекста. В общем, LIMIT где-то между 100 и 1000, вероятно, будет оптимальным.

Конечно, в этом примере было бы еще эффективнее исключить все сдвиги контекста и сделать все в одном SQL-запросе.

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

В первую очередь имеет смысл прибегать к PL/SQL, если вы выполняете какие-то манипуляции с данными из исходной таблицы, которые вы не можете разумно реализовать в SQL.

Кроме того, я намеренно использовал явный курсор в своем примере. Если вы используете неявные курсоры, в последних версиях Oracle вы неявно получаете преимущества BULK COLLECT с LIMIT из 100. Есть еще один вопрос StackOverflow, в котором обсуждается относительный преимущества неявных и явных курсоров с массовыми операциями, где более подробно описаны эти особенности.

Насколько я понимаю, задействовано два механизма: движок PL/SQL и механизм SQL< /а>. Выполнение запроса, использующего один движок за раз, более эффективно, чем переключение между двумя

  INSERT INTO t VALUES(1)

  FOR Lcntr IN 1..20

  END LOOP

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