Oracle Database 11g: Настройка производительности - Глава 3: Использование AWR

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

3. Использование автоматического репозитория нагрузки (AWR)

Автоматический репозиторий нагрузки

AWR это инфраструктура, которая предоставляет сервисы СУБД Oracle для сбора, управления и использования статистики в процессе идентификации проблем и их автоматического устранения.

Инфраструктура AWR состоит из двух важных частей: 

• Сбор статистики в памяти - возможность, которая используется различными компонентами для сбора статистики. Эта статистика хранится в памяти из соображений производительности. Статистика, хранящаяся в памяти доступна через представления производительности V$.
• AWR snapshot - снимки памяти, позволяющие анализировать что происходило раньше. AWR снэпшоты доступны через представления словаря данных (DBA) и в Enterprise Manager Database Control.

Статистика хранится в виде AWR снэпшотов постоянном хранилище по нескольким причинам:

• Статистика должна сохраниться в случае отказа экземпляра.
• Исторические данные для сравнения опорных линий необходимы для определенных типов аналитики
• Защита от переполнения памяти: Когда старая статистика заменяется новой ввиду малого объема памяти, замещаемые данные могут быть сохранены для дальнейшего использования.

Версии статистики из памяти помещаются на диск на регулярной основе фоновым процессом MMON (Manageability Monitor). С AWR сервер БД Oracle предоставляет возможность автоматического сбора и хранения статистики без участия DBA.

Данные, хранящиеся в автоматическом репозитории нагрузки (AWR)

 

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

Active Session History (ASH) сначала захватываются из памяти с интервалом в секунду только для в настоящее время активных сессий. Затем данные ASH сокращаются в десятки раз, сохраняя на диск случайную выборку данных из оперативной памяти. Данные ASH активно используются компонентом Automatic Database Diagnostic Monitor (ADDM) для выявления причин падения производительности.

Отчеты, сгенерированные ADDM, Segment Advisor, и другими также хранятся в AWR для дальнейшего использования.

Статистика, хранящаясяв AWR делится на 2 типа: статистика хранящаяся в памяти, которой можно получить доступ через представления V$ и историческая или постоянная статистика, доступ к которой можно получить через представления DBA_*.  

Репозиторий нагрузки

• Репозиторий нагрузки - это постоянный набор статистики производительности, собирающийся за определенный период времени и принадлежащий пользователю SYS. Репозиторий нагрузки хранится в табличном пространстве SYSAUX и занимает в нем одно из самых главных мест.
• Снимок (Snapshot) - это набор статистики производительности собраный в определенный период времени. Снимки используются для подсчета рейтингов и отслеживания на их основе изменений в производительности экземпляра в течение какого то времени. Каждый Снимок помечается номером, выделяемым последовательностью (snap_id), который является уникальным в рамках репозитория нагрузки.
• По умолчанию снимки создаются каждые 60 минут. Вы можете уменьшить частоту создания снимков изменив параметр INTERVAL. Поскольку внутренние процессы advisor используют эти снимки помните, что увеличение интервала между снимками может повлиять на точность измерений при диагностике. Например установив интервал в 4 часа,  можно пропустить всплески на графике, которые будут заметны при интервале в 1 час.
• В Real Application Clusters окружении каждый снимок охватывает все экземпляры БД в кластере. Снимки на каждом узле кластера используют один snap_id и идентифицируются по Instance ID. 
• Вы можете сделать снимок вручную используя Database Control. Ручное создание снимка поддерживается в связке с автоматическими снимками, генерируемыми системой. Наиболее распространенное применение ручных снимков  - ситуация, когда вам необходимо получить снимок состояния системы между двумя специфическими точками во времени, которые не совпадают с временем в автоматически сгенерированных снимках.

AWR в Database Control

Используя Database Control, вы можете сконфигурировать параметры RETENTION и INTERVAL. 

Политика очистки снимков AWR

Вы контролируете количество исторических данных в AWR установкой периода хранения и интервала снимков. По умолчанию, снимки удаляются автоматически в хронологическом порядке. Снимки, относящиеся к опорным линиям сохраняются до тех пор пока опорная линия не будет удалена или устареет. В системе с 10-ю активными пользователями требуется 200-300 МБ пространства в файле данных для хранения недельных снимков статистики. Потребление дискового пространства зависит напрямую от количества активных сессий в системе. Скрипт utlsyxsz.sql анализирует множество факторов таких например как, размер объектов, расположенных в табличном пространстве SYSAUX, количество активных сессий, частоту создания снимков и время отклика. Скрипт awrinfo.sql генерирует отчет содержащий данные о прогнозируемом размере занятого дискового пространства в SYSAUX. Оба скрипта расположены в директории $ORACLE_HOME/rdbms/admin.

AWR управляет хранением снимков. Каждую ночь процесс MMON очищает снимки, которые старше периода хранения. Если AWR зафиксирует, что закончилось место в табличном пространстве SYSAUX, он автоматически удалит наиболее старые снимки для того, чтобы разместить новые. При этом администратору БД будет направлено уведомление об окончании свободного пространства в SYSAUX.

Настройка снимков AWR

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

• Период хранения: RETENTION указывается в минутах. Значение по умолчанию - 8 дней. 
• INTERVAL между снимками. Минимальное значение - 10 минут, максимальное - 100 лет, Значение по умолчанию - 60 минут.
• Количество SQL, для которых необходимо собирать данные о производительности. Разрешается устанавливать следующие значения: DEFAULT, MAXIMUM, n , где n - это количество SQL выражений, которое можно вычистить при соответствии определенным условиям, таким как время выполнения или CPU time. При указании значения DEFAULT захватывается топ 30, при установке значения параметра STATISTICS_LEVEL = TYPICAL и  топ 100 при установке значения ALL для STATISTICS_LEVEL. При указании значения MAXIMUM в снимок AWR захватываются все SQL, хранящиеся в кэше курсоров.

В исключительных ситуациях автоматическое создание снимков может быть отключено установкой интервала в значение 0. В этом случае вы также не можете создать снимок вручную. Oracle настоятельно рекомендует не отключать автоматическое создание снимков.

Создание снимков вручную

Управление снимками при помощи PL/SQL 

Пакет DBMS_WORKLOAD_REPOSITORY содержит процедуры для управления снимками и опорными линиями. Большинство из  процедур, содержащихся в пакете используются Enterprise manager для управления автоматическим репозиторием нагрузки и редко приходится пользоваться ими вручную.

Создание AWR отчетов в Enterprise Manager

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

Создание отчетов AWR в SQL*Plus 

Вне зависимости от того, где был создан отчет в SQL*Plus или в  EM, он содержит одну и ту же информацию. Скрипт awrrpt.sql, запущенный в SQL*Plus из директории $ORACLE_HOME/rdbms/admin сгенерирует AWR отчет. Для запуска скрипта пользователь должен иметь привилегию SELECT_CATALOG_ROLE. В процессе запуска скрипт запрашивает следуюйщие дополнительные опции: 

• Тип отчета: HTML или текстовый
• Число дней з которого нужно выбрать снимки. Указание количества дней показывает наиболее свежие снимки относительно заданного периода времени. Вы также можете определить Snap_id требуемого снимка, запросив соответствующие столбцы таблицы DBA_HIST_SNAPSHOT, содержащие SNAP_ID и временную метку. 
• SNAP_ID начального и конечного снимков: Пара снимков, которая определяет период отчета.
• Имя файла: Файл указанный пользователем в который будет записан отчет.

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

Как читать AWR отчет

Первая секция AWR отчета представляет следующие диагностические данные: 

• Время  снимков
• Использование памяти 
• Профиль загрузки 
• Эффективность экземпляра в процентах
• Топ 5 foreground процессов 
• Статистика операционной системы, CPU и память использованные экземпляром в заданный для отчета период времени

Топ 5 foreground процессов является хорошей точкой для начала диагностики проблем производительности. Назначение этого блока отчета - показать наиболее значительные события. Пример выше показывает что очень высокий % от DB time был затрачен на ожидание буферов в буферном кэше (buffer busy waits). 

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

Сравнение периодов по AWR отчетам

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

Преимущества использования сравнения периодов

Вы можете использовать отчет сравнения периодов из репозитория нагрузки для сравнения двух периодов. Если AWR отчет показывает AWR данные между двумя снимками (две точки во времени), то отчет сравнения периодов показывает разницу между двумя периодами (или двумя AWR отчетами, что эквивалентно четырем снимкам).

Использование сравнения периодов позволяет определить детали изменения атрибутов производительности и изменения в конфигурации между двумя периодами во времени. Например, если нагрузка на приложение стабильна в рабочее время, но производительность во вторник резко упала между 10:00 и 11:00 утра, генерация отчета сравнения периодов позволит сравнить период с 10:00 до 11:00 в понедельник и во вторник и тем самым выявить изменения конфигурации, профиля нагрузки и статистики между двумя периодами. Два периода, выбранные для сравнения могут быть разными по продолжительности. Если продолжительность периодов разная, отчеты перед сравнением приводятся к одному значению DB time.

Результаты отчета сравнения периодов

На рисунке выше показана часть отчета сравнения периодов, которая определяет статистику изменений между периодами. Этот отчет сравнивает одну и ту же нагрузку, выполненную на по разному сконфигурированных табличных пространствах. Сравнение может быть основано на показателях за одну секунду и за одну транзакцию. Поскольку нагрузка в разные периоды не отличалась, использование сравнения по транзакциям является наиболее предпочтительным. На данном графике видно, то что в первый период транзакции потребляли значительно больше DB time,  чем во второй период. Также отчет сравнения периодов можно сгенерировать запустив скрипт awrddrpt.sql, расположенный в директории $ORACLE_HOME/rdbms/admin.

Содержание отчета сравнения периодов

Секция : Профиль нагрузки 

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

Сравнение периодов: Топ событий ожидания 

Каждый экземпляр, в том числе и хорошо настроенный имеет набор событий ожидания. Эти события ожидания являются потенциальными областями для настройки производительности. В данном случае беспокойство вызывает событие ожидания buffer busy waits, находящееся в верху списка. Это событие ожидания затмевает все остальные события в списке. Во второй период мы можем видеть что событие ожидания buffer busy waits больше не является первым в списке событий ожидания. По предыдущим секциям отчета мы уже знаем что производительность экземпляра была увеличена. Во второй период появилось событие ожидания free buffer waits, а также увеличилось количество событий log file sync, что отразилось на общем времени и DB time. Это наблюдение позволяет провести дальнейшее исследование причин возникновения событий ожидания. Следующим шагом будет просмотр секций, содержащих детальную информацию относительно этих событий ожидания.