Том Кайт: про ігнорування, блокування та розборах, Інші СУБД, Бази даних, статті

Наш експерт розглядає програмну конструкцію WHEN OTHERS, блокування, каскадні оновлення та розбори.

У мові PL / SQL є вкрай небажана для мене програмна конструкція WHEN OTHERS. Коли ця пропозиція використовується в блоці обробки виняткових ситуацій, то захоплюються всі необроблені виняткові ситуації. Іноді цю пропозицію корисно, наприклад, для протоколювання помилок:

exception
when others
then
log_error(….);
raise;
end;

Проблема полягає в тому, що багато хто використовує пропозицію WHEN OTHERS без подальшого ініціювання виняткових ситуацій (виклики RAISE або RAISE_APPLICATION_ERROR). Це фактично приховує помилку. Насправді помилка відбувається, але вона не обробляється якимось осмисленим чином, а просто ігнорується – мовчки. Зухвалий вашого коду не має ніякого поняття, що сталося щось надзвичайне і ваш код збився, зазвичай він думає, що насправді все працює успішно.

На сайті Ask Tom (asktom.oracle.com) Я безперестанку отримую питання про це. Наприклад, нещодавно я отримав запитання:

Я створив пакет, який запускає 10 завдань для масової завантаження даних в плоский файл. Мені потрібно використовувати пакет UTL_FILE, оскільки в середині процесу я викликаю три процедури, які витягують деякі дані. Загальний час масової завантаження 9500000 рядків – 6:00. Мені потрібен ваш рада, як знизити цей час.

Мій код:
PROCEDURE prcl_MakeFile(…)
IS
… variables …
BEGIN
l_FileID := UTL_FILE.FOPEN (…);
OPEN cur;
LOOP … тут обробка записів … … багато коду …
END LOOP;
CLOSE cur;
UTL_FILE.FCLOSE(l_FileID);
EXCEPTION
WHEN OTHERS THEN
IF (UTL_FILE.IS_OPEN(l_FileID))
THEN UTL_FILE.FCLOSE(l_FileID);
END IF;
END prcl_MakeFile;

Моя відповідь була простою: я можу безмежно прискорити цей код. Все, що повинна робити ця процедура:

PROCEDURE prcl_MakeFile(…)
IS
… variables…
BEGIN
Return;
END prcl_MakeFile;

Ці дві процедури логічно еквівалентні, але моя працює набагато швидше! Отже, чому ж вони логічно еквівалентні? Через пропозиції WHEN OTHERS, за якому не слід виклик RAISE або RAISE_APPLICATION_ERROR. Припустимо, при виклику UTL_FILE. FOPEN виникає помилка – що тоді станеться? Весь код буде пропущено, але ніхто не дізнається про це. Коли в блоці обробки виняткових ситуацій використовується пропозицію WHEN OTHERS, а повторне ініціювання виняткової ситуації відсутня, весь код на мою думку можна безболісно видалити. Він же вам не потрібен, оскільки ви ігноруєте той факт, що цей код не виконується, якщо при його виконанні виникає помилка. Якщо ви припускаєте, що код іноді може не виконуватися, ви фактично можете дозволити цим кодом ніколи не виконуватися. Ви навіть не можете покладатися, що цей код насправді працює, тому вам ніколи не потрібно виконувати його.

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

procedure p
is
begin
insert into t1 values(1);
insert into t2 values(2);
insert into t3 values(3);
exception
when others then
dbms_output.put_line
(“something bad happened!”);
end;

Зухвалий цю процедуру ніколи не дізнається, що:


У цієї процедури є чотири можливих результату, але викликає ніколи не дізнається, який же результат вийшов насправді. По крайней мере, якщо з коду видалити конструкцію WHEN OTHERS, що викликає буде знати, що сталося під час виконання процедури – особливо, якщо нічого не відбулося.

Додаткову інформацію по цій важливій темі см. на сайтах:


Блокування в веб-середовищі

Питання. Я недавно натрапив на. NET-додаток, що працює з сервером Oracle Database 10g, Розробники цього додатка використовували оптимістичне блокування (витягували з таблиці ідентифікатор версії, оновлювали необхідну рядок, а потім оновлювали ідентифікатор версії), оскільки це єдиний спосіб гарантувати неможливість одночасного поновлення однієї і тієї ж записи багатьма користувачами. Я вважаю, що замість зайвого коду, в якому реалізований штучний механізм блокування, можна робити теж саме з допомогою пропозиції FOR UPDATE. Чи правий я?

Відповідь. У n-Звенні додатків є два способи доступу до бази даних:

1. Із збереженням стану: підключення до сервера зберігаються протягом тривалого часу, протягом якого генерується багато веб-сторінок.

2. Без збереження стану: підключення зберігаються протягом дуже короткого періоду часу, може бути навіть менше часу генерації однієї HTML-станиці.

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

Якщо у вас підключення без збереження стану, ви не можете використовувати песимістичне блокування (пропозиція FOR UPDATE). Ви будете втрачати таку блокування після генерації кожної сторінки. Для такого типу додатків підходить тільки оптимістичне блокування.

Отже, розробники, з якими ви працюєте, роблять, ймовірно, те, що треба. Цю тему я широко виклав у книзі Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress, 2005). Наведемо з неї невеликий уривок. Для підгонки до обсягу та формату колонки оригінальний текст був модифікований.

Оптимістичне блокування або песимістичне блокування?

Який метод найкращий? З мого досвіду, песимістичне блокування дуже добре працює в сервері Oracle Database (але, можливо, це не так в інших СУБД), і воно має багато переваг у порівнянні з оптимістичним блокуванням. Однак для нього потрібне підключення до сервера бази даних зі збереженням стану, таке як в середовищі “клієнт-сервер”, оскільки між підключеннями блокування не зберігаються. Один цей факт у багатьох випадках сьогодні робить песимістичний блокування нереалістичним. У минулому при роботі з клієнт-сервісними додатками і з кількома дюжинами або сотнями користувачів це був мій перший і єдиний вибір. Зараз же, проте, я рекомендую для більшості додатків оптимістичне управління конкурентним доступом. За утримування підключення протягом усього сеансу доводиться платити занадто високу ціну.

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


Отже, що ж використовую я? Я віддаю перевагу використовувати підхід зі стовпцем версії і зі стовпцем позначки часу. Це дає мені додаткову інформацію про час поновлення конкретної рядка. Цей підхід менш дорогий у порівнянні з обчисленням контрольної суми або хеш-значення, і він не піддається ризику зустрічі з даними типу LONG, LONG RAW, CLOB, BLOB і іншими дуже великими стовпцями.

Якщо таблиця, як і раніше використовується в схемі песимістичного блокування, наприклад, в клієнт-серверних додатках, то при додаванні оптимістичного управління конкурентним доступом для веб-додатків я віддаю перевагу підхід з використанням псевдостолбца ORA_ROWSCN. Справа в тому, що існуюче успадковане додаток може не “розуміти” появи нового стовпця, і навіть якщо я прийму додаткові заходи для приховування додаткового стовпця, я не можу не брати до уваги накладні витрати на тригер, необхідний для підтримки цього. У відношенні цього механізм ORA_ROWSCN буде ненав’язливим і полегшеним (Звичайно ж після пересозданія таблиці).

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

Каскадні оновлення

Питання. У мене є таблиця EMP з дочірніми таблицями, кожна з яких має свої власні дочірні таблиці. Я хочу в таблиці EMP оновити табельний номер службовця і хочу, щоб всі дочірні таблиці (включаючи дочірні таблиці дочірніх таблиць) оновилися автоматично. Як це зробити?

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

Тим не менш, ви можете використовувати каскадне оновлення, якщо воно дійсно потрібно (наприклад, для одноразової оновлення даних, які були злиті з даними таблиці). Для цього використовуйте відкладаються обмеження і збережену процедуру. Наприклад, припустимо у вас є наступна схема:

create table p
( x int primary key );
create table c1
( x constraint c1_fk_p r
references p deferrable,
y int,
primary key(x,y) );
create table c2
( x int,
y int,
z int,
constraint c2_fk_c1
foreign key(x,y)
references c1 deferrable,
primary key(x,y,z));

Тепер, обмеження в таблицях C1 і C2 можуть бути відкладені – в цьому випадку вони перевіряються або при фіксації транзакції, або при зміни стану обмежень на негайне (IMMEDIATE). Це дозволяє написати збережену процедуру, як показано на лістингу 1.







Лістинг 1: каскадні оновлення з відкладеними обмеженнями.

create or replace procedure
cascade_p_c1_c2
( p_old in int, p_new in int )
as
begin
execute immediate “set constraint c1_fk_p deferred”;
execute immediate “set constraint c2_fk_c1 deferred”;
update p set x = p_new where x = p_old;
update c1 set x = p_new where x = p_old;
update c2 set x = p_new where x = p_old;
execute immediate “set constraint c1_fk_p immediate”;
execute immediate “set constraint c2_fk_c1 immediate”;
end;

А тепер ви можете викликати цю процедуру, і вона успішно виконає каскад оновлень батьківського таблиці P, дочірньої таблиці C1 і її дочірньої таблиці C2. Але знову-таки, ви повинні робити це в рідкісних випадках – Це не те, що повинно стати частиною вашої постійної стратегії проектування та реалізації.

Є розбір і є розбір

Питання. У мене проблеми з клямками бібліотечного кеша. Я спробував ідентифікувати причину цих проблем за допомогою пакета STATSPACK:

	         Per Second    Per Transaction

———– —————

User calls: 1,107.76 53.24
Parses: 389.92 18.74
Hard parses: 0.28 0.01

У вас є які-небудь пропозиції?

Відповідь. У вашій системі виконується розбір маси SQL-операторів – приблизно 390 разів на секунду. Хороша новина – ці розбори в основному часткові (soft parse).

Тільки самі розробники можуть зменшувати кількість розборів. Сервер Oracle Database розбирає SQL-оператор кожен раз, коли додаток попросить про це, і тут додаток просить дуже часто.

За 12.98 хвилин на вашу звіту пакета STATSPACK виконується приблизно 303 669 розборів. Для кожного розбору потрібна клямка бібліотечного кеша.

Оптимальне рішення: розумійте, що єдино хорошим розбором є неіснуючий розбір, і якщо в коді, який звертається до бази даних, дотримується цей підхід, у вас НЕ буде занадто багато розборів. Залишайте курсори відкритими. Не закривайте їх до тих пір, поки вони не будуть більше потрібні. Ви можете легко реалізувати цей підхід, розмістивши всі SQL-оператори в збережених процедурах (машина PL / SQL автоматично кешує курсори – не закриває їх – так що, якщо ви зажадаєте: “PL / SQL закрий цей курсор”, сервер бази даних проігнорує вас і помістить курсор в кеш).

Якщо у вас використовується мова Java, використовуйте для кешування операторів інтерфейс Java DataBase Connectivity (JDBC), так що, цей інтерфейс буде ігнорувати спроби розробників закрити курсори.

Єдиний спосіб зменшувати кількість засувок полягає в тому, щоб зменшити кількість разів, коли ви робите те, для чого потрібні засувки. І розбір – величезний користувач засувок.

Ще потрібно розглянути наступне: чи використовується у вас механізм кешування курсорів в сеансах (параметр ініціалізації session_cached_cursors). Він може зробити ваші часткові розбори більш “м’якими.” Установка цього параметра допоможе, якщо ваш додаток багаторазово виконує наступне:

1. Розбір.
2. Зв’язування.
3. Виконання.
4. Закриття.
5. Перехід на крок 1 і багаторазове повторення всіх кроків.

Розглянемо приклад, моніторинг якого я виконую, використовуючи невеликий набір засобів тестування runstats (asktom.oracle.com/tkyte/runstats.html). Я почну з невеликою процедури, в якій багато разів виконуються тільки розбори, для цього використовується динамічний SQL (ці розбори будуть в основному м’якими розборами). Процедура показана на лістингу 2.







Лістинг 2: невпинні розбори.

create or replace procedure p( p_n in number )
as
l_cursor sys_refcursor;
begin
for i in 1 .. p_n
loop
open l_cursor for
“select * from dual d” // mod(i,2);
close l_cursor;
end loop;
end;

Тепер, якщо я порівняю різницю в кількості засувок, коли я виконую цю процедуру з кешування курсорів в сеансі і без кешування, я побачу велика різниця (див. лістинг 3).







Лістинг 3: засувки з параметром ініціалізації session_cached_cursors = 0.

SQL> alter session set session_cached_cursors=0;
Session altered.
SQL > exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL > exec p(1);
PL/SQL procedure successfully completed.
SQL > exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL > exec p(100000);
PL/SQL procedure successfully completed.
SQL > exec runStats_pkg.rs_stop(90000);
Name Run1 Run2 Diff
STAT…parse count (total) 6 100,005 99,999
LATCH.shared pool simulator 8 100,012 100,004
LATCH.shared pool 10 100,053 100,043
LATCH.library cache lock 36 400,044 400,008
LATCH.library cache 67 400,093 400,026
Run1 latches total versus runs — difference and pct
Run1 Run2 Diff Pct
361 11,001,012 1,000,651 .04%
PL/SQL procedure successfully completed.

Отже лістинг 3 показує, що для виконання 100000 м’яких розборів знадобилося приблизно 11000000 засувок, більшість з яких – засувки бібліотечного кеша. Тепер, я встановлю параметр session_cached_cursors наступним чином:

SQL> alter session set
session_cached_cursors=100;
Session altered.

Тепер, я виконаю цей же приклад (див. лістинг 4). Як видно на лістингу, кількість засувок значно знизилося, а кількість розборів залишилося тим самим – механізм кешування курсорів в сеансах почав діяти і зробив часткові розбори більше м’якими.







Лістинг 4: засувки з параметром ініціалізації session_cached_cursors = 100.

Name                            Run1       Run2     Diff
STAT…parse count (total) 6 100,005 99,999
STAT…execute count 6 100,005 99,999
STAT…session cursor cache hi 2 100,001 99,999
STAT…calls to get snapshots 2 100,001 99,999
STAT…opened cursors cumulati 6 100,005 99,999
STAT…recursive calls 5 300,002 299,997
Run1 latches total versus runs — difference and pct
Run1 Run2 Diff Pct
304 845 541 35.98%

Транспонування стовпця в рядок

Питання. Я хочу представити значення стовпця як рядки. Тобто, я хочу, щоб результат запиту до таблиці EMP виглядав так:

      DEPTNO      ENAME
———— ——————–
10 clark king miller
20 adams ford …

Чи можна це зробити, використовуючи лише мова SQL?

Відповідь. Після появи в сервері Oracle8i Release 2 аналітичних функцій і функції SYS_CONNECT_BY_PATH () в сервері Oracle9i Database Release 1 зробити це на мові SQL досить просто. Дотримуйтеся наступного підходу:



  1. Секціонуючою дані за номерами відділів (стовпець DEPTNO), дані кожного відділу сортуйте за прізвищами службовців (стовпець ENAME), призначте рядках порядкові номери, використовуючи аналітичну функцію ROW_NUMBER ().

  2. Використовуйте ієрархічний запит (з умовою CONNECT BY), починаючи з першого рядка (ROW_NUMBER () = 1), потім з’єднайте цей запис з рядком номер 2, що має те ж саме значення стовпця DEPTNO, і т.д. Отже, в кінцевому рахунку ви для кожного відділу отримаєте запис. в якій поєднані рядки 1, 2, 3, 4 і т.д.

  3. Для кожного відділу вибирайте тільки найдовший шлях до значення стовпця від кореня до вузла (connect by path) – в ньому будуть зібрані всі прізвища співробітників відділу.

Функція SYS_CONNECT_BY_PATH () буде повертати список зчеплених значень стовпця ENAME.

Цей запит виглядає так:
select deptno,
max(sys_connect_by_path
(ename, ” ” )) scbp
from (select deptno, ename,
row_number() over
(partition by deptno
order by ename) rn
from emp
)
start with rn = 1
connect by prior rn = rn-1
and prior deptno = deptno
group by deptno
order by deptno
/
DEPTNO SCBP
——— ———————————-
10 CLARK KING MILLER
20 ADAMS FORD JONES SCOTT …
30 ALLEN BLAKE JAMES MARTIN …

Зниження обсягу генерується журнальної інформації

Питання. У мене є PL / SQL-пакет, який копіює дані в наше сховище даних з множинних баз даних, використовуючи для цього зв’язок бази даних. У пакеті використовується масове зв’язування (пропозиція BULK_COLLECT) з обмеженням кількості рядків від 1000 до 2500, залежно від числа рядків у кожній таблиці. Транзакцію я фіксую за межами циклу, так що у мене тільки одна операція фіксації.

Адміністратори бази даних заявляють, що обсяг журнальної інформації немислимий, тому вони навіть повинні були збільшити дисковий простір, доступний серверу Oracle Database. Вони безумовно не говорили, що проблема пов’язана з моїм кодом, але вона з’явилася приблизно під час реалізації мого коду.

Як я можу контролювати або гарантувати під час написання коду, що журнализация оптимізована?

Відповідь. Наскільки це можливо намагайтеся використовувати одиночні SQL-оператори. Найбільший вплив, яке ви можете надати на генерацію журнальної інформації – обмеження обсягу роботи, виконуваної під час одного виклику.

Крім того, розгляньте також можливість виконання операцій прямої вставки (direct-path) з відключеною журналізацію. (Тільки погоджуйте свою роботу з вашими адміністраторами бази даних! Відразу після виконання цих операцій вони повинні створити резервну копію.)

Порівняйте різницю в обсягах генерується журнальної інформації при виконанні порядковий операцій – обробка “помалу” (slow-by-slow) – і одного SQL-оператора, показану на лістингу 5.







Лістинг 5: мінімізація обсягу генерується журнальної інформації.

SQL> create table t ( x int primary key, y char(10), z date );
Table created.
SQL > create index t_idx1 on t(y);
Index created.
SQL > create index t_idx2 on t(z);
Index created.
SQL > @mystat “redo size”
NAME VALUE
————- ————-
redo size 84538288
SQL > begin
2 for x in (select object_id a, “x” b, created c from all_objects)
3 loop
4 insert into t values ( x.a, x.b, x.c );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL > @mystat2
NAME VALUE DIFF
————- ————- ———-
redo size 144124840 59,586,552
SQL > truncate table t;
Table truncated.
SQL > @mystat “redo size”
SQL > set echo off
NAME VALUE
————- ————
redo size 144294508
SQL > begin
2 insert into t select object_id, “x”, created from all_objects;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL > @mystat2
NAME VALUE DIFF
————- ———– ———–
redo size 168114280 23,819,772

Тобто, при виконанні порядкової вставки ми маємо 59MB журнальної інформації, а при виконанні одного ефективного SQL-оператора – 23MB!

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


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

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

Ваш отзыв

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

*

*