UPDATE: сравнение производительности

Как повысить производительность UPDATE  - этот вопрос возникает очень часто, особенно в ETL-разработке.

Наиболее частые случаи массовых обновлений:

- обновление каждой строки в таблице;

- обновление небольшой части строк в очень большой таблице.

В первом случае самый быстрый способ - перестроить таблицу с нуля. Остальные методы будут работать хуже. 

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

Создадим таблицы и наполним их данными.

Условия создания - наличие primary key. 

Первая таблица будет содержать меньшее количество строк, из нее, как из источника, будут браться значения для второй таблицы с большим количеством данных.

Создаем первую таблицу:

create table stage_src
(id number generated always as identity,
code number,
name varchar2(10));

alter table stage_src add constraint pk_srcid primary key (id);

Наполним данными:

begin

insert into stage_src
(code, name)
 
select 12 + level as code, 'a'||(1 + level) as name
from dual
connect by level <= 100000;
commit;
 
end;

Создаем вторую таблицу:

create table stage_trg
(id number generated always as identity,
code number,
name varchar2(10));

 

alter table stage_trg add constraint pk_trgid primary key (id);

Наполним данными:

begin
insert into stage_trg
(code, name)

select 12 + level as code, 'a'||(1 + level) as name
from dual
connect by level <= 1000000;
commit;
 
end;

Применяемые методы:

• Explicit Cursor Loop (Явный курсор в цикле)
• Implicit Cursor Loop (Неявный курсор в цикле)
• UPDATE with nested SET subquery (Обновление с вложенным подзапросом)
• Updateable Join View
• BULK COLLECT / FORALL UPDATE
• MERGE (Слияние)
• Parallel DML MERGE 
• Parallel PL/SQL

💧 Explicit Cursor Loop

 

declare
cursor stg is
select * from stage_src;
 
rec_stg stg%rowtype;
 
begin
 
  open stg;
  loop
 
    fetch stg into rec_stg;
    exit when stg%notfound;
   
    update stage_trg
    set code = rec_stg.code,
        name = replace (rec_stg.name, 'a', 'b')
    where id = rec_stg.id;
   
    end loop;
   
    close stg;
 
end;

Время выполнения: 5.165 sec

 

💧 Implicit Cursor Loop 

 

begin

  for rec_stg in (select * from stage_src)

   

  loop

   

     update stage_trg

    set code = rec_stg.code,

        name = replace (rec_stg.name, 'a', 'b')

    where id = rec_stg.id;

   

    end loop;

   

end;

Время выполнения: 4.459 sec

Приведенное время обоих медодов - это время повторных запусков. Время же первого запуска отличается еще больше: явный курсор - 8.163 sec , неявный курсор - 5,334 sec.

Здесь подтверждается практический вывод по работе с этими типами курсоров: неявный курсор всегда будет эффективнее.

 

Почему так?

Отвечает Том Кайт

".. PL/SQL, как и Java, интерпретируется. PL/SQL VM(virtual machine), запускающая PL/SQL, написана на С. Чем больше кода - тем больше интерпретировать. Интерпретированный код  работает медленнее, чем собственный код (объектный/двоичный код). Таким образом, у виртуальной машины гораздо больше работы, больше CPU циклов, меньше выполнения напрямую на С - следовательно, меньше производительность..."

Здесь то самое переключение контекста между PL/SQL и SQL для каждого FETCH в цикле.

Подробнее о механизме переключения можно прочитать  в этой статье.

И прямая цитата Кайта, которую хочется вынести в эпиграф:

 It just goes right along with my mantra:

• if you can do it in a single sql statement - do so.
• if you cannot, do it in as little PLSQL as you can.
• if you cannot, because plsql cannot do it, try some java (eg: sending an email with an attachment)
• if you cannot do it in java (too slow, or you have some existing 3gl code already) do it in C as an extproc.

Еще один вывод следует из времени выполнения без создания primary key в таблицах.

Метод	Время 1-го запуска, sec	Время 2-го запуска, sec
Explicit Cursor Loop	2230.454	1727.143
Implicit Cursor Loop	1470.823	1466.088

Разница во времени при первом запуске подтверждает затраты на переключение контекста. Второй запуск уже дает разницу, которой можно пренебречь. 

А вот время выполнения по отношению к времени выполнения методов с таблицами, где объединение идет по primary key, то есть по индексированному полю -  катастрофически увеличилось.

Поэтому для использования этих методов объединение таблиц должно быть по индексируемому полю. 

 

А если забежать вперед, то предварительное заключение - на данный момент вообще нет причин использовать для DML-операций эти методы.

 

💧UPDATE with nested SET subquery (Обновление с вложенным подзапросом)

 

update stage_trg trg

set (code, name)  = (select src.code, replace (src.name, 'a', 'b')

                       from stage_src src

                      where trg.id = src.id)

where trg.id in (select id from stage_src);

Время выполнения: 2.315 sec

Метод имеет право на жизнь при наличии индексов и собранной статистики. Применим на небольших объемах. И если в запросе есть коррелирующий подзапрос -  в плане, возможно, будет объединение NESTED LOOPS.

💧 Updateable Join View

update (select src.code as s_code,

               replace(src.name, 'a', 'b') as s_name,

               trg.*

          from stage_trg trg

          join stage_src src on trg.id = src.id)

   set code = s_code,

       name = s_name;

 

Время выполнения: 3.234 sec

До-MERGE-вый метод. Встретите в legacy - не пугайтесь, работает при наличии индексов и статистики.

💧 BULK COLLECT / FORALL UPDATE

declare

 

type t_tab_number is table of number;

type t_tab_varchar is table of varchar2(10);

tb_id t_tab_number;

tb_code t_tab_number;

tb_varchar t_tab_varchar;

 

begin 

 

    select src.id, src.code, src.name

    bulk collect

    into tb_id, tb_code, tb_varchar

    from stage_src src;

 

  forall i in tb_id.first .. tb_id.last

      update stage_trg

        set code = tb_code(i),

            name = replace (tb_varchar(i), 'a', 'b')

        where id = tb_id(i);

       

end;

 

Время выполнения: 1.434 sec

Причина такого быстрого обновления в том, что BULK COLLECT уменьшает переключение контекста между SQL и механизмом PL/SQL и позволяет механизму SQL получать записи сразу. 

Тот же тест, но с курсором.

declare

 

cursor stg is

select * from stage_src;

 

type t_tab_number is table of number;

type t_tab_varchar is table of varchar2(10);

tb_id t_tab_number;

tb_code t_tab_number;

tb_varchar t_tab_varchar;

 

begin

 

  open stg;

 

  loop

    fetch stg

    bulk collect

    into tb_id, tb_code, tb_varchar;

   

    exit when tb_id.count() = 0;

   

  forall i in tb_id.first .. tb_id.last

      update stage_trg

        set code = tb_code(i),

            name = replace (tb_varchar(i), 'a', 'b')

        where id = tb_id(i);

       

    end loop;

   

    close stg;

 

end; 

 

Если использовать курсор (добавить переключение контекста) - время выполнения будет 1,491. Разница небольшая, но она будет расти на более объемных данных.

Подробнее о BULK COLLECT  здесь.

💧 MERGE 

 

merge into stage_trg trg

using stage_src src

   on (trg.id = src.id)

 when matched

   then update

     set trg.code = src.code,

         trg.name = src.name;

 

Время выполнения: 1.284 sec 

Подробнее o MERGE  здесь.

💧 Parallel DML MERGE 

begin

execute immediate 'ALTER SESSION ENABLE PARALLEL DML';

 

merge /*+ parallel(trg) parallel(src) */

 into stage_trg trg

using stage_src src

   on (trg.id = src.id)

 when matched

   then update

     set trg.code = src.code,

         trg.name = src.name;        

end;

 

Время выполнения: 0.186 sec

В случае MERGE необходимо распараллеливать и целевую таблицу, и таблицу-источник.

Базе данных проще распараллеливать большие операторы на основе наборов, чем построчный императивный код.

💧 Parallel PL/SQL

create or replace noneditionable function test_parallel_update (test_cur in sys_refcursor)

 

 return test_num_arr

 

   parallel_enable (partition test_cur by any) pipelined

 

 is

 

  pragma autonomous_transaction;

 

   type num_tab_t is table of number(38);

   type vc2_tab_t is table of varchar2(4000);

   id_tab num_tab_t;

   code_tab num_tab_t;

   name_tab vc2_tab_t;

   cnt integer := 0;

 

begin

 

   loop

 

     fetch test_cur bulk collect

     into id_tab, code_tab, name_tab limit 1000;

       exit when id_tab.count() = 0;

 

    forall i in id_tab.first .. id_tab.last

    update stage_trg t

    set    t.code = code_tab(i)       ,

           t.name = name_tab(i)

    where  id = id_tab(i);

 

    cnt := cnt + id_tab.count;

 

    end loop;

 

   close test_cur;

 

   commit;

 

   pipe row(cnt);

 

   return;

 

end;

 

Обратите внимание, что данные передаются в функцию через Ref Cursor. Это особенность параллельных функций Oracle; они будут распределять строки одного Ref Cursor-а между параллельными потоками, при этом каждый поток будет работать с другим подмножеством входного набора данных.

Выполним запрос с использованием табличной функции.

select /*+ parallel */ sum(column_value)

from table(test_parallel_update(cursor(select * from stage_src)));

Время выполнения: 0.195 sec.

Выполнять таким образом обновление через запрос - мягко говоря, не очень хорошо, но это ближайший эквивалент в PL/SQL того, что применяется в ETL-инструментах (third-party ETL Tool  типа DataStage). И чтобы избежать защелок в функции нужно указать AUTONOMOUS TRANSACTION.  

 

TL;DR

MERGE выиграл.  Но сбрасывать со счетов 8 метод тоже не стоит.

 

И еще несколько выводов:

Переключение контекста влияет на производительность.

MERGE эффективнее всего работает с Hash Join.

Расспараллеливать большие объединения нужно.

Внешние ключи не будут особенно влиять на производительность.

Если обновляем таблицу с Bitmap-индексами - лучше всего вставьте транзакции в глобальную временную таблицу и примените MERGE (примера в статье нет, на практике почти всегда будут B-Tree, но если столкнетесь - примите к сведению).

 

И небольшой манифест с учетом опыта в 12с:

- хватит использовать явные курсоры,

- если можно, не вызывайте PL/SQL функции в SQL,

- если можно от чего-то отказаться в пользу SQL - do it!

- не писать своего, если это есть в пакетах Oracle (в частности dbms..), не плодить сущности в БД, пользоваться уже написанным, особенно на промышленных БД,

- отключайте индексы, если сканируете по ROWID и видите скан других индексов в плане.

Спасибо за внимание! Надеюсь, пригодится.

 

Источники:

SQL Statements - Oracle Docs

8 Bulk Update Methods Compared

What is the reason that implicit cursors are faster than explicit cursors- asktom.oracle.com