Transcript Повышение быстродействия
Slide 1
Повышение быстродействия сложных
расчетных алгоритмов с помощью
оптимизации запросов Oracle
2014
Slide 2
Повышение быстродействия
Содержание
• Пример из жизни по анализу и настройки
производительности
• Последовательность действий при анализе
• Где и как нужно применять следующие инструменты
Profiler, Hierarchical Profiler, SQL Tuning Advisor,
ASH reports
• Как правильно интерпритировать полученные
данные о производительности
01
Slide 3
Повышение быстродействия
Перед началом анализа
• Убедиться, что статистика находится в актуальном
состоянии (собирается автоматически)
• Используется автоматическое распределение
оперативной памяти
workarea_size_policy = AUTO
memory_target или sga_target + pga_target
• Режим оптимизатора установлен в значение по
умолчанию
optimizer_mode = ALL_ROWS
• Отсутствуют недокументированные параметры
(начинаются с символа _)
02
Slide 4
Повышение быстродействия
Определение пользовательской операции
• Используем SQL-монитор (в составе Парус 8), чтобы
определить точку старта и параметры вызова
• Берем время выполнения по данным SQL Monitor
• Определяем цель оптимизации вместе с пользователем!
Сколько времени должно тратиться на выполнение?
03
Slide 5
Повышение быстродействия
Проверка влияния окружения
Проверяем в «идеальных» условиях:
–
Отсутствует или минимальна нагрузка на сервер
–
Время тестирования не пересекается с автоматическими заданиями
–
Для тестовой БД конфигурация та же, что и для рабочей
засекаем, есть ли разница во времени
Разница не существенна
Переходим к анализу
04
Разница существенна
Поиск других факторов
- OEM
Slide 6
Повышение быстродействия
Пример
1. Фиксируем исходные данные и цель
• Операция: «Сформировать производственный состав»
Модуль «Планирование и учет в дискретном производстве»
Раздел «Инженерные данные» - спецификация
• Текущее время отклика:
Текущее время выполнения 30 секунд для состава из 5000
позиций.
• Цель: 15 секунд
• Точка старта: P_FCPRODCMP_FORMING
05
Slide 7
Повышение быстродействия
Пример
2. Определение узких мест – PLSQL Profiler
• Позволяет определить операции в PLSQL коде, которые
выполнялась наиболее долго – узкое место
• Выбираем 1-3 наиболее долгих операций и начинаем
анализ с самой долгой
• Profiler встроен в программу PLSQL Developer, запускается
одной кнопкой
• Легко перенести вызов из SQL-монитора Паруса
06
Slide 8
Повышение быстродействия
Пример
Profiler в PLSQL Developer
07
Slide 9
Повышение быстродействия
Пример
Profiler в PLSQL Developer
• Возможна разница между первым запуском и
последующими (для тестовой БД можно использовать
alter system flush BUFFER_CACHE;)
• Время серверное и не учитывает SQL*Net message
1-й запуск
2-й запуск
08
Slide 10
Повышение быстродействия
Темы доклада
Узким местом является SQL запрос
for rSP in (
select nvl(T.PR_COND, nSHT_PR_COND) as PR_COND,
T.OPER_NUMB,
H.MANPOWER,
H.EQUIPMENT,
H.FPDACCNT,
H.SUBDIV as SUBDIV_,
nvl(H.SUBDIV, nSHT_SUBDIV) as SUBDIV,
decode( nUSE_SHORT_NAME, 0, nvl(D.CODE, sSUBDIV), nvl(D.SHORTNAME_NOM,
from FCROUTSHTSP
T,
FCROUTSHTSPHIST H,
INS_DEPARTMENT D
where T.PRN = nRN
and H.PRN = T.RN
and H.DATE_FROM = ( select max(DATE_FROM)
from FCROUTSHTSPHIST
where PRN = T.RN
and DATE_FROM <= dDATE )
and H.SUBDIV = D.RN(+)
and nvl(H.SUBDIV, nSHT_SUBDIV) is not null
and H.NORM > 0
order by H.NUMB
09
Slide 11
Повышение быстродействия
Темы доклада
Определение последовательности вызова
• Необходимо для понимания логики работы
• Необходимо, если в «узкое место» можно попасть из
разных частей программы
Hierarchical Profiler или Визуальный анализ кода
10
Slide 12
Повышение быстродействия
Пример
Иерархический Profiler - показывает дерево вызовов
•
•
Доступен в Oracle SQL Developer
Можно запустить самостоятельно
Файл с примером запуска
-- 1) Run
declare
nTMP number;
begin
DBMS_HPROF.START_PROFILING('PLSHPROF_DIR', 'test004.trc');
-- Пользовательская операция
-- без PROLOGUE/EPILOGUE
UDO_P_FCPRODCMP_FORMING(68824, 'Производственный состав',
'29', NULL, 'M_5', 'M_5', to_date('19.11.2014','dd.mm.yyyy'),
NULL, 0, NULL, 'test ak', nTMP);
DBMS_HPROF.STOP_PROFILING;
rollback; -- пользовательские данные
end;
11
Slide 13
Повышение быстродействия
Пример
Иерархический Profiler
•
•
12
Отсортировать по убыванию времени выполнения
Запомнить номер строки выходных данных (слева)
Slide 14
Повышение быстродействия
Анализ узких мест
Анализ узких мест
• Определение причины неисправности («ответственного»):
CPU, Disk, Memory
Инструменты:
SQL Trace, ASH reports, SQL Tuning Advisor, SQLXtrace
Работают с планом запроса и дополнительной статистикой
• Определение возможности устранить неисправность, не
прибегая к изменению кода
- Применение профиля SQL Tuning Profile
- Дополнительные индексы
- Дефрагментация
- Изменение конфигурации сервера
13
Slide 15
Повышение быстродействия
Анализ узких мест
3. Проверка найденного узкого места - OEM
• Определение причины неисправности («ответственного»):
CPU, Disk, Memory
• Инструменты:
01
Slide 16
Повышение быстродействия
Анализ узких мест
Анализ с помощью OEM
Позволяет
• Подтвердить найденное ранее узкое место
• Грубо определить «ответственного»
15
Slide 17
Повышение быстродействия
Анализ узких мест
Дальнейший анализ
• SQL Tuning advisor
Проверка оптимальности плана запроса
• ASH report (active session history)
Статистика по объектно (таблицы, индексы),
События ожидания, в том числе SQL*Net message
• AWR report
Снимок со всей БД за один час
Использование ресурсов всего сервера
16
Slide 18
Повышение быстродействия
Анализ узких мест
4. Проверка оптимальности плана - SQL Tuning advisor
7% это не уменьшение времени отклика,
это выигрыш в затратах ресурсов БД
После применения профиля необходимо заново
протестировать производительность с помощью Profiler
17
Slide 19
Повышение быстродействия
Промежуточные итоги
• После применения профиля время выполнения операции
не уменьшилось (для данного примера)
• Дальнейшая оптимизация плана запроса практически не
возможна
об этом говорит SQL Tuning Advisor
• SQL Tuning Advisor не выдал рекомендации по индексам и
по секционированию
Далее необходимо:
• Проверить распределение оперативной памяти
• Проверить какие объекты БД являются узким местом
• Проверить оптимально ли размещены сегменты этих
объектов
18
Slide 20
Повышение быстродействия
Анализ узких мест
5. Проверяем достаточно ли оперативной памяти
OEM – Memory Advisors
Для нашего примера увеличение
размера не требуется
19
В данной ситуации памяти не
хватает
Slide 21
Повышение быстродействия
Анализ узких мест
5. Проверяем объекты БД – ASH Report
• ASH хранит данные за последние 5-10 минут при
постоянной рабочей нагрузке.
• Показывает детальную статистику по событиям
ожидания (CPU, Disk)
• Показывает, к каким объектам БД относятся события
ожидания
20
Slide 22
Повышение быстродействия
Анализ узких мест
ASH report
21
Типы операций, объекты БД,
события ожидания
Slide 23
Повышение быстродействия
Анализ узких мест
6. Проверка сегментов хранения данных
22
Slide 24
Повышение быстродействия
Анализ узких мест
Уменьшение времени доступа к объектам
а) с помощью OEM
23
Slide 25
Повышение быстродействия
Анализ узких мест
Уменьшение времени доступа к объектам
Б) вручную
24
Slide 26
Повышение быстродействия
Анализ узких мест
Уменьшение времени доступа к объектам
до:
после:
Перестройка индексов и таблиц дает прирост
в производительности как правило не более 1-2%
25
Slide 27
Повышение быстродействия
Выводы
• Наибольшее время тратится на выполнение чтений с диска
• Дальнейшая оптимизация плана запроса не возможна
Протестировано: применение рекомендуемого профиля не
дало результата
• Увеличение оперативной памяти не требуется
Протестировано: увеличение на 1Гб не дало результата
• Оптимизация структур хранения данных дала выигрышь
всего в 2%
Протестировано: проведена дефрагментация выбранных
объектов
Затрачено времени: 4 часа
26
Slide 28
Повышение быстродействия
Выводы
При текущей конфигурации достигнуть цель в 15 секунд не
возможно
Необходимо:
• либо увеличить скорость доступа к диску
– Реконфигурация RAID
– Покупка дополнительных дисков
• либо уменьшить количество чтений с диска
– Существенно увеличить оперативную память
– Изменить логику обработки данных
27
Slide 29
СПАСИБО ЗА ВНИМАНИЕ
«Корпорация ПАРУС»,
129366, Москва, ул. Ярославская, д.10 корп.4,
(495) 797-89-90, 797-89-91
[email protected]
www.parus.com
Повышение быстродействия сложных
расчетных алгоритмов с помощью
оптимизации запросов Oracle
2014
Slide 2
Повышение быстродействия
Содержание
• Пример из жизни по анализу и настройки
производительности
• Последовательность действий при анализе
• Где и как нужно применять следующие инструменты
Profiler, Hierarchical Profiler, SQL Tuning Advisor,
ASH reports
• Как правильно интерпритировать полученные
данные о производительности
01
Slide 3
Повышение быстродействия
Перед началом анализа
• Убедиться, что статистика находится в актуальном
состоянии (собирается автоматически)
• Используется автоматическое распределение
оперативной памяти
workarea_size_policy = AUTO
memory_target или sga_target + pga_target
• Режим оптимизатора установлен в значение по
умолчанию
optimizer_mode = ALL_ROWS
• Отсутствуют недокументированные параметры
(начинаются с символа _)
02
Slide 4
Повышение быстродействия
Определение пользовательской операции
• Используем SQL-монитор (в составе Парус 8), чтобы
определить точку старта и параметры вызова
• Берем время выполнения по данным SQL Monitor
• Определяем цель оптимизации вместе с пользователем!
Сколько времени должно тратиться на выполнение?
03
Slide 5
Повышение быстродействия
Проверка влияния окружения
Проверяем в «идеальных» условиях:
–
Отсутствует или минимальна нагрузка на сервер
–
Время тестирования не пересекается с автоматическими заданиями
–
Для тестовой БД конфигурация та же, что и для рабочей
засекаем, есть ли разница во времени
Разница не существенна
Переходим к анализу
04
Разница существенна
Поиск других факторов
- OEM
Slide 6
Повышение быстродействия
Пример
1. Фиксируем исходные данные и цель
• Операция: «Сформировать производственный состав»
Модуль «Планирование и учет в дискретном производстве»
Раздел «Инженерные данные» - спецификация
• Текущее время отклика:
Текущее время выполнения 30 секунд для состава из 5000
позиций.
• Цель: 15 секунд
• Точка старта: P_FCPRODCMP_FORMING
05
Slide 7
Повышение быстродействия
Пример
2. Определение узких мест – PLSQL Profiler
• Позволяет определить операции в PLSQL коде, которые
выполнялась наиболее долго – узкое место
• Выбираем 1-3 наиболее долгих операций и начинаем
анализ с самой долгой
• Profiler встроен в программу PLSQL Developer, запускается
одной кнопкой
• Легко перенести вызов из SQL-монитора Паруса
06
Slide 8
Повышение быстродействия
Пример
Profiler в PLSQL Developer
07
Slide 9
Повышение быстродействия
Пример
Profiler в PLSQL Developer
• Возможна разница между первым запуском и
последующими (для тестовой БД можно использовать
alter system flush BUFFER_CACHE;)
• Время серверное и не учитывает SQL*Net message
1-й запуск
2-й запуск
08
Slide 10
Повышение быстродействия
Темы доклада
Узким местом является SQL запрос
for rSP in (
select nvl(T.PR_COND, nSHT_PR_COND) as PR_COND,
T.OPER_NUMB,
H.MANPOWER,
H.EQUIPMENT,
H.FPDACCNT,
H.SUBDIV as SUBDIV_,
nvl(H.SUBDIV, nSHT_SUBDIV) as SUBDIV,
decode( nUSE_SHORT_NAME, 0, nvl(D.CODE, sSUBDIV), nvl(D.SHORTNAME_NOM,
from FCROUTSHTSP
T,
FCROUTSHTSPHIST H,
INS_DEPARTMENT D
where T.PRN = nRN
and H.PRN = T.RN
and H.DATE_FROM = ( select max(DATE_FROM)
from FCROUTSHTSPHIST
where PRN = T.RN
and DATE_FROM <= dDATE )
and H.SUBDIV = D.RN(+)
and nvl(H.SUBDIV, nSHT_SUBDIV) is not null
and H.NORM > 0
order by H.NUMB
09
Slide 11
Повышение быстродействия
Темы доклада
Определение последовательности вызова
• Необходимо для понимания логики работы
• Необходимо, если в «узкое место» можно попасть из
разных частей программы
Hierarchical Profiler или Визуальный анализ кода
10
Slide 12
Повышение быстродействия
Пример
Иерархический Profiler - показывает дерево вызовов
•
•
Доступен в Oracle SQL Developer
Можно запустить самостоятельно
Файл с примером запуска
-- 1) Run
declare
nTMP number;
begin
DBMS_HPROF.START_PROFILING('PLSHPROF_DIR', 'test004.trc');
-- Пользовательская операция
-- без PROLOGUE/EPILOGUE
UDO_P_FCPRODCMP_FORMING(68824, 'Производственный состав',
'29', NULL, 'M_5', 'M_5', to_date('19.11.2014','dd.mm.yyyy'),
NULL, 0, NULL, 'test ak', nTMP);
DBMS_HPROF.STOP_PROFILING;
rollback; -- пользовательские данные
end;
11
Slide 13
Повышение быстродействия
Пример
Иерархический Profiler
•
•
12
Отсортировать по убыванию времени выполнения
Запомнить номер строки выходных данных (слева)
Slide 14
Повышение быстродействия
Анализ узких мест
Анализ узких мест
• Определение причины неисправности («ответственного»):
CPU, Disk, Memory
Инструменты:
SQL Trace, ASH reports, SQL Tuning Advisor, SQLXtrace
Работают с планом запроса и дополнительной статистикой
• Определение возможности устранить неисправность, не
прибегая к изменению кода
- Применение профиля SQL Tuning Profile
- Дополнительные индексы
- Дефрагментация
- Изменение конфигурации сервера
13
Slide 15
Повышение быстродействия
Анализ узких мест
3. Проверка найденного узкого места - OEM
• Определение причины неисправности («ответственного»):
CPU, Disk, Memory
• Инструменты:
01
Slide 16
Повышение быстродействия
Анализ узких мест
Анализ с помощью OEM
Позволяет
• Подтвердить найденное ранее узкое место
• Грубо определить «ответственного»
15
Slide 17
Повышение быстродействия
Анализ узких мест
Дальнейший анализ
• SQL Tuning advisor
Проверка оптимальности плана запроса
• ASH report (active session history)
Статистика по объектно (таблицы, индексы),
События ожидания, в том числе SQL*Net message
• AWR report
Снимок со всей БД за один час
Использование ресурсов всего сервера
16
Slide 18
Повышение быстродействия
Анализ узких мест
4. Проверка оптимальности плана - SQL Tuning advisor
7% это не уменьшение времени отклика,
это выигрыш в затратах ресурсов БД
После применения профиля необходимо заново
протестировать производительность с помощью Profiler
17
Slide 19
Повышение быстродействия
Промежуточные итоги
• После применения профиля время выполнения операции
не уменьшилось (для данного примера)
• Дальнейшая оптимизация плана запроса практически не
возможна
об этом говорит SQL Tuning Advisor
• SQL Tuning Advisor не выдал рекомендации по индексам и
по секционированию
Далее необходимо:
• Проверить распределение оперативной памяти
• Проверить какие объекты БД являются узким местом
• Проверить оптимально ли размещены сегменты этих
объектов
18
Slide 20
Повышение быстродействия
Анализ узких мест
5. Проверяем достаточно ли оперативной памяти
OEM – Memory Advisors
Для нашего примера увеличение
размера не требуется
19
В данной ситуации памяти не
хватает
Slide 21
Повышение быстродействия
Анализ узких мест
5. Проверяем объекты БД – ASH Report
• ASH хранит данные за последние 5-10 минут при
постоянной рабочей нагрузке.
• Показывает детальную статистику по событиям
ожидания (CPU, Disk)
• Показывает, к каким объектам БД относятся события
ожидания
20
Slide 22
Повышение быстродействия
Анализ узких мест
ASH report
21
Типы операций, объекты БД,
события ожидания
Slide 23
Повышение быстродействия
Анализ узких мест
6. Проверка сегментов хранения данных
22
Slide 24
Повышение быстродействия
Анализ узких мест
Уменьшение времени доступа к объектам
а) с помощью OEM
23
Slide 25
Повышение быстродействия
Анализ узких мест
Уменьшение времени доступа к объектам
Б) вручную
24
Slide 26
Повышение быстродействия
Анализ узких мест
Уменьшение времени доступа к объектам
до:
после:
Перестройка индексов и таблиц дает прирост
в производительности как правило не более 1-2%
25
Slide 27
Повышение быстродействия
Выводы
• Наибольшее время тратится на выполнение чтений с диска
• Дальнейшая оптимизация плана запроса не возможна
Протестировано: применение рекомендуемого профиля не
дало результата
• Увеличение оперативной памяти не требуется
Протестировано: увеличение на 1Гб не дало результата
• Оптимизация структур хранения данных дала выигрышь
всего в 2%
Протестировано: проведена дефрагментация выбранных
объектов
Затрачено времени: 4 часа
26
Slide 28
Повышение быстродействия
Выводы
При текущей конфигурации достигнуть цель в 15 секунд не
возможно
Необходимо:
• либо увеличить скорость доступа к диску
– Реконфигурация RAID
– Покупка дополнительных дисков
• либо уменьшить количество чтений с диска
– Существенно увеличить оперативную память
– Изменить логику обработки данных
27
Slide 29
СПАСИБО ЗА ВНИМАНИЕ
«Корпорация ПАРУС»,
129366, Москва, ул. Ярославская, д.10 корп.4,
(495) 797-89-90, 797-89-91
[email protected]
www.parus.com