Повышение производительности при применении FORALL

Стивен Фернстайн

Источник: Рекомендации RevealNet (группа PL/SQL) за апрель 2001,(http://128.121.241.221/Pipelines/PLSQL/tips.htm)

Improving Performance with FORALL, by Steven Feuerstein

Благодарю Jeff Thomas (jsthomas@ixl.com)

Недавно я опробовал опцию "Bulk Binding" ("Массовое Связывание") в Oracle 8.1.6 и был поражен повышением производительности. Не прав будет тот разработчик, который захочет познакомиться с этой возможностью. Свой небольшой эксперимент я оформил в виде пакета и теперь представляю вам результат. Безусловно, ваши результаты будут другими, но отправная точка должна быть вполне понятна. Ниже показаны скрипты и выходные листинги как для обычного PL/SQL-программирования, так и для использования FORALL-метода.

SQL Script

set echo on
set feedback on
spool forall
 
/* 

Простой тест на время выполнения массового связывания при использовании FORALL. Покажем, что этот МЕТОД быстрее.

Для иллюстрации перегрузим 100,000 записей из plsql-таблицы в таблицу базы данных. Для этого имеются два способа, как показано ниже.

 DDL-предложение для таблицы примера:
 drop table test1;
 create table test1 (testnum number);
*/ 

Пример1:
Обычное применение PL/SQL без использования массового связываеия (BULK BIND)

set timing on
 
DECLARE
type t1_type is table of test1.testnum%type index by binary_integer;
t1_tab t1_type;
t1_idx binary_integer := 0;
 
BEGIN
 
 -- загрузка plsql-таблицы
FOR n in 1 .. 100000
LOOP
 t1_tab(n) := n;
 t1_idx := t1_idx + 1;
END LOOP;
 
 -- загрузка db-таблицы
FOR n in 1 .. t1_idx
LOOP
 insert into test1 (testnum) values (t1_tab(n));
END LOOP;
 
END;
/
 
set timing off
select count(*) from test1;
truncate table test1 reuse storage;
set timing on

Пример2:

Использование FORALL

DECLARE
type t1_type is table of test1.testnum%type index by binary_integer;
t1_tab t1_type;
t1_idx binary_integer := 0;
 
BEGIN
 
 -- загрузка plsql-таблицы
FOR n in 1 .. 100000
LOOP
 t1_tab(n) := n;
 t1_idx := t1_idx + 1;
END LOOP;
 
FORALL n in 1 .. t1_idx
 insert into test1 (testnum) values (t1_tab(n));
 
END;
/
 
set timing off
select count(*) from test1;
truncate table test1 reuse storage;
 
spool off

Выходной листинг

Пример1:

Обычное применение PL/SQL без использования массового связываеия (BULK BIND)

set timing on
 
DECLARE
 2 type t1_type is table of test1.testnum%type index by binary_integer;
 3 t1_tab t1_type;
 4 t1_idx binary_integer := 0;
 5 
 6 BEGIN
 7 
 8 -- load the plsql table
 9 FOR n in 1 .. 100000
 10 LOOP
 11 t1_tab(n) := n;
 12 t1_idx := t1_idx + 1;
 13 END LOOP;
 14 
 15 -- load the db table
 16 FOR n in 1 .. t1_idx
 17 LOOP
 18 insert into test1 (testnum) values (t1_tab(n));
 19 END LOOP;
 20 
 21 END;
 22 /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:25.97
 
set timing off
select count(*) from test1;
 
COUNT(*)
--------
 100000
 
1 row selected.
 
truncate table test1 reuse storage;
 
Table truncated.
 
set timing on

Пример2:

Использование FORALL

DECLARE
 2 type t1_type is table of test1.testnum%type index by binary_integer;
 3 t1_tab t1_type;
 4 t1_idx binary_integer := 0;
 5 
 6 BEGIN
 7 
 8 -- load the plsql table
 9 FOR n in 1 .. 100000
 10 LOOP
 11 t1_tab(n) := n;
 12 t1_idx := t1_idx + 1;
 13 END LOOP;
 14 
 15 FORALL n in 1 .. t1_idx
 16 insert into test1 (testnum) values (t1_tab(n));
 17 
 18 END;
 19 /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:01.73
 
set timing off
select count(*) from test1;
 
COUNT(*)
--------
 100000
 
1 row selected.
 
truncate table test1 reuse storage;
 
Table truncated.
 
spool off