Oracle Database 11g: Настройка производительности - Глава 4: Определение проблем

Oracle Database 11g Настройка производительности.

4. Определение проблем

Проблемы могут возникнуть в любое время. DBA в проактивном режиме должен следить за проблемами и исправлять их еще до их возникновения. Раньше, исследование и определение проблемы были очень утомительным и зависящим от пользовательских отзывов процессом. Использование пользовательских отзывов важно, но чаще всего субъективно и не воспроизводимо. В  базе данных Oracle 11g большенство из информации, ранее предоставляемой пользователями может быть получено в интерфейсе Enterprise Manager, например: 

• Мониторинг текущего состояния экземпляра и сравнение его с предыдущим состоянием. 

1. Использование Statspack или AWR для сбора данных о производительности на регулярной основе поможет на ранней стадии выявить изменения в производительности до того как они станут заметны для пользователей.
2. Использование средства ЕМ и операционной системы позволяет мониторить нагрузку на процессор, память и другие системные ресурсы, являющиеся ключевыми для производительности экземпляра.

• Внимательно исследуйте отчеты AWR и STATSPACK.

1. Используйте доступные средства, такие как отчеты Statspack или AWR для определения SQL выражений в приложениях, потребляющие больше всего ресурсов. Изменились ли они за последнее время? 
2. Проверяйте alert.log файлы трассировки на наличие ошибок, которые могут дать ключ к быстрому решению проблемы. Не игнорируйте системные журналы и журналы приложения.
3. Убедитесь, что установленные параметры инициализации не влияют на производительность системы.
4. Соберите статистику экземпляра и операционной системы. ADDM сфокусирует ваше внимание на компонентах, настройка производительности которых даст наибольшее преимущество.

Ограничение области 

Где возникла проблема в операционной системе, в экземпляре БД или в SQL коде приложения? Не всегда можно легко и однозначно ответить на этот вопрос. Медленно работающий SQL может привести к повышенному количеству операций чтения/записи, проявляясь как проблемы I/O. Неправильно выставленный размер компонентов памяти (вопрос конфигурации экземпляра) может привести к свапированию в операционной системе. Неправильно сконфигурированные диски может проявляться как проблема с настройкой экземпляра, вызывая большое количество ожиданий redo или commit и другие проблемы. Исключайте возможности. Когда в экземпляре появляются проблемы с вводом/выводом, сравните I/O статистику на уровне БД и операционной системы. Отличия могут вывести вас на причину возникновения проблемы. Например: Если среднее время ожидания табличного пространства больше обычного, может говорить о следующем: 

• Аппаратное обеспечение: Файл лежит на медленном диске или на входящей RAID конфигурации.
• Операционная система: Операционная система занята другими файлами на том же диске или партиции. 
• Экземпляр: Табличное пространство создано с отличными от других табличных пространств опциями, другие нагруженные файлы данных расположены на том же диске или партиции (система ввода/вывода не сбалансирована между всеми дисками).
• Приложение: Приложение генерирует повышенный ввод/вывод в результате неправильного плана выбранного оптимизатором, в следствие устаревшей статистики, неэффективных индексов, или по другим причинам. 

Определите область возникновения проблемы для того чтобы сфокусировать ваши усилия на решении, которое принесет наибольшую пользу.

Расстановка приоритетов.

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

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

Каждый серверный процесс обычно находится в одном из трех состояний: 

• Idle: Процесс ждет чего либо
• Running code: Использование процессора или находится в очереди выполнения
• Waiting(blocked): Процесс ждет освобождения ресурса или завершения текущей активности.

Топ 5 Событий

Топ 5 событий всегда содержит данные. Данные, представленные на рисунке выше говорят о том,  что экземпляр использует CPU. 

Секция Instance CPU показывает что экземпляр использует 65% от доступных ресурсов процессора. Звучит неубедительно. Предполагается, что экземпляр использует процессорную мощность, а не простаивает. Как упоминалось ранее, настройка производительности может сократить как время ожидания, так и время обработки. В данном случае требуется сокращение времени обработки выражения. Для того чтобы сократить время обработки, необходимо проверить область SQL.

Пример расстановки приоритетов 

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

AWR отчет, выдержка которого представлена на рисунке выше, показывает что CPU time (время пользовательских вызовов) составляет 474,04 секунды. Время затраченное на обработку пользовательских выводов составляет 44.85% от общего DB time. "sql execute elapsed time" показан 1050.06 секунд (данная цифра включает время ожидания запросов). Исходя из этой секции, события ожидания в процессе выполнения SQL выражений занимают значительное время, и ведет вас к просмотру статистики ожидания в процессе выполнения SQL выражений и к SQL отчетам для дальнейшего выявления SQL выражений и их настройке.

Значение показателя "% от DB time " можно использовать для измерения прогнозированного прироста производительности. Если "sql execute elapsed time" будет сокращено, максимально возможный прирост будет составлять 1050 секунд или 99%.  Выполнение SQL всегда будет занимать какое то время. Поэтому, актуальное увеличение производительности может быть намного ниже, чем в приведенном примере, однако предположительный прирост производительности вы можете получить используя показатель DB time.

Отчет топ SQL

Следующие секции отчета топ sql сортируют данные содержащиеся в данном блоке по использованию ресурсов: 

• SQL сортированный по времени исполнения 
• SQL сортированный по CPU time
• SQL сортированный по получению данных из буфера
• SQL сортированный по чтениям
• SQL сортированный по выполеннию
• SQL сортированный по запросам на разбор
• SQL сортированный по использованию разделяемой памяти 
• SQL сортированный по версионности

Данная сортировка позволяет найти проблемное SQL выражение. Вы также можете посмотреть полный список SQL с текстами выражений в секции SQL text. В данном примере выражение приведенное ниже ответственно за большую часть активности экземпляра.

Распространенные проблемы, требующие настройки производительности 

• Настройка неэффективных или высоко нагруженных SQL выражений оказывает огромное влияние на сокращение использования памяти, CPU и других ресурсов системы. Процесс настройки SQL включает в себя некорректно написаный код, неэффективное использование индексов, некорректно выбранный план исполнения или излишняя сортировка. В данной серии статей не будет рассматриваться настройка кода SQL выражений, его я опишу в следующей серии статей по настройке SQL. 
• Такие проблемы как избыточное установление соединений с БД, избыточный разбор SQL кода, и высокая конкуренция за ресурсы для маленького количества данных (также известно как конкуренция на уровне приложения) могут существенно снизить производительность экземпляра.
• Проблемы с использованием памяти также находится вверху списка проблем с производительностью экземпляра. Установка соответствующих размеров областей памяти включая Shared Pool буферный кеш и PGA сокращает конкуренцию за ресурсы памяти, а также косвенно влияет на потребление CPU и сокращение ввода/вывода.
• Высокий уровень конкурентной активности, множество процессов или пользователей могут привести к конкуренции за общедоступные ресурсы, которая может проявляться в форме различных ожиданий. Множество ресурсов может быть доступно только одним процессом одновременно. Другие пользователи, параллельно обращаясь к занятому ресурсу создают конкуренцию.
• В любой БД проблемы с вводом/выводом, такие как например неправильное распределение файлов на медленных дисках может быть причиной проблем с производительностью. в OLTP системах, количество генерируемого undo и redo может стать причиной падения производительности ввода/вывода или памяти.
• Некоторые проблемы о которых сообщает пользователь могут не проявиться в отчетах, которые делаются на основе снимков с интервалом 30 минут и больше. В СУБД Oracle имеются дополнительные инструменты (ASH), которые позволяют DBA просматривать статистику и показатели метрик на небольшом участке времени в недалеком прошлом.
• Множество БД постепенно меняются. Меняется количество пользователей, количество данных, число отчетов, количество модулей приложения, которые её используют. Эти изменения могут также вести к падению производительности. В проактивном режиме DBA должен собирать и сохранять наборы статистики с момента когда база данных работает штатно, до момента падения производительности для того, чтобы определить разницу. 
• Окружение в котором находится БД редко остается неизменным. Патчи, обновления, установка нового аппаратного обеспечения или изменение настроек экземпляра могут изменить производительность системы. Иногда изменения в одной области дают прирост в производительности системы, в то же время вызывая падение производительности в другой.
• Проблемы с блокировками, не типичные проблемы, однако если они возникают, устранить их является критически важной задачей.

Фазы жизненного цикла процесса настройки производительности

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

Разработка: Дизайн  приложения и программирование

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

Тестирование: Конфигурация БД

Фаза тестирования является продолжением фазы разработки, с использованием более реальных тестов, с использованием продуктивного программного и аппаратного обеспечения.

Внедрение: Добавление нового приложения к существующей БД

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

Внедрение в продуктивное окружение: Выявление проблем и настройка

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

Миграция, обновление, смена окружения

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

Настройка в процессе жизненного цикла

Настройка в процессе жизненного цикла включает в себя 2 направления деятельности: проактивная и реактивная настройка. В процессе фаз проектирования, разработки, тестирования настройка больше проактивная, то есть сценарии и test case разрабатываются заранее. Результаты тестирования измеряются и сравниваются с другими результатами при измененной конфигурации. В процессе внедрения и эксплуатации настройка в большей степени реактивная. Необходимость в гипотетической нагрузке отпадает с появлением новых пользователей и нагрузки на систему, но также уменьшается возможность прогнозирования проблем с производительностью. Вы можете проводить мониторинг для выявления изменений и трендов в метках производительности. Из информации полученной в результате мониторинга, вы можете определять проблемы производительности и уменьшать их влияние на систему до того как пользователи это заметят.

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

Дизайн и разработка приложения

Методология настройки производительности, которой вы следуете в процессе фаз проектирования и разработки должна быть сфокусирована на общее устранение узких мест в системе: нормализация, структуры данных, точки сериализации ресурсов, важные отчеты, и запросы, обрабатывающие большой обьем данных. На самой ранней стадии проектирования уже известны основные функции, планируемые в приложении.  Выбор неподходящего уровня нормализации данных имеет достаточно серьезные последствия. Избыточно нормализованная архитектура может повлечь за собой большое количество объединений таблиц в процессе запроса данных, которые потенциально могут создать существенную нагрузку на доступные ресурсы. Недостаточный уровень нормализации приведет к другим проблемам: не консистентные данные, постоянное комплексное чтение данных, и проблемы связанные с миграцией данных в другие схемы БД. Оптимальным решением является сначала проектирование полностью нормализованной архитектуры и затем её частичная денормализация со встроенными проверками консистентности данных.

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

Каждое из перечисленных узких мест должно иметь test case. Эти test case должны быть настроены в соответствии с методологиями, используемыми продуктивным окружением. Для тестирования должна быть собрана статистика, в процессе тестирования должны быть устранены все узкие места.

Тестирование настроек экземпляра БД

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

Нагрузочное тестирование должно максимально использовать аппаратные ресурсы. Тогда данное тестирование покажет максимально и часто используемые ресурсы. Данные ресурсы, ограничивают производительность системы на аппаратном уровне.

DBA использует временную модель и события ожидания на каждой из фаз для выявления узких мест и устраняет эти узкие места.

Внедрение

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

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

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

Продуктивное окружение

• Настройка на остальных фазах в большинстве своем проактивная. Вы ищете потенциально возможные проблемы и устраняете их до того как о них узнают пользователи.
• Тюнинг продуктивной БД очень часто также реактивный. Что то пошло не так: Отчет, который работал минуты, теперь обрабатывается часы, время отклика увеличилось и пользователи жалуются, резервное копирование не выполнилось в заданное время.
• Что то изменилось: Добавилось много пользователей ? Запустили новый отчет, потребляющий много ресурсов ? Что то изменилось в операционной системе? 
• Настройка продуктивной системы в работе которой в определенный момент были зафиксированы проблемы, зависит от знания или определения изменений в системе. Сравнение отчета статистики опорных линий, полученного в момент нормального функционирования БД с отчетом полученным во время проявления проблемы позволяет получить разницу между двумя периодами, которая является причиной возникновения проблемы.
• Процесс настройки базы данных, не имеющей опорных линий более сложен, но возможен. Используется та же методология и настраиваются приоритетные компоненты. Для идентификации проблем используется временная модель. Продумываются возможные решения начиная с дизайна приложения до последующих шагов. Затем выбранные решения применяются на тестовой системе для определения потенциального прироста производительности. 

Процесс настройки производительности при помощи ADDM

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

ADDM консолидирует задачи ручной настройки производительности и позволяет быстро выявить области настройки, которые дадут максимальный прирост производительности. ADDM запускается автоматически каждый раз когда делается снимок AWR. Рекомендации из последнего отчета ADDM отображаются на домашней странице Enterprise Manager.

Ресурсы, используемые для настройки производительности

Oracle предоставляет большой набор ресурсов для настройки производительности. Первый и наиболее применимый ресурс - документация по продукту. Oracle Database Performance Tuning Guide покрывает широкий спектр типичных проблем с производительностью и содержит шаги по их устранению. В Oracle Database 2 Day + Performance Tuning Guide - обзор методологий настройки производительности  и ориентированный на задачи подход к устранению типичных проблем производительности.

My Oracle Support (MOS) содержит базу документов, доступных всем подписанным на сервис поддержки. В этой базе содержится большое количество документов, описывающих методы  диагностики и устранения проблем. 

Помимо доступа к базе знаний Oracle Support при наличии активного контракта технической поддержки позволяет размещать запросы на решение проблем с производительностью. Oracle Support разработал инструмент Remote Diagnostics Report tool, который используется для сбора большого количества информации о системе, чтобы в дальнейшем помочь команде поддержки в решении возникшей проблемы.

Отчет RDA

Remote Diagnostics Agent (RDA) разработан для предоставления полного набора информации, требуемой службой поддержки. RDA - полезный инструмент диагностики производительности поскольку он консолидирует информацию в один, доступный к использованию отчет. 

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

Обзор средств мониторинга и настройки производительности

В следующих главах мы будем использовать большенство из средств представленных на рисунке выше. Самые светлые блоки содержат средства, которые содержат необработанные данные. Темные блоки содержат средства, которые обрабатывают исходные сырые данные для получения более детальной информации.  Часто информация сформированная в отчет как например отчет Active Session History (ASH).

Представления производительности  - подвид динамических представлений производительности, или V$ представления, которые хранят статистику в памяти.

Файлы трассировки очень тяжело читать в том виде в котором их формирует Oracle, и в не форматированном виде используются только службой технической поддержки.  Только определенные виды трассировки могут быть отформатированы утилитой tkprof. Утилита trcsess уникальное приложение, позволяющее скомбинировать и отфильтровать файлы трассировки для того чтобы извлечь статистику для одной сессии, сервиса или модуля из множества файлов трассировки. Использование данной утилиты рассмотрим позже в следующих главах.

Блок services показывает что направление мониторинга производительности организовано вокруг сервисов. Статистика объединяется по сервисам и многие отчеты могут формироваться в разрезе сервиса. Статистика, собранная по сервису будь то схема, экземпляр, или сессия может детализовать представление производительности. 

Средства, представленные на рисунке выше форматируют данные для того чтобы сделать их более читабельными. Некоторые из них анализирует данные для проактивного выявления проблем и выдачи рекомендаций по их предупреждению. Все эти средства будут более подробно рассмотрены в следующих статьях по настройке производительности СУБД Oracle 11g.