Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Август 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : ускорение вставки


Информационный Канал Subscribe.Ru

Выпуск 43

Уважаемые подписчики рассылки!

Этот выпуск посвящен различным способам множественной вставки данных, которые выбираются из другой таблицы. По мотивам ответа Тома Кайта на вопросы посетителей его замечательного сайта.

Ускорение вставки

Том,

Я бы хотел знать, как лучше всего выполнить следующие вставки.

У меня есть таблицы t1, t2, t3 и x1, x2 и x3:
В таблице t1 - примерно 400000 строк
В таблице t2 - примерно 1000000 строк
В таблице t3 - примерно 200000 строк

Таблицы x1, x2, x3 первоначально пусты - данные добавляются в них каждый месяц. Сейчас для добавления данных я использую 3 вложенных цикла FOR. Основным является внешний цикл, в котором выбираются все записи из таблицы t1 и вставляются в x1. Первичный ключ таблицы x1 берется из последовательности с помощью триггера, срабатывающего перед вставкой. Значение последовательности возвращается в переменную с помощью конструкции returning into.

Следующий вложенный цикл выбирает все записи из таблицы t2, у которых значение в столбце id соответствует значению id (первичному ключу) из x1, и вставляет их в таблицу x2 (снова генерируя первичный ключ в триггере и возвращая его в переменную). Значение последовательности из предыдущего цикла вставляется в x2, чтобы обеспечить связь между x1 и x2.

Последний цикл во многом аналогичен - в таблицу x3 вставляются все записи из t3, у которых значение в столбце id соответствует значению id (первичному ключу) из x2, а значение последовательности из предыдущего цикла вставляется в x3, создавая связь между x2 и x3.

Итак, используется следующий код:

 
cursor c1 is select * from t1; 
cursor c2(v_id1 in number) is select * from t2 where t2.id = v_id1; 
cursor c3(v_id2 in number) is select * from t3 where t3.id = v_id2; 
for rec1 in c1  
loop 
  insert into x1 (col1, col2, col3) values (null, rec1.a, rec1,b)  
    returning col1 into v_new_id; 
  
  for rec2 in c2(rec1.a) 
  loop 
    insert into x2 (col1, col2, col3) values (null, v_new_id, rec2.a)  
    returning col1 into v_new_id2; 
  
    for rec3 in c3(rec2.a) 
    loop 
      insert into x3 (col1, col2, col3) values (null, v_new_id2, rec3.a); 
    end loop; 
    
  end loop; 
  
end loop;

Не будет ли лучше использовать массивы и множественные вставки?

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

Нет ли способа получше...

Верите или нет, но лучше всего будет использовать просто три оператора insert. Вы получите потрясающее ускорение. И ресурсов потребуется намного меньше. Я создам три таблицы t1, t2, t3 следующим образом:

 
ops$tkyte@ORA920> create table t1( id int, data char(20) ); 
 
Table created. 
 
ops$tkyte@ORA920> create table t2( id int, fk_t1 int, data char(20) ); 
 
Table created. 
 
ops$tkyte@ORA920> create table t3( id int, fk_t2 int, data char(20) ); 
 
Table created. 
 
ops$tkyte@ORA920> insert into t1 select rownum, 'x' from big_table.big_table where rownum <= 400000; 
 
400000 rows created. 
 
ops$tkyte@ORA920> insert into t2 
  2  select rownum, id, 'x' 
  3    from ( select id from t1 
  4            union all 
  5           select id from t1 
  6            union all 
  7           select id from t1 where mod(id,2) = 0 ); 
 
1000000 rows created. 
 
ops$tkyte@ORA920> insert into t3 
  2  select rownum, id, 'x' 
  3    from (select id from t2 where mod(id,5) = 0); 
 
200000 rows created. 
 
ops$tkyte@ORA920> create index t2_fk_idx on t2(fk_t1); 
 
Index created. 
 
ops$tkyte@ORA920> create index t3_fk_idx on t3(fk_t2); 
 
Index created. 
 
ops$tkyte@ORA920> analyze table t1 compute statistics for table for all indexes for all indexed columns; 
 
Table analyzed. 
 
ops$tkyte@ORA920> analyze table t2 compute statistics for table for all indexes for all indexed columns; 
 
Table analyzed. 
 
ops$tkyte@ORA920> analyze table t3 compute statistics for table for all indexes for all indexed columns; 
 
Table analyzed.

Затем я содаю ваши таблицы x1, x2, x3:

 
ops$tkyte@ORA920> create table x1a ( newid int, oldid int, data char(20) ); 
 
Table created. 
 
ops$tkyte@ORA920> create table x2a ( newid int, oldid int, fk_t1 int, data char(20) ); 
 
Table created. 
 
ops$tkyte@ORA920> create table x3a ( newid int, oldid int, fk_t2 int, data char(20) ); 
 
Table created. 
 
ops$tkyte@ORA920> create index x1a_idx on x1a(newid,oldid); 
 
Index created. 
 
ops$tkyte@ORA920> create index x2a_idx on x2a(newid,oldid); 
 
Index created.

Я создал таблицы x1 и x1a, x2, x2a и т.д. - таблицы x1a, x2a... отличаются наличием индексов, что позволяет продемонстрировать мой подход к решению этой задачи.

По таблицам x1, x2, x3 я создаю триггеры:

 
ops$tkyte@ORA920> create or replace trigger x1_trigger 
  2  before insert on x1 for each row 
  3  begin 
  4     select s.nextval into :new.newid from dual; 
  5  end; 
  6  / 
 
Trigger created.

Хотя использование триггеров и является самым худшим способом... Вот как я бы загружал данные в таблицы x1, x2, x3:

 
ops$tkyte@ORA920> exec runstats_pkg.rs_start 
 
PL/SQL procedure successfully completed. 
 
ops$tkyte@ORA920> alter sequence S cache 1000000; 
 
Sequence altered.

Подобный оператор alter sequence необходимо выполнять перед интенсивным использованием последовательности, - вас удивит, НАСКОЛЬКО это повысит производительность.

 
ops$tkyte@ORA920> column S new_val S; 
ops$tkyte@ORA920> select s.nextval S from dual; 
 
         S 
---------- 
         1 
 
ops$tkyte@ORA920> insert /*+ APPEND */ into x1a 
  2  select s.nextval, id, data from t1; 
 
400000 rows created. 
 
ops$tkyte@ORA920> commit; 
 
Commit complete. 
 
ops$tkyte@ORA920> insert /*+ APPEND */ into x2a 
  2  select s.nextval, t2.id, x1.newid, t2.data 
  3    from t2, x1a x1 
  4   where x1.newid > &S 
  5     and x1.oldid = t2.fk_t1; 
old   4:  where x1.newid > &S 
new   4:  where x1.newid >          1 
 
1000000 rows created. 
 
ops$tkyte@ORA920> commit; 
 
Commit complete. 
 
ops$tkyte@ORA920> insert /*+ APPEND */ into x3a 
  2  select s.nextval, t3.id, x2.newid, t3.data 
  3    from t3, x2a x2 
  4   where x2.newid > &S 
old   4:  where x2.newid > &S 
new   4:  where x2.newid >          1 
 
200000 rows created. 
 
ops$tkyte@ORA920> commit; 
 
Commit complete. 
 
ops$tkyte@ORA920> alter sequence S cache 20; 
 
Sequence altered. 
 
ops$tkyte@ORA920> exec runstats_pkg.rs_middle; 
 
PL/SQL procedure successfully completed.

Вот и весь процесс загрузки -- эти операторы делают все необходимое. Теперь ваш способ:

 
ops$tkyte@ORA920> declare 
  2      l_newid_t1 int; 
  3      l_newid_t2 int; 
  4  begin 
  5      for x in ( select * from t1 ) 
  6      loop 
  7          insert into x1 ( oldid, data ) values ( x.id, x.data ) 
  8          returning newid into l_newid_t1; 
  9          for y in ( select * from t2 where t2.fk_t1 = x.id ) 
 10          loop 
 11          insert into x2 ( oldid, fk_t1, data ) values ( y.id, l_newid_t1, y.data ) 
 12          return newid into l_newid_t2; 
 13          for z in ( select * from t3 where t3.fk_t2 = y.id ) 
 14          loop 
 15              insert into x3 ( oldid, fk_t2, data ) values ( z.id, l_newid_t2, z.data ); 
 16          end loop; 
 17          end loop; 
 18      end loop; 
 19  end; 
 20  / 
 
PL/SQL procedure successfully completed. 
 
ops$tkyte@ORA920> exec runstats_pkg.rs_stop(100000); 
 
Run1 ran in 12183 hsecs 
Run2 ran in 235576 hsecs 
run 1 ran in 5.17% of the time

Мой вариант работает почти в 20 раз быстрее (причем приходится поддерживать два дополнительных индекса!). Но, что еще важнее:

 
Name                                  Run1        Run2        Diff 
LATCH.checkpoint queue latch        36,430     141,104     104,674 
LATCH.row cache enqueue latch        3,846     163,180     159,334 
LATCH.dml lock allocation              370     160,329     159,959 
LATCH.sequence cache             4,800,009   4,960,014     160,005 
STAT...recursive cpu usage              63     199,915     199,852 
STAT...CPU used by this sessio       5,509     217,812     212,303 
STAT...CPU used when call star       5,509     217,812     212,303 
STAT...Elapsed Time                 12,270     235,598     223,328 
LATCH.undo global data              19,681     253,812     234,131 
STAT...enqueue releases              5,151     242,495     237,344 
STAT...enqueue requests              5,151     242,499     237,348 
LATCH.library cache pin alloca       2,989     323,616     320,627 
LATCH.enqueue hash chains           10,545     487,183     476,638 
LATCH.row cache objects              5,286     484,151     478,865 
STAT...sorts (rows)              1,402,631       2,618  -1,400,013 
STAT...consistent gets - exami     407,293   1,890,245   1,482,952 
STAT...table scan rows gotten      400,001   2,000,000   1,599,999 
STAT...table scans (short tabl           1   1,600,000   1,599,999 
STAT...table scan blocks gotte       1,757   2,000,048   1,998,291 
STAT...redo entries                 52,903   2,215,021   2,162,118 
LATCH.redo allocation               53,998   2,222,013   2,168,015 
LATCH.session allocation                31   2,401,734   2,401,703 
STAT...buffer is pinned count    2,600,058           0  -2,600,058 
STAT...db block gets               102,375   3,161,828   3,059,453 
STAT...no work - consistent re   1,207,558   4,402,253   3,194,695 
STAT...db block changes             99,734   4,401,587   4,301,853 
STAT...buffer is not pinned co   1,202,995   5,598,153   4,395,158 
STAT...execute count                   502   4,680,368   4,679,866 
STAT...recursive calls               5,033   7,323,453   7,318,420 
STAT...consistent gets           3,022,880  10,894,618   7,871,738 
STAT...calls to get snapshot s      12,743   8,043,676   8,030,933 
LATCH.shared pool                1,604,008   9,805,723   8,201,715 
STAT...session logical reads     3,125,255  14,056,446  10,931,191 
LATCH.library cache pin          3,204,928  16,245,960  13,041,032 
LATCH.library cache              4,807,752  19,774,939  14,967,187 
LATCH.cache buffers chains       6,112,094  33,752,275  27,640,181 
STAT...redo size               127,584,104 606,190,928 478,606,824

Я просто показываю, что по некоторым показателям разница огромна. Посмотрите на значение REDO SIZE, посмотрите, насколько меньше требуется внутренних блокировок (показатели LATCH)! Это принципиальное отличие...

 
Run1 latches total versus runs -- difference and pct 
Run1        Run2        Diff       Pct 
20,739,779  91,534,813  70,795,034     22.66% 
 
PL/SQL procedure successfully completed.

Просто используйте 3 оператора insert, и все. Когда приходится писать процедурный код, остановитесь и задайте себе вопрос: "Зачем я это делаю?"

Вопросы читателя от 13 июня 2003 года

Том,

У меня возникли два вопроса относительно твоих операторов insert:

 
ops$tkyte@ORA920> insert /*+ APPEND */ into x2a 
  2  select s.nextval, t2.id, x1.newid, t2.data 
  3    from t2, x1a x1 
  4   where x1.newid > &S 
  5     and x1.oldid = t2.fk_t1; 
old   4:  where x1.newid > &S 
new   4:  where x1.newid >          1 
 
1000000 rows created. 
 
ops$tkyte@ORA920> commit; 
 
Commit complete. 
 
ops$tkyte@ORA920> insert /*+ APPEND */ into x3a 
  2  select s.nextval, t3.id, x2.newid, t3.data 
  3    from t3, x2a x2 
  4   where x2.newid > &S 
old   4:  where x2.newid > &S 
new   4:  where x2.newid

Вопрос 1: Зачем нужна конструкция "x1.newid > &S" в конструкции where при вставке в таблицу x2a?

Вопрос 2: Почему конструкция where "x2.oldid = t3.fk_t2;" отсутствует при вставке в таблицу x3a? Аналогичное условие, "x1.oldid = t2.fk_t1;", указано при вставке в таблицу x2a?

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

Вопрос 1: Я предположил, что эта таблица X1 накапливается со временем и, поскольку таблица X1 будет содержать данные за последний месяц, а также данные за текущий месяц, значение x1.oldid будет дублироваться.

Добавление условия по x1.newid ограничивает просмотр таблицы X1 только вновь добавленными данными.

Вопрос 2: Вы очень внимательны.

Это была ошибка копирования (неверно выбрана граница блока :). Правильно будет так:

 
ops$tkyte@ORA920> insert /*+ APPEND */ into x3a 
  2  select s.nextval, t3.id, x2.newid, t3.data 
  3    from t3, x2a x2 
  4   where x2.newid > &S 
  5     and x2.oldid = t3.fk_t2; 
old   4:  where x2.newid > &S 
new   4:  where x2.newid >          1 
 
200000 rows created.

Обратите внимание, что в исходном тексте нет ни ';', ни '/' -- я потерял последнюю строку.

Прекрасный пример

Том,

Ты используешь в своем примере непосредственную вставку?

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

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

Да, да и еще раз - да.

Комментарий читателя от 15 июня 2003 года

Кажется, я чего-то не понимаю! Если это действие необходимо выполнить в пакете, что придется изменить в твоем примере - я не понимаю, что это значит &S в конструкции where?

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

&S - значение последовательности перед началом множественной вставки. В PL/SQL-процедуре это может выглядеть так:

 
  select s.nextval into l_lo_seq from dual; 
  insert /*+ APPEND */ into x2a 
  select s.nextval, t2.id, x1.newid, t2.data 
    from t2, x1a x1 
   where x1.newid > L_LO_SEQ 
     and x1.oldid = t2.fk_t1;

А что это за пакет runstats_pkg?

Нельзя ли получить исходный код пакета runstats_pkg?

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

Ищите ссылку на простой набор для тестирования (simple test harness) на этой странице.

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2003 Oracle Corporation

В следующем выпуске

Пример использования средств Fine Grained Access Control (FGAC). Следуем опубликованной программе развития рассылки.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное