Прискорення вставки

Том,

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


У мене є таблиці 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>

*

*