Прискорення вставки, Інші СУБД, Бази даних, статті

Том,

Я б хотів знати, як найкраще виконати наступні вставки.


У мене є таблиці 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: Чому конструкція wherex2.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) на цій сторінці (http://asktom.oracle.com/ ~ tkyte /).

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*