Построение профиля сессии в СУБД Oracle на основе триггера on-logoff для СУБД Oracle 9i, 10g

Оптимизация ИС методом поочередной
(модуль за модулем) оптимизации пользовательских модулей,
наверное, представляет собой самый действенный путь. Но такая
оптимизация, вероятнее всего, востребует очень длительного
времени, которого, обычно, у заказчика нет. Не получив
резвого результата, заказчик вообщем можем принять решение о
прекращении проекта.

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

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

Данная статья указывает, как используя
формулу для расчета времени отклика, с наименьшими временными
затратами:
Оценить возможность оптимизации ИС на системном уровне;
Найти более “проблемные” сессии;
Найти сессии, создающие самую большую нагрузку на
систему;
Найти время выполнения бизнес-транзакций.

Собранные данные являются основой для
принятия решения о направлении оптимизации: прикладного ПО либо
оптимизации на системном уровне. В случае принятия решения о
выполнении оптимизации прикладного ПО собранные данные уже
содержат аспекты для включения трассировки пользовательских
сессий. На основании собранных данных можно представить
бизнес-план оптимизации системы с указанием:
Длительности и состава работ: перечня модулей для
оптимизации;
Достигаемого результата: вероятного ускорения;
Цены работ.

1. Введение

При принятии решения об ускорении какой-нибудь
информационной системы (ИС) появляется вопрос – что, фактически
говоря, нужно ускорять? Варианта может быть два:
не устраивает работа ИС в целом либо
есть определенные жалобы на тот либо другой модуль.

Благодаря работам Cary Millsap [1],
довольно ясно, как улучшить определенный модуль: следует
включать трассировку и рассматривать ее вывод. Mr.
Millsap считает, что указать модули для оптимизации
должен заказчик. Но таковой подход обладает рядом недочетов.
Во-1-х, реакция на жалобы юзеров – это реактивный
подход, а хотелось бы иметь возможность предупреждения
схожих ситуаций (проактивный подход). Во-2-х, перечень
модулей возможно окажется довольно велик, а общая причина
крыться в недостаточно производительном дисковом массиве. В
этом случае оптимизация модуль за модулем возможно окажется
очень долгой, дорогой и потому невостребованной.

Если нас не устраивает работа ИС в целом,
выделение определенных модулей может стать очень сложной
задачей. В компаниях не всегда есть согласованные
бизнес-требования к времени выполнения. А сбор таких
требований, их согласование – предмет отдельного обследования.
Если же требования есть, не непременно существует
система регистрации недовольства юзеров (help desc).
Вероятна и такая ситуация, когда все смирились с имеющимся
положением вещей и уже не считают необходимым обращаться в службу
help desc.

Таким макаром, в эталоне следует
без помощи других оценить текущую загрузку ИС, способности
оптимизации ИС на системном уровне, собрать перечень модулей, с
указанием их воздействия на ИС. А потом уже сопоставить приобретенные
данные с требованиями заказчика.

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

Построенный профиль сессии указывает:
общую длительность работы сессии,
время реальной работы,
число транзакций,
рассредотачивание времени сервис и времени ожидания,
также статистику сессии.

Статистика сессии: физический ввод/вывод,
логический ввод/вывод, сетевой ввод/вывод, потребление
ресурсов

Session info :
————–
Os user / Oracle user / Machine :D SBOOKdsvolk / 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

2. Время отклика

Для осознания данной статьи нужно быть
знакомым с формулой времени отклика:

Время отклика = Время обслуживания + Время
ожидания
(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 a
where a.name = ‘CPU used by this session’;

Время ожидания может быть получено из
динамических представлений V$SYSTEM_EVENT и V$SESSION_EVENT,
суммируя все времена ожидания, кроме неких из
их: select sum(time_waited) “Total Wait Time”

from v
where event not in ();

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

Для событий ожидания действует такая же
логика: если ожидания было наименее 1 мкс для Oracle9i,
Oracle10g и наименее 1 мс для версии Oracle8i то в
итоге будет записан 0.

Таким макаром, следует записать:

Время отклика =
Время обслуживания + Время ожидания + Неучтенное время

(Response Time = Service Time + Wait Time +
Unaccounted Time)

Unaccounted Time представляет собой
ошибку измерений. Если в итоге анализа, окажется что
Unaccounted Time составляет значимый процент от времени
ожидания, следует найти и исключить причину такового
поведения.

2.1. Время отклика на уровне
экземпляра

Время отклика на уровне экземпляра удобнее
всего получить из отчета 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;

К огорчению, итог представляет собой
“среднюю температура по поликлинике”, а конкретно, указывает общую
ситуацию, которая для отдельных сессий может очень
отличаться. Потому построение рассредотачивание времени отклика
для отдельных сессий очень полезно.

2.2. Время отклика на уровне
сессии

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

На уровне сессии будет любопытно получить не
только рассредотачивание времени отклика, да и найти источник
больших ожиданий. Но, огромное кол-во событий ожидания
(более 800 в версии Oracle10g) и статистик (более 300 в
Oracle10g) очень затрудняет предстоящий анализ. Потому
для упрощения предстоящего анализа можно перейти к классам
событий ожидания и статистики.

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

Таким макаром, время ожидания выходит из
v$session_event
:

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

А время обслуживания из v$mystat:

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

Как увидит внимательный читатель, на самом
деле статистика содержит не только лишь информацию об использовании
CPU. Сведения об использовании другой инфы будут
приведены в последующих главах.

3. Сбор данных

3.1. На уровне экземпляра

Как уже упоминалось, на уровне экземпляра
полностью довольно пользоваться несколько измененным
отчетом statspack. Установка, сбор данных statspack –
это отлично документированная процедура, не оказывающая
огромного воздействия на работу экземпляра, в особенности при условии
сбора статистики уровня менее 5.

3.2. На уровне сессии

На уровне сессии задачка сбора данных
несколько труднее. В книжке [3], «Oracle Wait Interface: A
Practical Guide to Performance Diagnostics & Tuning»,
приводится пример триггера before logoff on database.
Вправду, как указывает практика, воздействие такового
триггера на работающую производственную систему мало, а
собираемая статистика более полна. Но, сохранение
статистики в БД может плохо сказаться на
производительности. Потому статистика будет сохранять во
наружном текстовом файле в формате csv.

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

4. Анализ данных

4.1 Оценка способностей
оптимизации на системном уровне

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

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

4.2. Оценка способностей
оптимизации на уровне сессий

Для анализа данных проще всего загрузить
собранные при помощи триггера on-logoff данные в СУБД. Начиная с
версии Oracle9i можно для схожей операции
пользоваться наружными таблицами (organization external):

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

4.2.1. Поиск “проблемных”
сессий

Получив из statspack среднюю оценку времени
ожидания по экземпляру, не стоит отчаиваться. Наша задачка как
раз и заключается в том, чтоб отыскать те сессии, у каких времена
ожидания составляют значимый процент времени отклика.

Для поиска проблемных сессий, воспользуемся
последующим аспектом: время ожидания составляет более 60% от
общего времени ответа.

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

Из листинга ниже, мы лицезреем, что есть сессия,
у который % ожидания составляет 66.62% от времени ответа.

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

Для избранных сессий следует произвести
более детализированный анализ обстоятельств ожидания.

4.2.2 Поиск более томных
сессий

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

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

На листинге ниже мы лицезреем, что сессия
юзера DWH (warehouse) выполнила наибольшее кол-во
физических чтений.

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

4.2.3 Определение времени
бизнес транзакции

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

Время транзакции = (Время обслуживания +
Время ожидания )/кол-во транзакций.

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

4.2.4 Учет передачи данных по
сети

Во время передачи данных клиенту формируется
пара сообщений, которые составляют так именуемый 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) приводится запрос для
определения значений:

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

Для моей сессии sqlplus, в какой я
выполнил большой full scan запрос, а потом
просматривал приобретенные данные, профиль сессии смотрится так:

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

Из этого видно: общее время сессии 118 сек,
из их только чуток более 18 сек – время работы, а более 95% -
время ожидания (передача по сети).

Разумеется, что точность способа находится в зависимости от
точности определения среднего времени на 1 roundtrip, но
другого метода я не знаю.

5. Границы внедрения

Приводимый в данной статье способ обладает
рядом ограничений, которые следует учесть при применении.

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

Если сессия делает параллельную
операцию, то, к огорчению, данные об ожиданиях сессии
остаются в подчиненных процессах, которые делали реальное
чтение данных. Наша сессия испытывает ожидания вида:

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

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

Не любая пользовательская сессия
непременно делает операции фиксации либо отказа
(commit либо rollback). Полностью могут существовать
юзеры, осуществляющие только постоянное чтение данных.
В данном случае не получиться высчитать время их
бизнес-транзакций, делая упор на количество фиксаций – их просто
нет. Но, в принципе, можно перейти к анализу среднего времени
выполнения 1-го пользовательского вызова (user calls).
Естественно, данная ситуация просит более глубочайшего осознания
приложения.

6. Включение трассировки

Итак, после определения “проблемных” сессий
нужно выполнить их трассировку. Удобнее всего выполнить
такую трассировку в триггере on-logon. Во время сбора
инфы накоплено довольно инфы о времени
выполнения сессии, имени юзера, наименование программки,
чтоб выстроить условие, которые позволит точно отобрать
нужную сессию.

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

7. Заключение

Триггер on-logoff предоставляет нам широкие
способности для выбора “проблемных” сессий по нескольким
аспектам. При всем этом сбор инфы не оказывает воздействия на
работу конечных юзеров. Благодаря способности
одновременного анализа статистики и событий ожидания, можно
вести поиск по нескольким фронтам. В тоже время объем
собираемой инфы значительно меньше, чем объем
соответственных файлов трассировки.

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

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

Аналогичный товар: Комментирование на данный момент запрещено, но Вы можете оставить ссылку на Ваш сайт.

Комментарии закрыты.