Побудова профілю сесії в СУБД Oracle на основі тригера on-logoff для СУБД Oracle 9i, 10g, Інші СУБД, Бази даних, статті

Анотація


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

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

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

Дана стаття показує, як використовуючи формулу для розрахунку часу відгуку, з мінімальними тимчасовими витратами:


  1. Оцінити можливість оптимізації ІС на системному рівні;
  2. Визначити найбільш “проблемні” сесії;
  3. Визначити сесії, що створюють найбільше навантаження на систему;
  4. Визначити час виконання бізнес-транзакцій.

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


  1. Тривалості та складу робіт: списку модулів для оптимізації;
  2. Досягається результату: можливого прискорення;
  3. Вартості робіт.

Введення


При прийнятті рішення про прискорення небудь інформаційної системи (ІС) виникає питання – що, власне кажучи, необхідно прискорювати? Варіанта може бути два:


Завдяки роботам Cary Millsap [1], досить ясно, як оптимізувати конкретний модуль: слід включати трасування і аналізувати її висновок. Mr. Millsap вважає, що вказати модулі для оптимізації повинен замовник. Але такий підхід має ряд недоліків. По-перше, реакція на скарги користувачів – це реактивний підхід, а хотілося б мати можливість попередження подібних ситуацій (проактивний підхід). По-друге, список модулів може виявитися досить великий, а загальна причина критися в недостатньо продуктивному дисковому масиві. В цьому випадку оптимізація модуль за модулем може виявитися занадто довгою, дорогий і тому незатребуваною.

Якщо нас не влаштовує робота ІС в цілому, виділення конкретних модулів може стати вельми складним завданням. У компаніях не завжди існують узгоджені бізнес-вимоги до часу виконання. А збір таких вимог, їх узгодження – предмет окремого дослідження. Якщо ж вимоги існують, не обов’язково існує система реєстрації невдоволення користувачів (help desc). Можлива і така ситуація, коли всі змирилися з існуючим станом речей і вже не вважають за потрібне звертатися до служби help desc.

Таким чином, в ідеалі слід самостійно оцінити поточну завантаження ІС, можливості оптимізації ІС на системному рівні, зібрати список модулів, з зазначенням їх впливу на ІВ. А потім уже порівняти отримані дані з вимогами замовника.

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

Побудований профіль сесії показує:


Статистика сесії: фізичний введення / виведення, логічний введення / виведення, мережевий введення / виведення, споживання ресурсів




Session info :
————–
Os user / Oracle user / Machine :DSBOOKdsvolk / DSVOLK / GPVKDSBOOK
Module / Program :SQL*Plus / sqlplusw.EXE
Responce time:
————–
Total (s) / Work time (s) / Transactions : 34.00 / 23.40 / 1
Service (s) / Wait (s) / Unaccounted time (s) : 1.25 / 21.40 / .75
Service (%) / Wait (%) / Unaccounted time (%) : 5.34% / 91.45% / 3.21%
Session stats:
————–
IO (Mb)/ Cache Memory (Mb)/ Net IO (Mb) : 10.20 / 6.34 / 1.92
Total work :
————–
PGA Memory usage (Mb) / Total changes(Mb): 4.99 / .03


Час відгуку


Для розуміння даної статті необхідно бути знайомим з формулою часу відгуку:

Час відгуку = Час обслуговування + Час очікування
(Response Time = Service Time + Wait Time)

Дані формула була вперше опублікована в роботі [2] (“The COE perfomance method”). Час обслуговування може бути отримано з динамічних уявлень V $ SYSSTAT або V $ SESSTAT як компонента “CPU used by this session” :

select a.value “Total CPU time”
from v$sysstat a
where a.name = “CPU used by this session”;

Час очікування може бути отримано з динамічних уявлень V $ SYSTEM_EVENT і V $ SESSION_EVENT, підсумовуючи всі часи очікування, за винятком деяких з них:

select sum(time_waited) “Total Wait Time”

from v$system_event
where event not in (<Some Idle events>);


Ми можемо дещо поліпшити нашу формулу, розуміючи, наскільки акуратно Oracle накопичує часи. Так в “Oracle Database Reference 10g Release 2 (10.2)” для статистики CPU used by this session сказано: якщо користувальницький виклик завершився швидше, ніж 10 мс, то до статистики буде додано 0 мс. Також відомо, що подібна “неакуратність” може відбуватися в сильно перевантажених серверних комплексах.

Для подій очікування діє така ж логіка: якщо очікування було менше 1 мкс для Oracle9 i, Oracle10 g і менше 1 мс для версії Oracle8 i то в результаті буде записаний 0.

Таким чином, слід записати:

Час відгуку = Час обслуговування + Час очікування + Невраховані час
(Response Time = Service Time + Wait Time + Unaccounted Time)

Unaccounted Time являє собою помилку вимірювань. Якщо в результаті аналізу, виявиться що Unaccounted Time складає значний відсоток від часу очікування, слід виявити і виключити причину такої поведінки.

Час відгуку на рівні примірника

Час відгуку на рівні примірника найзручніше отримати зі звіту statspack за допомогою наступного запиту:




select event, time, pctwtt from
(
select “Responce time” event
, (:tcpu*10000 + :twt)/1000000 time
, to_number(“100”) pctwtt
from dual
Union
select “Service time” event
, (:tcpu*10000)/1000000 time
, decode(:twt + :tcpu*10000, 0, 0,
100
* :tcpu*10000
/ (:twt + :tcpu*10000)
) pctwtt
from dual
union
select “Wait time” event
, (:twt)/1000000 time
, decode(:twt + :tcpu*10000, 0, 0,
100
* :twt
/ (:twt + :tcpu*10000)
) pctwtt
from dual
)
order by pctwtt desc;

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

Час відгуку на рівні сесії

Можна побудувати формулу розподілу часу відгуку для сесії, скориставшись динамічними уявленнями v$session_event і v$mystat

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

Так, функція apt_stat_class_t (p_statname varchar2 ) return varchar2 повертає клас статистики, а функція apt_event_class_t (p_event varchar2 ) return varchar2 повертає клас очікування. Треба відзначити, що класи подій очікування не збігаються з класами, введеними у версії 10g, і це зроблено навмисно.


Таким чином, час очікування виходить з v$session_event :


А час обслуговування з v$mystat :


Як помітить уважний читач, насправді статистика містить не тільки інформацію про використання CPU. Відомості про використання іншої інформації будуть приведені в наступних розділах.


Збір даних


На рівні примірника

Як уже згадувалося, на рівні примірника цілком достатньо скористатися кілька модифікованим звітом statspack. Установка, збір даних statspack – це добре документована процедура, не надає великого впливу на роботу примірника, особливо за умови збору статистики рівня не більше 5.

На рівні сесії

На рівні сесії завдання збору даних трохи складніше. У книзі [3], “Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning”, наводиться приклад тригера before logoff on database. Дійсно, як показує практика, вплив такого тригера на працюючу виробничу систему мінімально, а збирається статистика найбільш повна. Однак, збереження статистики в БД може негативно позначитися на продуктивності. Тому статистика буде зберігати в зовнішньому текстовому файлі у форматі csv.




 


Аналіз даних


Оцінка можливостей оптимізації на системному рівні

Обробляючи звіти statspack можна помітити, що час очікування на рівні примірника рідко перевищує 40-50%. А це означає, що якщо ми за допомогою налаштувань СУБД або апаратури зменшимо час очікування в 2 рази, наші користувачі, в середньому, отримають виграш не більше 20% -25%. Можливо, звідси й слід експериментально відомий факт, що за допомогою налаштувань ОС і примірника отримати більше 20% виграшу в продуктивності дуже складно.

Хочеться відзначити, що складно – не означає неможливо. Хоч і рідко, але все ще зустрічаються випадки, коли невдале розташування журналів протоколів транзакцій (redo logs) гальмує всю систему. Можна привести ще кілька подібних “стандартних” помилок. В цьому випадку ми зі звіту statspack бачимо, що час очікування становить значну частину загального часу відповіді. Таким чином, перед початком проекту по оптимізації спочатку краще переконатися, що на системному рівні все в порядку. Якщо необхідно, слід запропонувати шляхи системної оптимізації, але вказати і очікуваний ефект.


Оцінка можливостей оптимізації на рівні сесій


Для аналізу даних найпростіше завантажити зібрані за допомогою тригера on-logoff дані в СУБД. Починаючи з версії Oracle9 i можна для подібної операції скористатися зовнішніми таблицями (organization external):




 

Пошук “проблемних” сесій

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

Для пошуку проблемних сесій, скористаємося наступним критерієм: час очікування становить більше 60% від загального часу відповіді.




 

З лістингу нижче, ми бачимо, що є сесія, у який% очікування складає 66.62% від часу відповіді.




 

Для обраних сесій слід провести більш детальний аналіз причин очікування.

Пошук найбільш важких сесій

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




 

На лістингу нижче ми бачимо, що сесія користувача DWH (warehouse) виконала найбільшу кількість фізичних читань.




 

Визначення часу бізнес транзакції

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

Час транзакції = (Час обслуговування + Час очікування) / кол-во транзакцій.




 

Облік передачі даних по мережі

Під час передачі даних клієнта формується пара повідомлень, які складають так званий SQL*Net roundtrip:





WAIT #1: nam=”SQL*Net message from client” ela= 5103 p1=1413697536 p2=1 p3=0
WAIT #1: nam=”SQL*Net message to client” ela= 2 p1=1413697536 p2=1 p3=0


Зверніть увагу, що повідомлення from client триває приблизно 5 мс, а повідомлення to client – 2 мкс, що складає незначний відсоток від загального часу на 1 roundtrip. Однак, проблема полягає в тому, що якщо сесія простоює, наприклад, в очікуванні введення даних від клієнта, очікування SQL*Net message from client також накопичується. Таким чином, складно відрізнити очікування передачі даних від простою. На щастя, існує статистика SQL*Net roundtrips to/from client . Якщо була передача даних, ця статистика збільшується.

Отже, для акуратного підрахунку часу простою:

Час простою сесії = Загальний час очікування SQL * Net message from client – Кількість roundtrip * середній час 1 rountrip.

Середній час 1 roundtrip для ІС пропонується визначати на основі аналізу трасувань файлів. Для моєї локальної системи воно становить 5 мс.

Таким чином, формула для визначення часу відповіді сесії перетворюється:

Час відгуку – Час простою = Час обслуговування + Час очікування + Невраховані час

На лістингу нижче (r2.sql) наводиться запит для визначення значень:




 

Для моєї сесії sqlplus, в якій я виконав великий full scan запит, а потім переглядав отримані дані, профіль сесії виглядає так:




 

З цього видно: загальний час сесії 118 сек, з них тільки трохи більше 18 сек – час роботи, а більше 95% – час очікування (передача по мережі).

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


Межі застосування


Наведений в даній статті метод має ряд обмежень, які слід враховувати при застосуванні.

Так як технічно збір даних виконується в тригері on-logoff, то інформація накопичується тільки тоді, коли сесія виконує нормальний вихід з БД. Якщо сесія була припинена за допомогою команди kill, інформація про цю сесії зібрана не буде. Слід також враховувати, що під час включення тригера частина користувацьких сесій може вже працювати, а також що можуть існувати сесії, які не закінчаться до моменту виключення тригера. Для більш точного обліку даних, що збираються слід використовувати процедуру, що збирає накопичену статистику сесій користувачів до включення / вимикання тригера . Так, наприклад, якщо сесія вже працювала на момент включення тригера, то після закінчення її роботи з її статистики необхідно відняти значення, накопичені до початку роботи тригера.

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




 

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

Не кожна користувацька сесія обов’язково виконує операції фіксації або відмови (commit або rollback). Цілком можуть існувати користувачі, що здійснюють тільки регулярне читання даних. В цьому разі не вийти розрахувати час їх бізнес-транзакцій, спираючись на кількість фіксацій – їх просто немає. Але, в принципі, можна перейти до аналізу середнього часу виконання одного користувача виклику (user calls). Звичайно, дана ситуація вимагає більш глибокого розуміння програми.

Включення трасування


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




 


Висновок


Тригер on-logoff надає нам широкі можливості для вибору “проблемних” сесій за кількома критеріями. При цьому збір інформації не робить впливу на роботу кінцевих користувачів. Завдяки можливості одночасного аналізу статистики і подій очікування, можна вести пошук за кількома напрямками. У той же час обсяг інформації, що збирається істотно менше, ніж обсяг відповідних файлів трасування.

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

Зібрана інформація повинна бути проаналізовані і повинні бути сформульовані критерії для включення трасування “проблемних” модулів.

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


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

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

Ваш отзыв

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

*

*