Повышение быстродействия сложных расчетных алгоритмов с помощью оптимизации запросов Oracle 2014

Пример из жизни по анализу и настройки производительности Последовательность действий при анализе Где и как нужно применять следующие инструменты Profiler, Hierarchical Profiler, SQL Tuning Advisor, ASH reports Как правильно интерпретировать полученные данные о производительности Повышение быстродействия 01 Содержание

Убедиться, что статистика находится в актуальном состоянии (собирается автоматически) Используется автоматическое распределение оперативной памяти workarea_size_policy = AUTO memory_target или sga_target + pga_target Режим оптимизатора установлен в значение по умолчанию optimizer_mode = ALL_ROWS Отсутствуют недокументированные параметры (начинаются с символа _) Повышение быстродействия 02 Перед началом анализа

Используем SQL-монитор (в составе Парус 8), чтобы определить точку старта и параметры вызова Берем время выполнения по данным SQL Monitor Определяем цель оптимизации вместе с пользователем! Сколько времени должно тратиться на выполнение? Повышение быстродействия 03 Определение пользовательской операции

Проверяем в «идеальных» условиях: –Отсутствует или минимальна нагрузка на сервер –Время тестирования не пересекается с автоматическими заданиями –Для тестовой БД конфигурация та же, что и для рабочей засекаем, есть ли разница во времени Повышение быстродействия 04 Проверка влияния окружения Разница не существенна Переходим к анализу Разница существенна Поиск других факторов - OEM

Операция: «Сформировать производственный состав» Модуль «Планирование и учет в дискретном производстве» Раздел «Инженерные данные» - спецификация Текущее время отклика: Текущее время выполнения 30 секунд для состава из 5000 позиций. Цель: 15 секунд Точка старта: P_FCPRODCMP_FORMING Повышение быстродействия Пример Фиксируем исходные данные и цель

Позволяет определить операции в PLSQL коде, которые выполнялась наиболее долго – узкое место Выбираем 1-3 наиболее долгих операций и начинаем анализ с самой долгой Profiler встроен в программу PLSQL Developer, запускается одной кнопкой Легко перенести вызов из SQL-монитора Паруса Повышение быстродействия Пример Определение узких мест – PLSQL Profiler

Повышение быстродействия Пример 07 Profiler в PLSQL Developer

Возможна разница между первым запуском и последующими (для тестовой БД можно использовать alter system flush BUFFER_CACHE ;) Время серверное и не учитывает SQL*Net message Повышение быстродействия Пример 08 Profiler в PLSQL Developer 1-й запуск 2-й запуск

Повышение быстродействия Темы доклада 09 Узким местом является 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, sSHORTNAME) ) as SUBDIV_CODE 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 0 order by H.NUMB

Необходимо для понимания логики работы Необходимо, если в «узкое место» можно попасть из разных частей программы Hierarchical Profiler или Визуальный анализ кода Повышение быстродействия Темы доклада 10 Определение последовательности вызова

Доступен в Oracle SQL Developer Можно запустить самостоятельно Повышение быстродействия Пример 11 Иерархический Profiler - показывает дерево вызовов -- 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(' ','dd.mm.yyyy'), NULL, 0, NULL, 'test ak', nTMP); DBMS_HPROF.STOP_PROFILING; rollback; -- пользовательские данные end; Файл с примером запуска

Отсортировать по убыванию времени выполнения Запомнить номер строки выходных данных (слева) Повышение быстродействия Пример 12 Иерархический Profiler

Определение причины неисправности («ответственного»): CPU, Disk, Memory Инструменты: SQL Trace, ASH reports, SQL Tuning Advisor, SQLXtrace Работают с планом запроса и дополнительной статистикой Определение возможности устранить неисправность, не прибегая к изменению кода -Применение профиля SQL Tuning Profile -Дополнительные индексы -Дефрагментация -Изменение конфигурации сервера Повышение быстродействия Анализ узких мест 13 Анализ узких мест

Определение причины неисправности («ответственного»): CPU, Disk, Memory Инструменты: Повышение быстродействия Анализ узких мест Проверка найденного узкого места - OEM

Повышение быстродействия Анализ узких мест 15 Анализ с помощью OEM Позволяет Подтвердить найденное ранее узкое место Грубо определить «ответственного» Позволяет Подтвердить найденное ранее узкое место Грубо определить «ответственного»

Повышение быстродействия Анализ узких мест 16 Дальнейший анализ SQL Tuning advisor Проверка оптимальности плана запроса ASH report (active session history) Статистика по объектно (таблицы, индексы), События ожидания, в том числе SQL*Net message AWR report Снимок со всей БД за один час Использование ресурсов всего сервера

Повышение быстродействия Анализ узких мест Проверка оптимальности плана - SQL Tuning advisor 7% это не уменьшение времени отклика, это выигрыш в затратах ресурсов БД После применения профиля необходимо заново протестировать производительность с помощью Profiler

После применения профиля время выполнения операции не уменьшилось (для данного примера) Дальнейшая оптимизация плана запроса практически не возможна об этом говорит SQL Tuning Advisor SQL Tuning Advisor не выдал рекомендации по индексам и по секционированию Далее необходимо: Проверить распределение оперативной памяти Проверить какие объекты БД являются узким местом Проверить оптимально ли размещены сегменты этих объектов Повышение быстродействия 18 Промежуточные итоги

Повышение быстродействия Анализ узких мест Проверяем достаточно ли оперативной памяти OEM – Memory Advisors Для нашего примера увеличение размера не требуется В данной ситуации памяти не хватает

Повышение быстродействия Анализ узких мест Проверяем объекты БД – ASH Report ASH хранит данные за последние 5-10 минут при постоянной рабочей нагрузке. Показывает детальную статистику по событиям ожидания (CPU, Disk) Показывает, к каким объектам БД относятся события ожидания

Повышение быстродействия Анализ узких мест 21 ASH report Типы операций, объекты БД, события ожидания

Повышение быстродействия Анализ узких мест Проверка сегментов хранения данных

Повышение быстродействия Анализ узких мест 23 Уменьшение времени доступа к объектам а) с помощью OEM

Повышение быстродействия Анализ узких мест 24 Уменьшение времени доступа к объектам Б) вручную

Повышение быстродействия Анализ узких мест 25 Уменьшение времени доступа к объектам до: после: Перестройка индексов и таблиц дает прирост в производительности как правило не более 1-2%

Наибольшее время тратится на выполнение чтений с диска Дальнейшая оптимизация плана запроса не возможна Протестировано: применение рекомендуемого профиля не дало результата Увеличение оперативной памяти не требуется Протестировано: увеличение на 1Гб не дало результата Оптимизация структур хранения данных дала выигрышь всего в 2% Протестировано: проведена дефрагментация выбранных объектов Затрачено времени: 4 часа Повышение быстродействия 26 Выводы

При текущей конфигурации достигнуть цель в 15 секунд не возможно Необходимо: либо увеличить скорость доступа к диску –Реконфигурация RAID –Покупка дополнительных дисков либо уменьшить количество чтений с диска –Существенно увеличить оперативную память –Изменить логику обработки данных Повышение быстродействия 27 Выводы

СПАСИБО ЗА ВНИМАНИЕ «Корпорация ПАРУС», , Москва, ул. Ярославская, д.10 корп.4, (495) ,