Моніторинг продуктивності та аналіз подій очікування

при використанні СУБД Oracle у Unix

Моніторинг продуктивності бази даних та аналіз подій очікування

Щоб підвищити продуктивність СУБД Oracle, необхідно проаналізувати події очікування Oracle, щоб визначити вузькі місця та налаштувати параметри бази даних. Підвищення ефективності обробки в базі даних Oracle підвищує продуктивність усіх систем та програм, що використовують цю базу даних. У статті демонструється використання системних утиліт для ефективного збирання та аналізу статистики продуктивності бази даних Oracle з метою визначення проблем.

На продуктивність бази даних Oracle впливає багато факторів. Зокрема, значний вплив на продуктивність надають фактори, наведені нижче.

Неефективні SQL-оператори негативно впливають на продуктивність

За допомогою запитуselect * from employee витягуємо всі записи з таблиці. Знаходимо у результатах конкретного працівника. Якщо результат запиту містить багато записів, процес буде повільним.
Використовуємо запитselect * from employee where employee_name='employee1'. Цей оператор звужує пошук та прискорює процес.
Використовуємо запитselect * from employee where employee_id=123. Оскільки employee_id є первинним ключем, цей запит буде найшвидшим.

Щоб створити найефективніший SQL-оператор, використовуйте такі рекомендації:

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

Неефективна конфігурація бази даних негативно впливає на продуктивність

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

Параметр PCTFREE

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

Параметр PCTUSED

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

Неадекватне виділення пам'яті System Global Area (SGA) негативно впливає на продуктивність

Системна глобальна область (System Global Area - SGA) є групою загальних структур пам'яті, що містять дані і керуючу інформацію для одного екземпляра бази даних Oracle. Користувачі, що одночасно підключилися до одного і того ж екземпляра, спільно використовують дані в SGA екземпляра. Тому SGA іноді називають загальною глобальною галуззю. Для забезпечення ефективної продуктивності бази даних необхідно визначити оптимальний розмір пам'яті SGA та налаштувати її відповідним чином. Занадто невелика пам'ять може значно знизити продуктивність бази даних.

Події очікування негативно впливають на продуктивність

Події очікування відбуваються під час опрацювання запитів. Запит читання/запису до бази даних Oracle викликає кілька процесів. Ці процеси можуть зіткнутися з проблемами обладнання, програмного забезпечення, операційної системи або конфігурації, що призведе до зупинення або уповільнення обробки. Такі блокуючі чинники називаються подіями очікування. Для ефективної роботи бази даних необхідно звести до мінімуму використання операторів wait. Слід проаналізувати всі оператори wait, що виконуються, і виправити їх, якщо час очікування занадто великий. Однак, повністю усунути всі події очікування неможливо.

Oracle Database 11g має більше 1000 подій очікування, які можуть спричинити затримки обробки запиту. Ці події очікування прийнято поділяти на такі групи:

Cluster (кластер)
Network (мережа)
Administration (адміністрування)
Configuration (конфігурація)
Commit (фіксація)
Application (додаток)
Concurrency(паралелізм)
System I/O (системне введення/виведення)
User I/O (введення/виведення користувача)
CPU (процесор)

Використання Oracle Enterprise Manager для аналізу подій очікування

Для моніторингу подій очікування під час обробки даних використовується Oracle Enterprise Manager (OEM). OEM графічно показує стан бази даних під час обробки. Він також надає докладний аналітичний звіт, в якому можна перейти до кожної події та знайти відповідні SQL-оператори (див. рис. 1). У легенді справа показані типи подій очікування.

Рисунок 1. Приклад знімка екрана Oracle Enterprise Manager

На малюнку 2 показано детальне уявлення очікування типу User I/O для даних, які використані на малюнку 1. НатиснітьUser I/O в OEM, щоб перейти на сторінку відомостей про події очікування типу User I/O.

Малюнок 2. Очікування активних сеансів

Аналіз подій очікування за допомогою shell-сценарію

Можна зібрати статистику подій очікування бази даних Oracle без використання інструментальних засобів, скориставшись shell-сценарієм та вбудованими системними утилітами. Щоб зібрати та проаналізувати статистику для виявлення джерела вузьких місць та виправлення проблем за підтримки адміністратора бази даних, необхідно виконати наведені нижче кроки.

Підготовка данних

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

Крок 1. Створення сценарію

Щоб отримати детальну інформацію та SQL-оператори для подій очікування в представленнях V$ACTIVE_SESSION_HISTORY , V$EVENT_NAME та V$ SQLAREA , створіть просте з'єднання трьох таблиць.

V$ACTIVE_SESSION_HISTORY Відображає активність сеансів у базі даних. У ньому містяться щомиті знімки активного сеансу бази даних.V$EVENT_NAME Відображає інформацію про події очікування.V$SQLAREA Відображає статистику для загальної SQL-області, містить один рядок для кожного SQL-виразу. Надає статистику щодо SQL-операторів, які знаходяться в пам'яті, проаналізовані та готові до виконання.

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

Лістинг 1. Сценарій gather_event_stats.sh для періодичного збирання даних

Щоб дані, зібрані в текстовий файл, можна було подати за допомогою електронних таблиць (наприклад, Microsoft Excel) і надалі проаналізувати, як роздільник стовпців використовується символ тильди (

). Цей сценарій створює файл SQL через однакові проміжки часу, виконує SQL-вирази для збору даних і поміщає статистику в текстовий файл $outfile_name .

Крок 2. Запуск сценарію

Розмістіть сценарій на будь-якому сервері програм і виконайте сценарій для збирання даних. Унаведений нижче код перед викликом сценарію замініть змінні ORADBNAME, ORACLEID, PASSWORD відповідними значеннями.

Примітка. Ці значення можна зробити параметрами та передати до сценарію під час виконання.

Для виконання сценарію використовуйте наступний синтаксис:

Запустіть сценарій перед завантаженням даних і не зупиняйте його, поки завантаження даних не завершиться. При виконанні сценарію ретельно підберіть значення No.Of.Iterations ($1) та Interval ($2) для виразу No.Of.Iterations x Intervals = Test Duration (Кількість ітерацій x інтервали = тривалість тесту).

Щоб переконатися, що сценарій запущено, виконайте таку команду:

Крок 3. Обробка даних

Введіть дані та переконайтеся, що програма обробляє дані. Перевірте, чи дані завантажуються в базу даних. Зачекайте, доки не будуть оброблені всі дані. Виведіть файл $outfile_name (ім'я файлу, який містить вихідні дані запиту; у нашому випадку – статистика подій очікування) через однакові проміжки часу і переконайтеся, що розмір файлу зростає.

Крок 4. Аналіз даних та налаштування бази даних

Рисунок 3. Знімок екрана з вихідними даними у текстовому файлі

Імпортуйте дані в електронну таблицю Excel, вибравшиData > Convert Text to Columns. Вкажіть символ тильди (

) як роздільник даних у стовпцях (див. рисунок 4).

Рисунок 4. Знімок екрана з даними подій очікування після поділу в Excel за допомогою роздільника

На підставі цих даних створіть зведену таблицю, щоб отримати суму загального часу для кожної події. Одиницею часу є одна сота секунди, тому діліть значення на100, щоб перетворити його на секунди. Після створення відсортуйте зведену таблицю, щоб визначити подію з максимальним часом очікування (див. рис. 5).

Малюнок 5. Знімок екрана зведеної таблиці

Проаналізуйте подію очікування за допомогою адміністратора бази даних Oracle, щоб зрозуміти причину цієї події. У прикладі подією з найбільшим часом очікування є TX – index contention . Це свідчить про наявність проблеми індексування під час обробки транзакцій.
Отримайте SQLID для операторів wait з найбільшим часом очікування та перевірте відповідні SQL-оператори. У полі C.SQL_TEXT вказується SQL-оператор кожного SQLID . Зверніться до розробників та адміністраторів бази даних, щоб визначити можливість налаштування SQL-операторів для скорочення часу очікування.
Повторіть пункт 6 для кожної з 10 подій з найбільшим часом очікування.
Реалізуйте рішення, запропоновані розробниками та адміністратором бази даних, для усунення цих подій очікування.
Знову виконайте цей тест з тим самим навантаженням і з такою ж тривалістю, щоб переконатися у зменшенні часу очікування або повному усуненні події очікування.

Висновок

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