Самоналагоджувальна база даних: керовані програми та налаштування SQL. Частина 2, Інтеграція додатків і даних, Бази даних, статті


Об’єкти налаштування STS (SQL Tuning Set)

ADDM автоматично ідентифікує найбільш навантажені SQL-пропозиції для того, щоб користувач їх вибрав і настроїв. AWR дозволяє вибирати головні (top) SQL-пропозиції в інтервалі часу. Однак користувачеві може захотітися налаштовувати набір обраних SQL-пропозицій в тому порядку, який встановить сам користувач. Хороший приклад такої ситуації, коли розробник знаходиться в процесі розробки і випробування нових SQL-пропозицій. До тих пір, поки якесь SQL-пропозиція не вбудовано в систему, звичайні SQL-джерела (наприклад, AWR) застосовуватися не можуть. Таким чином, потрібний для користувача механізм для створення його власної робочої SQL-навантаження та застосування до неї Automatic SQL Tunung. Таким механізмом є SQL Tuning Set (STS). Підтримка STS була введена в Oracle 10g для полегшення управління набором SQL-пропозицій, тобто управління [набором] як єдиним модулем.

SQL Tuning Set – це база даних, яка зберігає одне або більше SQL-пропозиція разом з їх статистикою і станом виконання і, можливо, з користувальницьким ранжируванням пріоритетів. SQL-пропозиція може бути завантажено в SQL Tuning Set з різних SQL-джерел. SQL-джерела включають Automatic Workload Repository (AWR) курсорний кеш і вибраний SQL, що надається користувачем. Рисунок 5 показує модель типового застосування STS. STS створюється і потім завантажується з SQL-пропозицією з одного з SQL-джерел. На малюнку показано, що SQL-пропозиції вибираються, ранжуються і фільтруються, перед тим як завантажитися в SQL Tuning Set.


Рисунок 5. Вид моделі SQL Tuning Set

Контекст виконання, збережений з кожним SQL-пропозицією, включає: схему користувача, ім’я модуля програми та події, список пов’язаних значень і середу трансляції курсора. Зберігається статистика виконання, включаючи минулий час, процесорний час, логічні читання буферів, фізичні читання дисків, число оброблених рядків, вибірок курсорів, число виконань, число закінчилися виконань, вартість [Плану] оптимізатора та тип команди. Фільтрація SQL-пропозицій може бути виконана, використовуючи ім’я модуля програми і дію, а також будь статистики виконання. З цього випливає, що ранжування SQL-пропозицій може бути виконано на основі будь-якої комбінації статистики виконання.

SQL Tuning Set може постійно зберігатися в базі даних. Зміст SQL Tuning Set може оновлюватися, начебто STS – це об’єкт, і може управлятися за допомогою процедур пакету DBMS_SQLTUNE. Одного разу завантажений STS можна передавати на введення в SQL Tuning Advisor, який потім застосує автоматичну настройку SQL-пропозицій, схильних до впливу інших вхідних параметрів, специфікованих користувачем. Інші вводи включають: ліміт часу, рамки аналізу налаштування і т.д. Таким чином, SQL Tuning Set надає собою потужний метод збору і збереження цікавить набору SQL-пропозицій, поряд з великим скупченням інформації, яка допомагає в процесі настройки.

Інтерфейс настроювання SQL Tuning

Утиліта Enterprise Manager (ЕМ) може бути використана для ідентифікації високого навантаження і головних SQL-пропозицій. В Enterprise Manager є кілька місць, з яких може бути запущений SQL Tuning Advisor з ідентифікованим SQL-пропозицією (-ями) або з SQL Tuning Set.

Налаштування ADDM SQL

Наступний EM-екран показує ідентифікацію високо навантажених SQL-пропозицій за допомогою Automatic Database Diagnostics Monitor (ADDM). Як відомо, кожне з цих високо навантажених SQL-пропозицій споживають істотну порцію одного або більше системних ресурсів, типу часу CPU, логічних читань буферів (buffer gets), фізичних читань дисків і т.д. Цей екран дозволяє користувачеві запускати SQL Tuning Advisor для вибраного високо навантаженого SQL-пропозиції.


Малюнок 6. ADDM знаходить високо навантажені SQL-пропозиції

Налаштування головних SQL

Інший SQL-джерело – це список головних (top) SQL-пропозицій на EM-екрані, що показано на малюнку 7.

Список головних SQL-пропозицій ідентифікується переглядом сукупності їх статистик виконання протягом обраного вікна часу. Головні SQL-пропозиції можуть бути ранжовані на основі відповідної статистики, наприклад, використання CPU. Користувач може вибрати одне або більше головне SQL-пропозиція, ідентифікованих своїми ID, і застосувати до них SQL Tuning Advisor.


Малюнок 7. Головні SQL-пропозиції

Налаштування STS

Enterprise Manager також дозволяє вам переглядати різні SQL Tuning Set, створені різними користувачами. STS може бути створений зі списку головних SQL-пропозицій, або вибором SQL-пропозиції з діапазону знімків, створених Automatic Workload Reporitory (AWR), або створенням власного SQL-пропозиції. Наступний екран Enterprise Manger показує, як запустити SQL Tuning Advisor на вибраному STS.


Рисунок 8. Екран SQL Tuning Sets

Опції настройки

Якщо SQL Tuning Advisor вже запущений, то Enterprise Manager буде автоматично створювати задачу настройки, пропоновану користувачем, якщо він має для цього відповідну привілей ADVISOR. Enterprise Manager за замовчуванням показує задачу настройки з автоматичними значеннями в SQL Tuning Options, екран якого зображений нижче. На цьому екрані користувач може скористатися варіантами для зміни автоматичних значень за замовчуванням, що мають відношення до задачі настройки.


Малюнок 9. Опції SQL Tuning

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

Огляд рекомендацій SQL Tuning

Як тільки задача настройки завершується, можуть бути переглянуті рекомендації, згенеровані SQL Tuning Advisor. Enterprise Manager показує як огляд рекомендацій, так і їх подробиці. Наступний екран демонструє короткий огляд рекомендацій SQL Tuning Advisor для одного або декількох параметрів, SQL-пропозицій. Як показано нижче, є тільки одна рекомендація для створення SQL Profile для SQL-пропозиції. Якщо ви вибираєте SQL-пропозицію і натискаєте кнопку View Recomendations, то Enterprise Manager покаже подробиці рекомендації.


Малюнок 10. Огляд рекомендацій SQL Tuning

Наступний екран показує подробиці, пов’язані з SQL Profile, і коефіцієнт поліпшення роботи, якщо ця рекомендація буде прийнята. Ви можете прийняти цю рекомендацію, натиснувши на кнопку Implement, створюючи, таким чином, SQL Profile.


Малюнок 11. Подробиці рекомендацій SQL Tuning

Пакет DBMS_SQLTUNE

Хоча для Automatic SQL Tuning головним інтерфейсом є Oracle Enterprise Manager, для налаштування SQL-пропозицій може використовуватися і інтерфейс командного рядка пакета DBMS_SQLTUNE. DBMS_SQLTUNE – це новий пакет, доданий в Oracle10g, І він містить необхідні API для використання можливостей Automatic SQL Tuning, включаючи завдання для виконання автоматичної настройки пропозицій і управління SQL Profile і SQL Tuning Sets.

Налаштування управління завданнями (Task Management)

Важливо зауважити, що SQL Tuning Advisor, подібно всім іншим керуючим порадникам, сформований на основі загального Advisor Framework. Advisor Framework забезпечує загальну підтримку інфраструктури для побудови, збереження та пошуку рад, згенерованих за допомогою різних керуючих можливостей, включаючи SQL Tuning Advisor.Поетому всі процедури налаштування SQL працюють з об’єктами завдань порадника, названими завданнями налаштування. Це означає, що для виконання автоматичної настройки повинна бути створена завдання налаштування. Використання SQL-процедур настройки, включаючи створення задач настройки, вимагає наявності привілеї ADVISOR.

Для виконання автоматичної SQL-настройки, використовуючи пакет DBMS_SQLTUNE, першим кроком завжди буде створення завдання настройки викликом процедури create_tuning_task. Ця процедура створює завдання порадника і задає їй відповідні параметри, відповідно до встановлених користувачем вхідним параметрам.

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

Наступний приклад показує одну форму процедури create_tuning_task, яка дозволяє передавати текст SQL-пропозицією безпосередньо, як параметр. У цьому прикладі текст пропозиції передається, як CLOB.

create_tuning_task(
sql_text => ‘select * from emp where emp_id = :bnd”,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => ‘scott”,
scope => ‘comprehensive”,
time_limit => 60,
task_name => ‘my_sql_tuning_task”,
description => ‘task to tune a query on a specified employee”);

У цьому прикладі цільове пропозицію використовує пов’язану змінну bnd, значення якої (100) – це число, що передається як функціональний аргумент типу SQL_BINDS. SQL_BINDS – це новий тип об’єктів, введений в Oracle 10g. Параметр scott надає ім’я схеми, в якій аналізується подання. Можливості настройки завдання передаються, як comprehensive, щоб повідомити SQL Tuning Advisor, що потрібно зробити повний аналіз, включаючи генерацію SQL Profile. І, нарешті, аргумент 60 – Це межа за часом в секундах для налаштування SQL-пропозиції.

Є дві інші форми цієї процедури, доступні для адресата специфічного SQL-пропозиції, вибраного або з кеша курсорів, або з AutomaticWorkload Repository (AWR). У будь-якому випадку пропозиція буде ідентифіковано передачею його SQL_ID замість SQL-тексту.

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

execute_tuning_task(task_name => ‘my_sql_tuning_task”);

В будь-який час, після того як почалося виконання, користувач може використовувати відповідну процедуру порадника для скасування, переривання або скидання завдання. Користувач може також перевірити стан завдання шляхом перегляду інформації, розміщеної у робочий уявлення DBA_ADVISOR_LOG, або може запросити подання V $ SESSIO_LOGOPS для відображення інформації про прогрес виконання завдання. Ця інформація включає час, що залишився на виконання, кількість результатів, вигоду і число пропозицій, використаних SQL Tuning Set для настройки.

Коли завдання налаштування закінчена, результати установки можуть бути візуалізовані, викликом процедури report_tuning_task, Як показано нижче:

set long 10000;
select report_tuning_task(task_name => ‘my_sql_tuning_task”)
from dual;

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

Результати Automatic SQL Tuning завжди можуть бути переглянуті, використовуючи структурні уявлення порадника АБД, такі як: DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_RATIONALE і т.д. Крім цих уявлень, які є загальними для всіх порадників в Oracle 10 g, Automatic SQL Tuning розширює структуру, додаючи нові уявлення, які можуть відображати специфічну інформацію SQL-налаштувань, таку як SQL-статистики, пов’язані присвоювання та плани виконання. Для перевірки таких результатів користувач може запросити уявлення DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS і DBA_SQLTUNE_PLANS.

Управління SQL Profile

Процедури для управління SQL Profile також є частиною пакета DBMS_SQLTUNE. Коли SQL Profile рекомендується SQL Tuning Advisor, то SQL Profile може бути створений викликом процедури accept_sql_profile, Яка збереже його в словнику даних. Для створення SQL Profile потрібно привілей CREATE ANY SQL PROFILE. Одного разу створений SQL Profile буде автоматично застосовуватися до всіх наступних виконанням такого ж SQL-пропозиції. Наприклад, наступний виклик процедури зберігає SQL Profile, вироблений автоматичною настройкою SQL-пропозиції, пов’язаного із завданням настройки my_sql_tuning_task. У цьому прикладі SQL Profile отримує ім’я my_sql_profile.

accept_sql_profile(
task_name => ‘my_sql_tuning_task”,
name => ‘my_sql_profile”);

Інформація про SQL Profile може бути отримана через подання DBA_SQL_PROFILES. Користувач може також змінити атрибути існуючого SQL Profile, виконуючи процедуру alter_sql_profile. Щоб зробити це, потрібно привілей ALTER ANY SQL PROFILE.

У наступному прикладі my_sql_profile атрибут стану SQL Profile зміниться на disabled, Який означає, що SQL Profile більше не буде використовуватися для генерації плану виконання відповідного SQL-пропозиції.

alter_sql_profile(
name => ‘my_sql_profile”,
attribute_name => ‘status”,
value => ‘disabled”);

Інші атрибути SQL Profile, які можуть бути змінені: ім’я, опис і категорія. Нарешті, SQL Profile може бути видалений зі словника даних, використовуючи процедуру drop_sql_profile. Це вимагає привілеї DROP ANY SQL PROFILE.

Управління SQL Tuning Set

Типовий сценарій використання пакета DBMS_SQLTUNE для SQL Tuning Set (або просто sqlset) включає в себе створення нового SQL Tuning Set, завантажуючи його набором високо навантажених SQL-пропозицій, вибираючи і переглядаючи його вміст для ручного аналізу та подальшого модернизирования і вибору, потім запуск SQL Tuning Advisor для автоматичної настройки всіх пропозицій в SQL Tuning Set, і, нарешті, виключення SQL Tuning Set після виконання рекомендацій SQL Tuning Advisor.

У наступному прикладі процедура create_sqlset створює SQL Tuning Set з ім’ям my_sql_tuning_set, Який може бути використаний для завантаження вводу-виводу інтенсивних SQL-пропозицій, зібраних протягом певного періоду часу.

create_sqlset(
sqlset_name => “my_sql_tuning_set”,
description => “I/O intensive workload”);

Ця процедура створює в базі даних порожній SQL Tuning Set. Зверніть увагу на те, що для виконання процедури SQL Tuning Set користувач повинен мати привілей ADMINISTER SQL TUNING SET або ADMINISTER ANY SQL TUNING SET.

Після створення SQL Tuning Set процедура load_sqlset може бути використана для заповнення його вибраними SQL-пропозиціями. Стандартний джерело заповнення SQL Tuning Set – це Automatic Workload Repository (AWR), кеш курсорів або інший SQL Tuning Set, який був створений або завантажений раніше. Для кожного з цих джерел є зумовлені табличні функції, які можуть бути використані для вилучення і фільтрування вихідного вмісту перед завантаженням в новий SQL Tuning Set.

Наприклад, наступні виклики процедури використовуються для завантаження my_sql_tuning_set базової рядка AWR, званої “peak baseline”, Вибираючи тільки ті SQL-пропозиції, які були виконані не менше 10 разів, і які мають відношення (disk-reads/buffer-gets) більше 50% протягом базового періоду [часу]. SQL-пропозиції упорядковуються по відношенню (disk-reads/buffer-gets) і вибираються тільки 30 кращих SQL-пропозицій.

— open a ref cursor to select from the specified baseline
open baseline_ref_cursor for
select value(p)
from table (dbms_sqltune.select_baseline(
‘peak baseline”,
‘executions >= 10 and disk_reads/buffer_gets >= 0.5″,
null,
disk_reads/buffer_gets,
null, null, null,
30)) p;
— load statements and their stats from the baseline into the STS
dbms_sqltune.load_sqlset(
sqlset_name => “my_sql_tuning_set”,
populate_cursor => baseline_cur);

Тепер, коли SQL Tuning Set був створений і заповнений, АБД може переглянути SQL-пропозиція в SQL Tuning Set, використовуючи процедуру select_sqlset, як показано нижче:

SELECT * from TABLE(select_sqlset(
“my_sql_tuning_set”,
“(disk_reads/buffer_gets) >= 0.75”));

У цьому прикладі були відображені тільки SQL-пропозиції з відношенням (disk-reads/buffer-gets)> 75%. Подробиці SQL Tuning Set, який був створений і завантажений, можуть бути переглянуті, використовуючи подання АБД DBA_SQLSET, DBA_SQLSET_STATEMENTS і DBA_SQLSET_BINDS.

SQL-пропозиції можуть також бути модифіковані та видалені з SQL Tuning Set, заснованого на умовах пошуку. Наприклад, наступна процедура delete_sqlset видалить з my_sql_tuning_set всі SQL-пропозиції, які були виконані менше, ніж 50 разів.

delete_sqlset(sqlset_name  => “my_sql_tuning_set”,
basic_filter => “executions < 50”);

Нарешті, коли SQL Tuning Set більше не потрібний (наприклад, після настройки всіх інструкцій він містить і здійснює необхідні рекомендації) він може бути видалений процедурою drop_sqlset, як показано далі:

drop_sqlset(sqlset_name => “my_sql_tuning_set”);

Висновок

У цій статті ми описали керуючий компонент Automatic SQL Tuning, який був доданий в Oracle 10g. Automatic SQL Tuning забезпечує автоматичну настройку SQL-пропозицій у вигляді набору всебічних рекомендацій з налаштування. Він тісно пов’язаний з оптимізатором запитів. Фактично, оптимізатор запитів працює в режимі автоматичної настройки і генерує рекомендації з налаштування. Коли це буде потрібно, він також може сформувати SQL Profile на додаток до рекомендацій. Користувач може вибрати здійснювані рекомендації, включаючи SQL Profile. Одного разу створений SQL Profile буде використовуватися оптимізатором запитів для генерування добре налагоджених планів для відповідного SQL-пропозиції. Об’єкт настройки, що викликав SQL Tuning Set, представляється для здійснення можливості користувача створювати власну SQL-навантаження для цільової установки. Інтерфейс для Automatic SQL Tuning був створений, використовуючи Enterprise Manager, з можливостями вибору з різних SQL-джерел та налаштування SQL-пропозицій з різними можливостями налаштування.

Ми завершуємо цю статтю, показавши, як суттєво Automatic SQL Tuning спрощує процес настройки. Загальна проблема спостережень – це зниження ефективності SQL-пропозицій під час роботи, так як через якийсь час збільшується кількість даних. Для SQL-пропозицій, впроваджених в пакетні програми, наступна таблиця порівнює кроки налаштування SQL, виконувані в Oracle 9i і Oracle 10g.






































Кроки Oracle 9i  Oracle 10g 
1 Отримання плану виконання Запускає SQL Tuning Advisor
2 Перевірка об’єктів запиту і їх розмір Виконання рекомендацій
3 Перегляд і порівняння статистики плану виконання зі статистикою виконання (зберігається в представленні V $ SQL)  
4 Ідентифікація як ніби це проблема “first rows”, тому що відображаються лише недавні дані, незважаючи на запит до давньої хронології  
5 Контакт з виробником програми  
6 Підготовка тестового варіанту для виробника  
7 Отримання від виробника виправлення “first rows”  
8

Установка виправлення в наступному циклі обслуговування

 

Як вище показано в таблиці, зусилля і час, витрачені експертом-настроювачем на цю досить звичайну задачу в Oracle 9i, Значно більше порівняно з Oracle 10g. Крім того, в Oracle 9i клієнт для виправлення повинен чекати [реакції] виробника програми, що може зайняти тижні і місяці, тоді як в Oracle 10g дозвіл проблем [слід] негайно. Automatic SQL Tuning пропонує для настройки додатків комплексні, легкі у використання рішення, які можуть однаково ефективно бути використані, як новачком, так і досвідченим користувачем.


Бенуа Дагервілл, Муджес А.Мінхас, Халед Ягуб, ченцем зайти, Ченцям Зіауддін, Корпорація Oracle
(Benoit Dageville, Mughees A. Minhas, Khaled Yagoub, Mohamed Zait, Mohamed Ziauddin, all – Oracle Corp)

Частина I

Джерело: Доповідь на Oracle World-2003, Paris,
“The Self-Managing Database: Guided Application & SQL Tuning” – Формат Word, 512 КБ
(Файл з оригіналом доповіді можна також завантажити тут)

Переклад: Oracle Magazine RE

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


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

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

Ваш отзыв

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

*

*