Oracle_trace – краще вбудоване засіб діагностики, Інші СУБД, Бази даних, статті

В сервер Oracle вбудовано безліч діагностичного коду. Частина його, наприклад, sql_trace, Добре описана в документації, а частина, наприклад, подання x$trace, Не документована зовсім. Я люблю періодично присвячувати деякий час повторного аналізу такого коду, щоб дізнатися, наскільки розширені його можливості, чи отримали вони офіційне визнання і описані Чи в документації. Нещодавно, працюючи з сервером Oracle 9i, я з подивом виявив суттєве розширення можливостей oracle_trace, Яке відбулося за останніх пару релізів. Ця стаття являє собою короткий вступ в oracle_trace і опис його возомжностей.


Як … ?


Як знайти об’єкт, що є джерелом всіх подій buffer busy waits, Які можна побачити в поданні v$waitstat?


Всі ми читали керівництва по налаштуванню продуктивності: “Якщо ви бачите … може знадобитися збільшити кількість списків місць ( freelists ) Для проблемної таблиці “. Але там не сказано, як знайти цю саму проблемну таблицю.


Варіант 1: виконувати безперервний потік запитів до подання v$session_wait і перевіряти значення стовпців p1, p2, p3 при виникненні цієї події. Статистично, рано чи пізно ви отримаєте таким чином обгрунтоване уявлення про те, який об’єкт або об’єкти є причиною проблеми. Цей варіант – досить болючий, і результат його частково залежить від везіння.


Варіант 2: включити подія 10046 на рівні 8 і отримати потік інформації про очікування в трасувань файлах. Вельми серйозно навантажує систему і теж вимагає деякого везіння.


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


Отже, чи не хочете ви отримати список саме тих блоків, яких доводиться чекати, з ідентфікаторамі очікують сеансів, причиною і тривалістю очікування, і все це – з мінімальними витратами ресурсів? Саме це, крім іншого, і дозволяє отримати oracle_trace.


Що таке oracle_trace?


oracle_trace – Це компонент сервера, який збирає інформацію про події, використовуючи порівняно невеликі ресурси.


Серед цих подій – очікування, підключення, відключення, аналіз і виконання запитів, вибірка рядків, і деякі інші.


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


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


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


Використання коштів oracle_trace


Ну, і як oracle_trace допоможе відповісти на вихідний питання?


Просто: один з класів подій, які можна трасувати, – очікування. Треба перевірити, що сервер запущений в режимі, що дозволяє включити трасування, а потім вимагати від нього (або з допомогою PL / SQL, або з командного рядка) почати трасувати очікування (waits). При цьому ми обмежуємо набір подій одіжанія тільки подією 92 (Це buffer busy waits в Oracle 9i, але перевірте, на всякійц випадок, значення стовпців event# і name з уявлення v$event_name у вашій системі). Потім залишається сидіти і чекати приблизно годину в період, коли проблема відчувається найгостріше. Коли отримаємо досить великий файл трасування, припиняємо трасування, поміщаємо дані з файлу трасування в базу і виконуємо SQL-оператор, запитувач, скажімо, таке:




Для яких об’єктів виникали події buffer busy waits, Скільки доводилося чекати, як часто виникали очікування і хто найбільше постраждав?

Якщо змиритися з додатковими витратами ресурсів, можна при трасуванні збирати навіть очікують SQL-оператори, що дозволить дізнатися, які SQL-оператори найбільше постраждали від очікувань.


Збираємо всі разом


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








 



 Обов’язкові
oracle_trace_enable = true
oracle_trace_collection_name = **

Стандартні значення
oracle_trace_collection_size = 5242880
oracle_trace_collection_path = ?/otrace/admin/cdf
oracle_trace_facility_path = ?/otrace/admin/fdf
oracle_trace_facility_name = oracled

 

Рисунок 1: Параметри ініціалізації, пов’язані з oracle_trace


Параметру oracle_trace_collection_name потрібно явно задати пусте значення “”, Бо його стандартне значення – “oracle”, А якщо ім’я набору вказано і трасування включена, сервер Oracle виконує трассіроку на рівні примірника з моменту запуску (ого!).


Параметр oracle_trace_collection_path задає каталог, в якому будуть розміщуватися файли. У каталозі oracle_trace_facility_path розміщуються списки подій, які можна трасувати (facility definition files – Файли визначення коштів, що надаються Oracle Corporation). Параметр oracle_trace_facility_name задає список подій, які нас цікавлять. Нарешті, можна обмежити розмір (в байтах) файлу з трасування інформацією, задавши значення параметра oracle_trace_collection_size.


Після запуску сервера можна починати збір трасування інформації.


У цій статті я буду використовувати тільки засоби командного рядка, хоча є й альтернативний PL / SQL-інтерфейс (пакет dbms_oracle_trace_agent – Прим. перекладача), і навіть графічний інтерфейс, якщо купити відповідний модуль для Oracle Enterprise Manager. Ми будемо використовувати команду такого вигляду:




otrccol start 1 otrace.cfg

Команда otrccol – Основний інтерфейс для oracle_trace. Є й інші команди, але більшість їх можливостей були додані в otrccol. Очевидно, що параметр start вимагає почати трасування (а параметр stop– Її зупинити). Значення “1“- Довільно вибраний ідентифікатор завдання, а otrace.cfg – Файл конфігурації. Приклад файлу конфігурації представлений на рис. 2.








 



 Використовується для збору даних  

col_name = jpl
cdf_file = jpl
dat_file = jpl
fdf_file = waits.fdf
max_cdf = -10485760
buffer_size = 1048576
regid = 1 192216243 7 92 5 d901

Використовується для форматування
username = otrace
password = otrace
service = d901
full_format = 1

 

Рисунок 2: Приклад файлу конфігурації oracle_trace


Цей файл вимагає від сервера створити файл з набором даних по імені jpl.dat, З файлом визначення набору (collection definition file) на ім’я jpl.cdf і ідентифікатором набору jpl. Визначення трассируемого коштів знаходиться у файлі waits.fdf (Цей файл надається корпорацією Oracle і містить тільки події очікування). Розмір файлу трасування буде обмежений 10 Мбайтами, але він буде використовуватися повторно, так що, завжди буде містити 10 Мбайт останніх даних. Перед скиданням даних в цей файл сервер Oracle буде накопичувати їх в буфері розміром 1 Мбайт.


Можливість задати regid– Одна з найбільш потужних можливостей oracle_trace. “Стандартне” значення цього рядка містить “0 0” замість моїх “7 92”, І треубет, щоб oracle_trace трасували весь примірник Oracle, який задається ідентифікатором d901 в кінці рядка. Я ж попросив трасувати тільки засіб номер 7 (Події очікування) елемент 92 (Очікування buffer busy waits).


При необхідності можна вказувати у файлі декілька рядків regid. Для першого набору експериментів я використовував два рядки regid в файлі конфігурації, що задають трасування “7 129” і “7 130”– Послідовні (sequential) і вибіркові (scattered) читання, відповідно, оскільки ці типи очікувань легко згенерувати.


Розділ, що задає особливості форматування, я прокоментую далі.


Після того, як система попрацює деякий час, виконаємо:




otrccol stop 1 otrace.cfg
otrccol format otrace.cfg

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


Однак перш ніж ви зможете сформатувати набір, треба створити схему, в якій будуть знаходитися таблиці, що використовуються при форматуванні. Як ім’я користувача та пароль ми використовуємо значення, представлені раніше на рис. 2. Рядок full_format=1 у файлі конфігурації призводить до того, що в таблиці буде скинутий весь файл; установка full_format=0 призведе до скидання тільки нових даних. Зверніть увагу також на ім’я служби (service) – Воно задає базу даних, в якій знаходиться відповідна обліковий запис. Щоб використовувати команду format, Треба запустити процес прослуховування TNS (TNS listener), навіть якщо дані скидаються в локальну базу.


На рис. 3 представлений невеликий сценарій, який створює обліковий запис і надає їй необхідні привілеї.








 



create user otrace identified by otrace
default tablespace users – Якщо не використовується 9i:
— temporary tablespace temp
quota 100m on users;

grant create session to otrace;
grant create table to otrace;
grant create sequence to otrace;
grant create synonym to otrace;

 

Рисунок 3: Створення користувача, у схемі якого будуть перебувати таблиці трасування


При вказівці опції format програма автоматично (по крайней мере, в нових версіях Oracle) створить необхідні таблиці у вказаній схемі. Частина цих таблиць бцдет мати цілком осмислені імена, наприклад:




EPC_COLLECTION

Імена інших будуть позбавлені всякого сенсу:




V_192216243_F_5_E_9_9_0

Проблему з незручними іменами можна вирішити, запус сценарій otrcsyn.sql в каталозі $ORACLE_HOME/otrace/demo.


Цей сценарій створює синоніми для таблиць, даючи їм осмислені імена, наприклад:




WAIT
CONNECTION

(Імена відрізняються в різних версіях Oracle.)


Я виявив невелику проблему з автоматичною генерацією таблиць. Якщо вказати один із дуже виборчих списків засобів (наприклад, waits.fdf), Створюються тільки таблиці, необхідні для подання відповідних результатів при форматуванні. Якщо потім ви вирішите використовувати більш повний список трассируемого засобів (наприклад, oracle.fdf) При форматуванні відбудеться збій, оскільки частина таблиць є, а інших – ні. Так що, може мати сенс попрацювати кілька секунд, трассіруя oracle.fdf, Сформатувати дані, а потім викинути (truncate) дані з усіх таблиць. Це грубий, але ефективний метод настройки відповідної схеми.


Деякі результати


Отже, що ж ми зробили:



І що тепер?


Припустимо, ми використовували файл конфігурації, представлений на рис. 2. Підключившись від імені облікового запису otrace, Ми виявимо рядки в таблицях connection, disconnect і wait. Рядки в таблиці wait розкажуть на все про події buffer busy waits, Що сталися за час трасування.


Наприклад, ми могли виконати SQL-оператор, представлений на рис. 4:








 



select
p1 file_id,
p2 block_id,
p3 reason_code,
count(*) ct,
sum(time_waited)/100 secs

from
wait
group by
p1, p2, p3
order by
sum(time_waited) desc
;

 

Рисунок 4: Приклад запиту, що дозволяє виявити найбільш тривалі очікування зайнятих блоків


Якщо необхідно велика точність, можна видати всі очікування з тимчасовими позначками, і стовпцем, (досить оптимістично) названим timestamp_nano.


Якщо необхідно з’ясувати, яким користувачам довелося чекати найдовше, змініть запит, і підсумуйте по стовпцях session_index (SID) і session_serial (serial#). Для отримання за значеннями (session_index, session_serial) Імені користувача, імені машини, часу реєстрації і т.п. можна використовувати таблицю (синонім) connection.


Звичайно, нічого (окрім зниження продуктивності) не заважає поєднувати цю таблицю з поданням dba_extents для перетворення ідентифікаторів файлу і блоку в типи і імена об’єктів.


А якщо необхідно виявити конкретні SQL-оператори, при виконанні яких довелося чекати, завжди, хоча і ціною витрати ще більших ресурсів, можна перейти на використання файлу sql_waits.fdf, Який призводить до заповнення трасування інформацією ще кількох таблиць, які потім можна з’єднувати по стовпцях session_index, session_serial, timestamp і timestamp_nano.


Нарешті, якщо ви думаєте, що витрати на завантаження даних в таблиці і побудова звітів негативно позначаться на системі, завжди можна перенести файли cdf і dat на іншу машину і обробляти їх в іншій базі даних. Мені вдалося навіть, з невеликими виправленнями, згенерувати набір даних на примірнику версії 9i, а потім обробити їх на примірнику версії 8i, просто щоб довести цю можливість. Це, звичайно, ускладнить можливість за номерами блоків визначати об’єкти.


Майбутнє


Можливості нескінченні – наприклад, один з файлів визначення засобів називається oraclec.fdf і дозволяє відстежувати дії в буферному кеші. Після трасування цих дій можна, з точністю до мікросекунди, визначити, які блоки були завантажені в кеш, в якому порядку, і які блоки довелося витіснити з кеша, щоб можна було їх завантажити. (Я підозрюю, що додаткові витрати далеко не завжди дозволять використовувати цей метод.)


Ще одна можливість трасування, яка багатьом нагоді, представлена ​​файлом connect.fdf. Він перехоплює підключення і відключення сеансів, багато в чому аналогічно тому, як працює команда audit session. Однак у файлі трасування накопичується ще півдюжини додаткових статистичних показників (таких як записи повторного виконання), які в таблицю aud$ не потрапляють, і в процесі накопичення запис в базу даних не виконується.


Можна дістатися і до окремого користувача: можна націлити oracle_trace на трасування дій одного користувача. Можна навіть написати SQL-оператор, який читає результуючі таблиці і генерує файл, аналогічний створюваному sql_trace. При цьому можна буде також відстежувати момент реєстрації, переходу з одного розділяється сервера на інший і, нарешті, виходу.


Висновок


Це всього лише короткий вступ до oracle_trace, Що торкнулося основи його використання. Треба ще попрацювати над оцінкою стабільності роботи і побічних ефектів використання oracle_trace, Не кажучи вже про вплив на продуктивність.


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


В кінцевому підсумку, oracle_trace дозволяє отримувати точні відповіді на ряд складних питань, що турбують АБД багато років.


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


Проблема


Є багато відмінностей, зазвичай пов’язаних тільки з іменами, між реалізаціями oracle_trace у версіях Oracle 8i і Oracle 9i. Ця стаття написана виключно на базі Oracle 9i.

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


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

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

Ваш отзыв

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

*

*