SQL Tuning Advisor: хинти і результати настройки профілю, Інші СУБД, Бази даних, статті

Автор: Ілля Дєєв, Асоціація CBOSS, Oracle PL / SQL Developer Certified Associate


Розширене назву цієї статті: “Автоматичне налаштування запитів за допомогою SQL Tuning Adviser: отримання набору хінтів налаштованого профілю та перевірка результатів настройки”. У ній наводиться метод отримання набору хінтів, що відповідають профілю SQL-запиту, отриманого за допомогою пакета DBMS_SQLTUNE, а також розповідається про спосіб тестування налаштованих запитів.


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


В Oracle Database 11g опція Real Application Testing (RAT) включає функціонал SQL Performance Analyzer (SPA), призначений для тестування роботи запитів при зміні планів їх виконання (в результаті зміни параметрів ініціалізації, створення індексів, настройки профілів і т.п.). Однак, для багатьох розробників ще залишається актуальною налагодження та перевірка запитів в Oracle Database 10g. У статті наведено опис реалізації відносно простими засобами функціоналу, подібного SPA, пріменінельно до Oracle Database 10g.


Використання SQL Tuning Advisor


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


SQL Tuning Advisor – інструмент дуже корисний. Однак, як виявилося, він нерідко помиляється. Да-да! Як не дивно, засіб, призначений для поліпшення продуктивності, при неакуратному його використанні може приводити до погіршення продуктивності налаштованих запитів. Вперше довелося зіткнутися з цією проблемою при спробі настройки за допомогою DBMS_SQLTUNE одного із складних ієрархічних запитів, ретельно налаштованого раніше за допомогою хінтів.






Є одна тонкість в побудові плану ієрархічних запитів в Oracle 10g. За дивної причини навіть при наявності необхідних індексів в плані запиту фігурує FULL SCAN по таблиці, хоча значення відповідного параметра статистики table scan blocks gotten при виконанні не збільшується, як це зазвичай відбувається при реальному доступі через FULL SCAN. Очевидно, FULL SCAN виводиться лише в плані, а при виконанні запиту доступ до даних все ж відбувається за індексом. При налаштуванні запиту DBMS_SQLTUNE обманюється вартістю цього уявного FULL SCAN і пропонує інший план, вартість якого формально менше, але при виконанні призводить до збільшення числа логічних читань в порівнянні з вихідним варіантом запиту.


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


Невеликий приклад результатів настройки. На одній з тестових схем було налаштоване 64 запиту. Якби не відсівалися результати настройки запитів, які були налаштовані невдало, сумарний програш по всім обсягом читань налаштованих запитів був би приблизно в 5 разів! Проте, після аналізу показників статистики при реальному виконанні запитів були відсіяні результати помилковою налаштування по 36 запитам. У підсумку загальний обсяг читань скоротився в 3 рази. Даний аналіз не враховував кількість реальних виконань кожного запиту в системі (V $ SQL.EXECUTIONS), тобто розрахований мінімально можливий виграш, відповідний однократному виконання кожного запиту.


Вибір запитів для настройки


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



Отримання набору хінтів, що відповідають профілю запиту


Нерідко розробники прагнуть максимально стабілізувати план виконання запитів, захищаючись від різних несподіванок, що виникають при зміні версії Oracle, при зміні статистики, параметрів ініціалізації і т.п. Один із способів, що дозволяють зафіксувати план виконання, – застосування хінтів. Однак, часто при підборі правильного набору хінтів виникають труднощі. Іноді бувають ситуації, коли невірно підібрані хинти можуть погіршувати план виконання запиту. Цікавою можливістю в цьому випадку може стати отримання набору хінтів на основі налаштованого профілю запиту. Виявилося, що SQL Tuning Advisor при налаштуванні зберігає набір відповідних хінтів для налаштованого SQL-запиту у внутрішньому системному вигляді.


Набір хінтів для відповідного завдання налаштування з номером TASK_ID зберігається в стовпці OTHER_XML подання USER_SQLTUNE_PLANS в рядку стовпця ID, що має значення 1. Припустимо, що завдання з налаштування запиту називається “TEST1”. Тоді набір хінтів в системному вигляді можна отримати наступним запитом:

select extractValue(value(d), “/hint”) hint_text
from (select other_xml
from user_sqltune_plans
where task_id = (select task_id
from user_advisor_tasks
where task_name = “TEST1”)
and id = 1
and attribute = “Using SQL profile”) add_data,
table(XMLSequence(XMLType(add_data.other_xml).extract(“other_xml/outline_data/hint”))) d;

Результат виконання може бути приблизно таким:

USE_MERGE(@”SEL$1″ “E”@”SEL$1″)
LEADING(@”SEL$1” “D”@”SEL$1” “E”@”SEL$1″)
FULL(@”SEL$1” “E”@”SEL$1″)
INDEX(@”SEL$1” “D”@”SEL$1” (“DEPT”.”DEPTNO”))
OUTLINE_LEAF(@”SEL$1″)
ALL_ROWS
OPT_PARAM(“optimizer_index_caching” 70)
OPT_PARAM(“optimizer_index_cost_adj” 15)
OPTIMIZER_FEATURES_ENABLE(“10.2.0.4”)
IGNORE_OPTIM_EMBEDDED_HINTS

Після переведення в звичний вид хинти виглядають наступним чином:

USE_MERGE(E)
LEADING(D E)
FULL(E)
INDEX(D PK_DEPT)
ALL_ROWS

Варто зауважити, що при створенні плану кожного запиту, починаючи з Oracle 10g, в V $ SQL_PLAN.OTHER_XML в рядку з id = 1 також записується набір хінтів в системному вигляді.


Залишається перевести уявлення хінтів з системного виду в звичайний. Зокрема, необхідно перетворити позначення індексів у вигляді назви таблиці з перерахованих набором колонок, в звичайне, що представляє є власне ім’я індексу. Однією зі складностей є розстановка хінтів в необхідних місцях при налаштуванні складних запитів. Незручним для використання виявилося системне уявлення назв блоків запиту (блоки відповідають підзапиту). На допомогу прийшов хинт QB_NAME, що дозволяє задати для блоків запиту внутрішнє ім’я. Як виявилося, це ім’я зберігається в системному уявленні хінтів. Оскільки кожен хинт в системному уявленні прив’язується до відповідного блоку запиту, стало можливим розставити хинти в потрібних місцях запиту. При цьому довелося проігнорувати деякі хинти, які ставилися до проміжних наборам даних, одержуваних при виконанні запитів, наприклад, результатам з’єднання таблиць через HASH JOIN, які на рівні початкового тексту запиту не мають імені. Практика показала, що такі спрощення зазвичай проходять без негативних наслідків. Таким чином, маємо наступний порядок отримання набору хінтів та нового тексту SQL-запиту:



  1. Очищення тексту запиту від старих хінтів (для отримання нового тексту запиту з новими хинта), розстановка хінтів QB_NAME по блокам запиту. Для кожного підзапиту встановлюється власну назву блоку.

  2. Налаштування запиту за допомогою DBMS_SQLTUNE.

  3. Перевірка створення профілю.

  4. Якщо профіль створений, читання списку хінтів і перетворення їх в стандартний вигляд.

  5. Розстановка отриманих хінтів в потрібних місцях запиту.

Судячи з результатів настройки, при роботі DBMS_SQLTUNE ігноруються більшість заданих в тексті запиту хінтів, в тому числі ALL_ROWS і FIRST_ROWS. Але зберігає свою дію, наприклад, хинт QB_NAME. При цьому установка значення параметра OPTIMEZER_MODE надає безумовне вплив на результат настройки. Якщо профіль був налаштований для значення параметра OPTIMIZER_MODE, рівного FIRST_ROWS, то при зміні значення цього параметра на ALL_ROWS профіль використовуватися не буде.


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


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


Якщо необхідно лише перевірити результати настройки профілю в робочій системі (якщо набір хінтів не цікавить), з перерахованих вище пунктів залишаються тільки пункти 2 і 3.


Перевірка результатів настройки запитів реальним їх виконанням


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


В Oracle Database 10g відповідь на це питання спрощується тим фактом, що нарешті з’явився інтерфейс до значень BIND-змінних на рівні уявлень. По-перше, в V $ SQL і V $ SQLAREA з’явилося поле BIND_DATA типу RAW, в якому міститься набір значень BIND-змінних (читання можна здійснювати за допомогою DBMS_SQLTUNE.EXTRACT_BINDS). По-друге, з’явилося уявлення V $ SQL_BIND_CAPTURE, в якому містяться періодично оновлювані значення пов’язують змінних. Період оновлення BIND_DATA в секундах може встановлюватися недокументированним параметром _cursor_bind_capture_interval, значення якого по замовчуванням дорівнює 400 сек (проте, зміна значення даного параметра в бік зменшення може знизити продуктивність, тому в робочій системі цього робити не варто). [Примітка А.Бачіна, гл.редактора OM / RE: зміна значень недокументованих параметрів веде до припинення техпідтримки системи з боку Oracle на весь час їх дії.] Слід зазначити, що підхоплюються і відображаються лише ті BIND-змінні, які мають прості типи і використовуються в умовах WHERE і HAVING. BIND-змінні, використовувані тільки в списку вибраних стовпців, будуть мати значення NULL. Потрібно зазначити, що переглянути значення BIND-змінних не вдасться у випадку встановлення значення параметра STATISTICS_LEVEL в BASIC.


Крім перегляду значень змінних прив’язки в V $ SQL_BIND_CAPTURE і V $ SQL, в V $ SQL_PLAN.OTHER_XML у вмісті XML-елементу peeked_binds можна виявити значення змінних, які використовуються при побудові плану запиту. Використовуючи відомі значення змінних, можна створити динамічно виконуваний код для тестування SQL-запиту з використанням вибірки даних через BULK COLLECT у відповідну вимогу колекцію. Визначити набір повертаються запитом стовпців і їх типів можна за допомогою пакета DBMS_SQL, в якому є відповідна процедура DESCRIBE_COLUMNS2. Слід зазначити, що в разі потрапляння в список стовпців запиту стовпця з призначеним для користувача типом даних, цей тип буде визначений як XMLTYPE, що призведе до помилки невідповідності типів при спробі вибрати дані за допомогою сформованого автоматично скрипта. Перевірити налаштовані варіанти таких запитів доведеться, явно вказавши вручну необхідний тип замість типу XMLTYPE. Звичайно, при тестуванні запитів потрібно враховувати особливості обробки даних. У разі зміни обсягу та вмісту таблиць (наприклад, при очищенні таблиць, дані в які спочатку завантажуються з зовнішніх файлів, а потім очищаються після обробки) настройка запитів в промисловій системі може привести до абсолютно неправильним результатам.


Для запуску установки та виконання SQL в автоматичному режимі корисно використовувати наступні параметри:



Після виконання запиту і витягання результату легко проаналізувати показники статистики, порівнявши характеристики виконання в різних варіантах:



  1. Вихідний запит.

  2. Вихідний запит без хінтів, якщо хинти були присутні в первісному запиті. (Деякі SQL-запити можуть мати неоптимальний набір хінтів, марно обмежують свободу дій оптимізатора.)

  3. Запит з налаштованим і включеним профілем.

  4. Запит з набором отриманих з профілю хінтів (для перевірки відповідності профілю та набору хінтів).

Не у всіх випадках потрібно проводити всі експерименти. Якщо профіль не був налаштований за допомогою DBMS_SQLTUNE, то виконання запиту у варіантах 3 і 4 виключається. Якщо вихідний запит не мав хінтів і для нього не був налаштований профіль, результати виконання немає з чим порівнювати.


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


Резюме.


Розроблена на основі описаних принципів утиліта дозволяє отримати за результатами роботи SQL Tuning Advisor текст SQL-запитів з набором хінтів, що задає найбільш оптимальний, з точки зору оптимізатора, план виконання. Отримувані набори хінтів також можуть послужити хорошими навчальними прикладами в разі активного використання хінтів при налаштуванні запитів. Реальне виконання запитів до і після настройки дозволяє чисельно оцінити виграш від настройки за ключовими показниками (використання CPU, обсяг читань, час виконання). Однак, необхідно мати на увазі переваги безпосереднього використання SQL Tuning Advisor на робочій базі даних: налаштування запитів на реальних даних, можливість зміни плану в кращу сторону при змінах в схемі даних, зміні параметрів ініціалізації або розподілу даних у таблицях. У разі налаштування запитів на тестовій системі необхідно відтворити реальні обсяги даних або хоча б реальне відносне розподіл даних в таблицях, запити до яких настроюються. Також бажано тестувати настройку запитів з різними значеннями BIND-змінних.


Крім аналізу ефективності профілів, описані підходи тестування можуть бути корисні при дослідженні впливу різних змін на виконання запитів. Можна створювати набори спільно тестованих запитів, групуючи їх по модулям, по підсистемам, за таблицями, до яких відбуваються обігу та іншим довільним ознаками. Збереження параметрів статистики і значень параметрів, що впливають на роботу оптимізатора при виконанні запитів, може дати картину впливу зміни оточення на споживання ними ресурсів. Даний функціонал реалізований як в SPA в Oracle Database 11g, так і в утилітах сторонніх виробників, наприклад, Quest SQL Optimizer for Oracle. Якщо ж виникають якісь особливі вимоги, можна реалізувати подібну систему своїми силами.

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


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

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

Ваш отзыв

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

*

*