Використання збережених шаблонів при налаштуванні програм з недоступним вихідним кодом, Інші СУБД, Бази даних, статті

У даній статті описується один з багатьох аспектів використання збережених шаблонів (stored outlines) при налаштуванні продуктивності додатків, що працюють з СУБД Oracle. Зокрема, наводиться приклад їх використання для налаштування програм, до вихідного коду яких немає доступу. Наведений приклад був перевірений в Oracle 9i Release 2. Для виконання SQL-операторів використовувалася утиліта SQL * Plus.


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


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


Вирішити подібні проблеми можна за допомогою збережених шаблонів.


Непогане опис використання збережених шаблонів для стабілізації плану виконання SQL-операторів представлено в книзі Тома Кайта “Oracle для професіоналів” (Thomas Kyte, “Expert One on One: Oracle”), в перекладі В. Кравчука, а так само на сайті (http://ln.com.ua/ ~ openxs / projects / oracle /) автора перекладу.


Перейдемо до прикладу. Почнемо з постановки завдання. Припустимо, є SQL-оператор, що містить підказку /*+ RULE*/. Наша задача – позбутися від цієї підказки.


Підготуємо тестову середу. Створимо таблицю, складовою індекс і зберемо статистичну інформацію. Таблиця містить набір словників, кожен з яких описує якийсь набір сутностей:



SQL> create table t1 (
2 dict_id int,
3 itemid int,
4 name varchar2(100));
Таблиця створена.
SQL> create index indx_t1
2 on t1(dict_id, itemid);
Індекс створений.
SQL> analyze table t1 estimate statistics;
Таблиця проаналізована.



Включимо показ плану виконання SQL виразів:


SQL> set autotrace on explain


Створимо зв’язувану змінну (bind variable), проініціалізіруем її і виконаємо оптимізуються запит:


SQL> var itemid number
SQL> begin :itemid:= 0; end;
2 /
Процедура PL / SQL успішно завершена.
SQL> select /*+ rule*/ *
2 from t1
3 where itemid = :itemid;
рядки не вибрані
План виконання
———————————————————-
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF “T1”
Процедура PL / SQL успішно завершена.



План виконання показує повний перегляд таблиці (full table scan, FTS).


Виконаємо той же запит, але “вимкнувши” підказку (приберемо “+“):


SQL> select /* rule*/ *
2 from t1
3 where itemid = :itemid;
рядки не вибрані
План виконання
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=78)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “T1” (Cost=2 Card=1 Bytes=78)
2 1 INDEX (SKIP SCAN) OF “INDX_T1” (NON-UNIQUE)



План виконання показує доступ до даних таблиці за допомогою індексу INDX_T1. Доступ в межах індексу здійснюється як INDEX SCIP SCAN, Вперше представлений в Oracle9i. Припустимо, набір даних у таблиці така, що цей план набагато краще першого. До нього і будемо прагне.


Для подальшої роботи нам будуть потрібні наступні системні привілеї:



Виконаємо наступне:


SQL> alter session set create_stored_outlines = healthy_plans;
Сеанс змінений.



У цьому операторі HEALTHY_PLANS– Це ім’я категорії, з якої будуть пов’язані наші шаблони.


Далі, виконаємо по черзі два запити. Перший – “проблемний”, той який необхідно оптимізувати (з підказкою /*+ RULE*/). Другий – який ми прооптімізіровалі, “відключивши” підказку. Однак перед виконанням запитів нам необхідно відключити відображення планів виконання запитів. Це потрібно, щоб Oracle перехопив тільки наші два запити (інакше будуть перехоплені звернення до таблиці PLAN_TABLE, Яка містить плани виконання запитів):


SQL> set autotrace off
SQL> select /*+ rule*/ *
2 from t1
3 where itemid = :itemid;
рядки не вибрані
SQL>
SQL> select /* rule*/ *
2 from t1
3 where itemid = :itemid;
рядки не вибрані



На даному етапі важливо відзначити, що, на відміну від Oracle 8i, де порівняння SQL-запитів з їх аналогами в збережених шаблонах відбувається посимвольно, в Oracle 9i воно не має такого жорсткого критерію. Тому, скажімо, запит:


select /*+ rule*/ *
from t1
where itemid = :itemid;



з точки зору використання збережених шаблонів в Oracle 9i, аналогічний запит:


select /*+ RULE*/ * from t1 where
ItemID = :itemid;



тоді як в Oracle 8i це було б невірно.


Отже, продовжимо. Відключимо автоматичне створення збережених шаблонів:


SQL> alter session set create_stored_outlines = false;
Сеанс змінений.



Дивимося, що вийшло (для легкості читання встановимо розмір буфера відображення LONG-Полів рівним 15):


SQL> set long 15
SQL> select ol_name, sql_text from outln.ol$
2 where category = “HEALTHY_PLANS”;
OL_NAME SQL_TEXT
—————————— —————
SYS_OUTLINE_031028123825493 select /*+ rule
SYS_OUTLINE_031028123825813 select /* rule*



Задамо отриманим шаблонам інформативні імена:


SQL> alter outline SYS_OUTLINE_031028123825493 rename to with_plus;
Варіант змінений.
SQL> alter outline SYS_OUTLINE_031028123825813 rename to without_plus;
Варіант змінений.
SQL> select ol_name, sql_text from outln.ol$
2 where category = “HEALTHY_PLANS”;
OL_NAME SQL_TEXT
—————————— —————————-
WITH_PLUS select /*+ rule
WITHOUT_PLUS select /* rule*



Підказки збережених шаблонів знаходяться в таблиці ol$hints схеми OUTLN:


SQL> select ol_name, hint#, hint_text from outln.ol$hints
2 where category = “HEALTHY_PLANS”
3 order by ol_name desc, hint#;
OL_NAME HINT# HINT_TEXT
———— ——- ———————–
WITH_PLUS 1 NO_EXPAND
WITH_PLUS 2 ORDERED
WITH_PLUS 3 NO_FACT(T1)
WITH_PLUS 4 FULL(T1)
WITH_PLUS 5 NOREWRITE
WITH_PLUS 6 NOREWRITE
WITH_PLUS 7 RULE
WITHOUT_PLUS 1 NO_EXPAND
WITHOUT_PLUS 2 ORDERED
WITHOUT_PLUS 3 NO_FACT(T1)
WITHOUT_PLUS 4 INDEX(T1 INDX_T1)
WITHOUT_PLUS 5 NOREWRITE
WITHOUT_PLUS 6 NOREWRITE
13 рядків вибрано.



У вищезазначених російськомовних джерелах описується кілька прийомів “обману” вартісного оптимізатора (Cost Based Optimizer – CBO), вдавшись до яких можна “змусити” оптимізатор побудувати потрібний нам план. У цій статті я запропоную вам ще один подібний прийом.


Все досить просто. Що нам потрібно? Нам потрібно, щоб при виконанні запиту з підказкою оптимізатор використовував план із запиту з “відключеною” підказкою. Для цього ми просто поміняємо підказки в шаблонах. Робимо це шляхом зміни імен збережених шаблонів:


SQL> update outln.ol$
2 set ol_name = “RIGHT_PLAN”
3 where ol_name = “WITH_PLUS”;
1 рядок оновлена.
SQL> update outln.ol$hints
2 set ol_name = “RIGHT_PLAN”
3 where ol_name = “WITHOUT_PLUS”;
6 рядків оновлено.
SQL>
SQL> update outln.ol$hints
2 set ol_name = “WITHOUT_PLUS”
3 where ol_name = “WITH_PLUS”;
7 рядків оновлено.



Що став непотрібним шаблон WITHOUT_PLUS видаляємо (фіксація попередніх змін нам не потрібно, тому що SQL-оператор DROP відноситься до числа змінюють словник даних Oracle (Data Definition Language, DDL), що викликає неявну фіксацію попередньої транзакції):


SQL> drop outline WITHOUT_PLUS;
Варіант видалений.



Ось, власне, і все. Тепер можна перевірити наш “проблемний” запит. Для чистоти експерименту, подивимося ще раз на план нашого “проблемного” запиту:


SQL> set autotrace on explain
SQL> select /*+ rule*/ *
2 from t1
3 where itemid = :itemid;
рядки не вибрані
План виконання
———————————————————-
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF “T1”



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


SQL> alter session set use_stored_outlines=HEALTHY_PLANS;
Сеанс змінений.



І виконаємо ще раз все той же “проблемний” запит:


SQL> select /*+ rule*/ *
2 from t1
3 where itemid = :itemid;
рядки не вибрані
План виконання
———————————————————-
0 SELECT STATEMENT Optimizer=HINT: RULE (Cost=2 Card=1 Bytes=78)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “T1” (Cost=2 Card=1 Bytes=78)
2 1 INDEX (SKIP SCAN) OF “INDX_T1” (NON-UNIQUE)



Мета досягнута.


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


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


Проводячи дослідження в даному напрямі, я помітив цікаву особливість. Ось що я зробив. Я видалив індекс таблиці t1. Перевірив реакцію оптимізатора, – він проігнорував зберігається шаблон (як і було зазначено вище). Знову створив індекс з тим же ім’ям і на ті ж поля. І ось тут – найцікавіше! Так як я не перезбирати статистичну інформацію про таблицю після створення індексу, виконання запиту “без плюсик” привело до повного перегляду таблиці, а виконання нашого “проблемного” запиту показало прекрасний план, взятий вартісним оптимізатором з нашого шаблону. Ось такі справи :). Не забувайте збирати “статистику”!

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


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

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

Ваш отзыв

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

*

*