Продуктивність оператора UPDATE

Ця стаття присвячена обговоренню продуктивності досить складного оператора UPDATE з вкладеними. За мотивами чергового відповіді Тома Кайта на запитання, поставлене у червні 2003 року.


Як підвищити продуктивність цього оператора UPDATE у версії 8.1.7?


Привіт, Том!

Я використовую наступний оператор update для таблиці t1:

update t1
set (amount, enter_date) =
(select amount, trunc(enter_date)
from t2
where t2.id = t1.id
and t2.code in (select code from t3 where t3.sign = 1)
and t2.enter_date = (select max(enter_date) from t2
where t2.id = t1.id
and t2.code in (select code from t3
where t3.sign = 1)));

У таблиці t1 – Близько 10000 рядків, а в таблиці t2 – Близько 2 мільйонів. Я намагаюся отримати в таблиці t1 інформацію про суму та дату останньої банківської транзакції для всіх ідентифікаторів з таблиці t2 (Таблиці транзакцій). Таблиця t3 (Код транзакції) визначає, які транзакції слід враховувати.


Цей оператор update виконується досить довго. Що б ти порекомендував для підвищення продуктивності цього оператора update?


Відповідь Тома Кайта


Ну, цей оператор update, За наявності відповідних індексів і використанні вартісного оптимізатора, повинен працювати "порівняно швидко". На моїй персоналке він працює меньше 2 хвилин – хіба це довго?


У будь-якому випадку, я зімітував вашу ситуацію так:

create table t1 ( id int, amount int, enter_date date );

insert into t1
select rownum, object_id, sysdate
from all_objects big_table
where rownum <= 100000;

alter table t1 add constraint t1_pk primary key(id);

analyze table t1 compute statistics
for table
for all indexes
for all indexed columns;

create table t2 (id int, amount int, enter_date date, code int);
begin
for i in 1 .. 20
loop
insert /*+ APPEND */ into t2
select id, amount-i, enter_date-i, mod(i,2)
from t1;
commit;
end loop;
end;
/

create index t2_idx on t2(id,enter_date);

analyze table t2 compute statistics
for table
for all indexes
for all indexed columns;

create table t3 ( code int, sign int primary key);
insert into t3
select rownum, rownum
from all_users;

analyze table t3 compute statistics
for table
for all indexes
for all indexed columns;


Отже, у мене теж 100000 рядків, 2000000 рядків і окрема таблиця для пошуку коду транзакції. Я створив таке уявлення:

create or replace view v
as
select rid, amount, trunc(enter_date) enter_date
from ( select t1.rowid rid, t2.amount, t2.enter_date,
max (t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null)
/

І спробував виконати зміна процедурно:

declare
type ridArray is table of rowid index by binary_integer;
type numArray is table of number index by binary_integer;
type datArray is table of date index by binary_integer;
l_rid ridArray;
l_amt numArray;
l_ed datArray;
l_array_size number := 100;
l_cnt number := 0;
cursor c is select rid, amount, enter_date from v;
begin
open c;
loop
fetch c bulk collect
into l_rid, l_amt, l_ed
limit l_array_size;
forall i in 1 .. l_rid.count
update t1 set amount = l_amt (i), enter_date = l_ed (i)
where rowid = l_rid(i);
l_cnt := l_cnt + l_rid.count;
dbms_application_info.set_client_info ("updated" / / l_cnt / / "rows");
exit when c%notfound;
end loop;
close c;
end;
/

Потім спробував зробити те ж саме одним оператором update:

update t1
set (amount, enter_date) =
(select amount, trunc(enter_date)
from t2
where t2.id = t1.id
and t2.code in (select code from t3 where t3.sign = 1)
and t2.enter_date = (select max(enter_date) from t2
where t2.id = t1.id
and t2.code in (select code from t3
where t3.sign = 1)));

Для PL / SQL-блоку потрібно:

 call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ——— – ———- ———-
Parse 1 0.02 0.03 0 0 1 0
Execute 1 71.28 80.10 15610 106393 101914 1
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ——— – ———- ———-
total 2 71.30 80.14 15610 106393 101915 1

близько 80 секунд, а для update:

update t1
set (amount, enter_date) =
(select amount, trunc(enter_date)
from t2
where t2.id = t1.id
and t2.code in (select code from t3 where t3.sign = 1)
and t2.enter_date = (select max(enter_date) from t2
where t2.id = t1.id
and t2.code in (select code from t3
where t3.sign = 1)))

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ——— – ———- ———-
Parse 1 0.00 0.01 0 0 0 0
Execute 1 62.90 108.38 1366 5 2728542 204504 100000
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ——— – ———- ———-
total 2 62.90 108.39 1366 5 2728542 204504 100000


трохи більше …


Так що, можете спробувати процедурний підхід, але, за наявності відповідних індексів, зміна 100000 рядків не повинно викликати особливих проблем.


Чому SQL виконується повільніше, ніж PL / SQL?


Том, що в цьому прикладі робить його виключенням з твого простого правила: один SQL-оператор працює швидше і використовує менше ресурсів, ніж процедурне рішення?


Відповідь Тома Кайта


Справа в суті зміни: у ньому використовується багато корельованих підзапитів для пошуку max enter_date і т.п. Це ускладнює при оптимізації вибір будь-якого іншого плану крім вкладених циклів (саме так виконуються корельовані вкладені запити).


Я б хотів зробити наступне:

UPDATE(
select *
from ( select t1.amount t1_amt, t1.enter_date t1_ed,
t2.amount t2_amt, t2.enter_date t2_ed,
max (t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null)
)
set t1_amt = t2_amt, t1_ed = t2_ed;

Але, оскільки немає захисту ключем (key preservation) і використовуються аналітичні функції (що має на увазі "упорядкування" підставляємо подання), так робити не можна. Я хотів би вибирати рядки з допомогою саме такого запиту, оскільки він виконує множинні операції (що прискорює роботу при великій кількості рядків), а не 100000 складених циклів …


Отже, щоб "змінити результати цього запиту", мені, на жаль (в 8i), доводиться використовувати запит + update. Виконуючи множинні операції …


У версії 9i з'явився оператор MERGE:

merge into t1
using (
select rid, amount, trunc(enter_date) enter_date
from ( select t1.rowid rid, t2.amount, t2.enter_date,
max (t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null)
) t2
on ( t1.rowid = t2.rid )
when matched then
update set t1.amount = t2.amount, t1.enter_date = t2.enter_date
when not matched then
insert (id) values ( null )

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ——— – ———- ———-
Parse 1 0.01 0.01 0 0 0 0
Execute 2 43.03 53.45 16486 7558 102809 100000
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ——— – ———- ———-
total 3 43.05 53.46 16486 7558 102809 100000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 137

Rows Row Source Operation
——- —————————————————
2 MERGE (cr=7558 r=16486 w=10807 time=53444549 us)
100000 VIEW (cr=7531 r=16477 w=10807 time=49308344 us)
100000 HASH JOIN OUTER (cr = 7531 r = 16477 w = 10807 time = 48646484 us)
100000 VIEW (cr=7187 r=16139 w=10807 time=46776991 us)
1000000 WINDOW SORT (cr = 7187 r = 16139 w = 10807 time = 45026596 us)
1000000 HASH JOIN OUTER (cr = 7187 r = 5331 w = 0 time = 27551523 us)
100000 INDEX FAST FULL SCAN OBJ # (37458) (cr = 215 r = 210 w = 0 time = 235332 us) (object id 37458)
1000000 VIEW (cr=6972 r=5121 w=0 time=18959603 us)
1000000 HASH JOIN (cr = 6972 r = 5121 w = 0 time = 15955318 us)
1 TABLE ACCESS BY INDEX ROWID OBJ # (37461) (cr = 2 r = 0 w = 0 time = 97 us)
1 INDEX UNIQUE SCAN OBJ # (37462) (cr = 1 r = 0 w = 0 time = 58 us) (object id 37462)
2000000 TABLE ACCESS FULL OBJ # (37459) (cr = 6970 r = 5121 w = 0 time = 4275638 us)
100000 TABLE ACCESS FULL OBJ # (37457) (cr = 344 r = 338 w = 0 time = 376606 us)


Він робить те ж саме, що і мій PL / SQL-код, але трохи ефективніше.


Аналогічна проблема


Я намагався розібратися, чому мій оператор update виконується годинами, А якщо розділити його на дві частини – ці частини виконуються декілька секунд. Можливо, проблема пов'язана з самосоедіненіем, але я не впевнений.


Моя проблема в тому, що спочатку не була врахована можливість народження одностатевих двійнят. У таблиці є стовпці CLIID, стать та дата народження. Я просто хочу помітити можливі дублюються рядка (частина з них може бути пов'язана з двійнятами, а частина – з друкарськими помилками при наборі) по кожному CLIID. У таблиці – близько 150000 записів, з яких близько 10000 потенційно можуть бути дублюючими.


Я написав наступний оператор:

update testchild tcx set pd = (select “rank” from
(Select cliid, id, fname, rank () over (partition by cliid order by fname) "rank"
from testchild tc2 where exists
(Select * from testchild tc3 where tc3.cliid = tc2.cliid and cliid> 1000000000
group by cliid having count(*) > 1)) x
where x.id = tcx.id)

Plan Table
————————————————– —————————————-
/ Operation / Name / Rows / Bytes / Cost / Pstart / Pstop /
————————————————– —————————————-
/ UPDATE STATEMENT / / 152K / 745K / 284 / / /
/ UPDATE / TESTCHILD / / / / / /
/ TABLE ACCESS FULL / TESTCHILD / 152K / 745K / 284 / / /
/ VIEW / / 7K / 193K / 319 / / /
/ WINDOW SORT / / 7K / 149K / 319 / / /
/ FILTER / / / / / / /
/ TABLE ACCESS FULL / TESTCHILD / 7K / 149K / 284 / / /
/ FILTER / / / / / / /
/ SORT GROUP BY NOSORT / / 1 / 9 / 1 / / /
/ FILTER / / / / / / /
/ INDEX RANGE SCAN / TC_CLIID / 1 / 9 / 1 / / /
————————————————– —————————————-


Сам запит виконується близько 6 секунд. Якщо я виконую CREATE TABLE AS SELECT, а потім змінюю таблицю testchild на основі цієї "тимчасової" таблиці, потрібно менше хвилини. Додавання індексу по ID для тимчасової таблиці особливої ролі не грає. А ось якщо виконувати представлений вище повний update… Він почався в 11 ранку вчора, і сьогодні о 9 ранку ще виконується. Кількість consistent gets вимірюється мільярдами. Все це працює у версії 9i на персоналке.


Що відбувається, не підкажеш?


Відповідь Тома Кайта


Проблема та ж.


Цей 6-секундний запит виконується 152k раз (представлена статистика Свідоцтво, що змінювати в таблиці доведеться 152k рядків) Представлений раніше прийом дозволить виконувати 6-секундний запит блоком (IN BULK), а не порядково, і змінювати його результати.


Прекрасне рішення


Я спробував запропоноване процедурне рішення, і воно зажадало близько 60 секунд. Аналітичні функції – це чудово.


У мене, однак, залишилася одна проблема. Замість створення уявлення я спробував створити курсор:

CURSOR last_payment_cur IS
select rid, amount, trunc(enter_date) enter_date
from ( select t1.rowid rid, t2.amount, t2.enter_date,
max (t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null);

У результаті було отримано таке повідомлення про помилку:

LINE/COL ERROR
51/32 PLS-00103: Encountered the symbol "(" when expecting one of the
following: , from

Але цей самий запит у вікні SQL * Plus чудово працює. Чому не можна створити курсор?


Відповідь Тома Кайта


Я спеціально створив уявлення, побачивши в питанні згадка версії 8.1.7


Див розділ в моїй книзі, присвячену аналітичним функціям. Стор. 586

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


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

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

Ваш отзыв

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

*

*