Скільки коштує update?, Інші СУБД, Бази даних, статті

Ця стаття присвячена аналізу наслідків використання одного оператора update для виконання всіх можливих змін даних рядка в клієнтському додатку.


На ринку є кілька генераторів додатків, що підтримують базовий підхід до розробки “чим простіше, тим краще”. Простий код легше генерувати і простіше супроводжувати, навіть якщо він здається трохи менше ефективним. Але якщо генератор форм завжди генерує код для оновлення кожного стовпця в таблиці, навіть якщо користувач змінює всього лише одне поле у ​​формі, наскільки при цьому зростає навантаження на сервер бази даних?


Коротка історія генераторів форм


Був час, коли в SQL * Forms (так його тоді називали) було прийнято використовувати єдиний SQL-оператор поновлення для блоку. Цей оператор update оновлював (за значенням rowid) Кожен стовпець таблиці, що згадується в блоці. Це здавалося непоганою ідеєю, оскільки спрощувало код і робило його більш ефективним на клієнті: не потрібно було вирішувати обчислювально складну задачу визначення действіетльно змінених полів і динамічно формувати SQL-оператор для поновлення тільки відповідних стовпців в базі даних.


Потім, в районі версії Forms 4.5 (я можу помилятися з версією – чекаю поправок), корпорація Oracle додала прапор, який можна встановлювати для вибору або одного оператора, “оновлюючої все”, або динамічно генерується оператора “оновлювати тільки мінімальний набір стовпців”. Яка з цих опцій краще?


Скільки коштує оновити стовпець?


Зокрема, нас буде цікавити, скільки коштує оновлення стовпця, якщо його значення не змінюється. Якби сервер міг виявити, що при оновленні реально нічого не змінюється, то додаткові витрати ресурсів на зайве оновлення були б мінімальні. На жаль, сервер і не намагається виявляти “зайві” оновлення. В кінцевому підсумку, логічно припускати, що при оновленні дані повинні змінюватися. Було б контрпродуктивно додавати перевірку умови, що майже завжди буде істинним, просто щоб трохи зекономити в тем “дуже рідкісних і дивних” випадках, коли при оновленні зміни не відбуваються.


Отже, що ж може статися при оновленні одного стовпця в таблиці в окремій транзакції? Зрозуміло, що рядок треба заблокувати і дані змінити, а для цього – отримати запис списку зацікавлених транзакцій (Interested Transaction List – ITL) в блоці. Необхідно захопити слот таблиці транзакцій (transaction table slot) у заголовку сегмента скасування (undo segment header) для використання в якості глобально видимої “посилання” на транзакцію, а також внести запис скасування в блок скасування, описує, як скасувати зміни, тільки що виконані в блоці даних. Зміни в усіх трьох блоках необхідно записати в журнал повторного виконання (спочатку – в буфер журналу), і в цьому простому випадку потрібно тільки один запис повторного виконання (redo record).


Потім, при фіксації транзакції в слоті таблиці транзакцій записується відповідне значення номера системного зміни (commit SCN) І він позначається як вільний, а адреса використаного блоку скасування теж можна записати в пул вільних блоків в блоці заголовка сегмента скасування. Ці зміни в блоці заголовка сегмента скасування записуються в журнал повторного виконання (в буфер), а для скидання буфера журналу на диск викликається процес запису журналу (log writer process – lgwr), Після чого користувальницький процес повідомляють, що транзакція успішно зафіксована. (Oracle може, а в цьому випадку, швидше за все, і буде також очищати змінений блок даних, але не буде записувати виконані при очищенні зміни в журнал повторного виконання).


Припустимо, користувач змінив на екрані всього одне поле – представлене вище опис дає зрозуміти, який обсяг роботи доведеться виконати серверу, щоб реалізувати цю зміну. Але що, якщо генератор форм оновив весь рядок – які додаткові витрати ресурсів при цьому виникають?


Витрати на отримання записи ITL, блокування та оновлення рядки, імовірно, не сильно зміняться, а витрати на отримання блоку заголовка сегмента скасування взагалі не зміняться.


Але от обсяг даних в записі скасування, ймовірно, зросте суттєво – замість додаткових приблизно 100 байт плюс стара версія одного стовпця ми отримаємо додаткові байти плюс стара версія всіх стовпців. Ймовірно, це не так уже й важливо, – в кінцевому підсумку, сервер Oracle пише блоки, а не записи, – але якщо записи скасування в результаті виросли за розміром в чотири рази, доведеться записувати у чотири рази більше блоків скасування.


Аналогічно, істотно зміниться і запис повторного виконання. В ідеалі, запис повторного виконання буде для цього зміни довжиною близько 200 байтів (плюс ще 200, пов’язаних з з блоком заголовка сегмента і вектором аудиту транзакції). Цей запис, ймовірно, буде доповнена до 512 байтів (типовий мінімальний розмір блоку ОС) всяким сміттям при фіксації транзакції. Але основна запис повторного виконання полягає, по суті, з двох векторів змін – вектора для блоку таблиці і вектора для запису відміни, – і обидва вони збільшаться в розмірі, тому що запис скасування включатиме стару версію всіх стовпців, а запис повторного виконання для таблиці – нову версію всіх стовпців. Тому обсяг генеруються даних повторного виконання збільшується в два-чотири рази, а в багатьох інтенсивно завантажених системах швидкість скидання даних повторного виконання на диск часто є критичною проблемою продуктивності.


Але це ще не все


Додатковий обсяг даних відміни і повторного виконання – це ще не обов’язково найбільша проблема – в кінцевому підсумку, додатково генеруються і буферизованих дані часто не так вже й великі в порівнянні з базовими накладними витратами. Більше того, оскільки архітектура сервера Oracle така, що записи на диск, по можливості, виконуються фоновими “асинхронними” процесами, кінцевий користувач не часто відчуває уповільнення через записи на диск. Але є ще ряд нюансів, які безпосередньо впливають на продуктивність системи з точки зору кінцевих користувачів.


Як ви думаєте, що робить сервер при оновленні стовпця без реальної зміни з наступними об’єктами:



Спрацьовують чи рядкові тригери типу “update of {Спісок_столбцов} “?
before row
after row
instead of
Чи змінюються індекси, що включають такий стовпець?
Що, якщо це індекси на основі B *-дерева?
А як щодо індексів на основі бітових карт?
Що буде з індексами по функції (function-based indexes)?
Як сервер буде забезпечувати цілісність посилань?
Якщо цей стовпець – підлеглий?
Якщо цей стовпець – головний?

Тригери


Створимо просту таблицю з тригером і виконаємо дії, представлені нижче в лістингу 1:

Лістинг 1

create table t2 (
id_gp number(4),
id_p number(4),
n2 number(4)
);
insert into t2 values (1,1,1);
commit;

create or replace trigger t2_bru
before update of id_gp on t2
for each row
— when (
— new.id_gp != old.id_gp
–or new.id_gp is null and old.id_gp is not null
–or old.id_gp is null and new.id_gp is not null
— )
begin
dbms_output.put_line(“Updating”);
end;
/
column rid new_value m_rid
select rowid rid
from t2
where rownum = 1;
update t2 set id_gp = id_gp where rowid = “&m_rid”;


Тригер спрацьовує. Те ж саме відбувається і з тригером after-row update. Підтвердження мого припущення, що спрацює і тригер instead of залишаю читачеві в якості вправи.


Фактично, рядковий тригер before генерує одну додаткову запис скасування і одну додаткову запис повторного виконання, навіть якщо нічого при цьому не робить через додавання досить складної конструкції when, Яка в прикладі закоментований. Так що, якщо є вибір, трохи ефективніше буде використовувати рядкові тригери after.


Індекси


Деякі експерименти з індексами повинні бути більш продуманими, оскільки для оцінки всіх витрат може мати сенс враховувати логічний введення / виведення, а для цього – побудувати досить велику таблицю, щоб індекс по ній був багаторівневим. Однак для деяких тестів досить буде перевіряти обсяг даних відміни і повторного виконання, і враховувати блокування, залишаючи осторонь такий тонкий момент, як обсяг логічного введення / виведення.


Отже, що ж відбувається при виконанні поновлення без фактичної зміни значення для стовпця, що входить в простій індекс на основі B-дерева? Нічого. Сервер Oracle визначає, що проіндексовані значення не змінилося, і навіть не звертається до індексу, не кажучи вже про блокування запису. Це вірно і для простих індексів на основі бітових карт.


Ви можете засумніватися, а чи не станеться щось жахливе при переході на індекси за функцією – чи не буде викликатися функція “на всякий випадок”, чи правильно відстежує сервер Oracle залежності між функціями і використовуваними в них стовпцями? Відповідь: Все працює правильно, ніяких зайвих обчислень функції або проходів по індексу не буде. В якості тесту можна створити таку ж таблицю, як в лістингу 1, а потім виконати наступні SQL-оператори:

create or replace function my_fun (
i1 in number,
i2 in number
) return number deterministic
as
begin
dbms_output.put_line(“Testing function”);
return i1 + i2;
end;
/
create index t2_idx on t2(my_fun(id_gp,id_p));
update t2 set id_gp = id_gp where rowid = “&m_rid”;
update t2 set id_gp = id_gp + 1 where rowid = “&m_rid”;

Ви побачите, що функція викликається один раз (оскільки в таблиці тільки один рядок), при створенні індексу, але вона не викликається при відновленні без зміни значення. До речі, при виконанні другого оператора update функція буде викликатися двічі – один раз для пошуку вихідного місцезнаходження в індексі, а другий – для обчислення нового. Ви можете виявити, що функція викликається ще два рази при виконанні відкату – я впевнений, що стикався з цим в колишніх версіях, коли індекси за функції тільки з’явилися, але зараз це більше не відбувається.


Цілісність посилань


Ось тут, мабуть, і настає вирішальний момент для систем оперативної обробки транзакцій (ООТ – OLTP). Їх чекає подвійний удар – спочатку в підпорядкованої таблиці, а потім і в головній.


Візьміть таблицю з лістингу 1 і вставте в неї ще 9 однакових рядків, щоб усього їх стало 10. Потім виконайте наступні тести, щоб перевірити обсяг логічного введення / виводу і т.п.:

update t2 set id_gp = id_gp;
10 rows updated.
alter table t1
add constraint t1_pk primary key (id_gp);
alter table t2
add constraint t2_fk_p1 foreign key (id_gp) references t1;
update t2 set id_gp = id_gp;
10 rows updated

Ви виявите, що кількість прочитаних блоків db block gets (current mode gets) Збільшиться на 10 після додавання обмеження цілісності. Чому? Тому що при кожному оновленні сервер Oracle перевіряє обмеження зовнішнього ключа, і робить це шляхом перегляду індексу з первинного ключу в головній таблиці за допомогою current mode gets. Якщо головна таблиця буде досить великий, піде років зо три current mode gets для кожного надлишкового поновлення стовпця в підпорядкованої таблиці.


Тепер перейдемо до проблем головної таблиці. Достатньо тільки спробувати виконати оновлення “без зміни” рядки головної таблиці, якщо немає індексу по зовнішньому ключу, і ви виявите сумно відому блокування TM/4. Індекси по зовнішнім ключам не обов’язкові, якщо значення первинного ключа не змінюються і не видаляються, але якщо ви стикаєтеся з випадковими “зависаннями” і повідомленнях про взаємні блокування, то, ймовірно, ви зіткнулися з класичною проблемою, – ви самі первинні ключі не оновлюєте, а ось генератор додатків за кадром це робить.


Компроміс буде завжди


Ви могли вже вирішити, що пора переписувати масу коду. Але, написання коду, генеруючого ідеальний SQL-оператор щоразу, підвищує ймовірність помилок. Компроміс між підвищеним ризиком помилки (а також часом на кодування, тестування і налагодження) і продуктивністю завжди буде, так що, якщо сервер далекий до повного завантаження, цілком припустимо й обгрунтовано буде ігнорувати всі описані вище проблеми. По крайней мере, в короткостроковій перспективі.


Є і ще один, більш тонкий компроміс. Якщо програма генерує ідеальний SQL-оператор для кожного оновлення, яке воно потенційно може згенерувати кінцеве додаток, то істотно зростає кількість різних SQL-операторів в системі.


Теоретично, якщо в таблиці є N стовпців, тоді різних операторів update може бути power(2,N) – 1, І це якщо обмежитися тільки однорядковими оновленнями по rowid. Якщо не збільшити відповідно розділяється пул і не налаштувати декілька параметрів, на зразок session_cached_cursors, Може виявитися, що економія в одному місці обертається додатковими проблемами (такими як конфлікти доступу до бібліотечного кешу) в іншому.


Висновок


Якщо дозволити інструментальним засобам створення кінцевих додатків використовувати простий спосіб оновлення даних (шляхом генерації одного SQL-оператора для всіх можливих оновлень), навантаження на систему може істотно зрости. При роботі в середовищі клієнт-сервер або в багаторівневій архітектурі може мати сенс використовувати додаткові обчислювальні ресрси на клієнті або сервері додатків для побудови специфічних SQL-операторів, що мінімізують витрати ресурсів на сервері. Рішення при цьому, однак, – не очевидне. Переконайтеся, що переваги окуплять витрати.

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


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

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

Ваш отзыв

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

*

*