Май 2005
Профессионалу администратору
Алексей Данченков,
Ведущий аналитик,
эксперт по технологиям Oracle
компания ЛИВС
От STATSPACK к ORACLE10g AWR
[От редакции OM/RE: Алексей Данченков является соавтором книги
"Oracle Tuning. Oracle Time-series Optimization with the Automatic Workload Repository",
анонс которой помещен в этом выпуске журнала.
По нашей просьбе познакомить читателей с содержанием своей книги, он написал эту статью.
Мы надеемся, что найдется российский издатель, который порадует нас переводом этой весьма актуальной книги.]
На протяжении нескольких лет администраторы баз данных Oracle (АБД) использовали утилиту STATSPACK для мониторинга и настройки производительности. Пакет STATSPACK входил в стандартный набор утилит, поставляемых вместе с программным обеспечением сервера БД ORACLE. Впервые пакет STATSPACK появился в версии Oracle 8.1.6 и явился логическим продолжением и развитием известных скриптов UTLBSTAT/UTLESTAT из седьмой версии сервера Oracle. С выходом новых версий сервера БД Oracle расширялись и возможности пакета STATSPACK.
Собственно идея пакета STATSPACK заключается в том, что он позволяет хранить в базе данных историю статистик, описывающих производительность работы сервера БД Oracle. Поставлямые с пакетом STATSPACK скрипты позволяли получить отчет о производительности БД за интересующий отрезок времени, когда производительность БД была низкой, и сравнить его с другим временным интервалом, когда, например, производительность сервера Oracle была приемлимой. Данный подход позволяет быстро находить зависимости и причины возникновения проблем с производительностью базы данных Oracle.
Несомненными достоинтвами пакета STATSPACK авляются легкость и простота его установки и использования, понятная структура хранилища данных STATSPACK, где хранятся исторические статистические данные. Надо сказать, что большинство таблиц хранилища данных пакета STATSPACK (все они имеют префикс STATS$) имеют сходную структуру с соответствующими динамическими представлениями словаря данных V$, снимок информации которых и хранят таблицы репозитория STATSPACK. Это позволяет написать свои собственные отчеты по производительности, дополняющие стандартные, которые строятся скриптом spreport.sql.
Более того, хранилище данных пакета STATSPACK предоставляет возможность для создания программного обеспечения для более удобного визуального анализа производительности БД в виде графических отчетов, анализа зависимостей и трендов, и т.д. В качестве примера, можно привести пакет Statspack Viewer, созданный автором статьи, который предназначен для визуализации в виде графичексих отчетов информации STATSPACK и более удобного анализа исторических данных.
В последней версии сервера Oracle10g пакет STATSPACK получил дальнейшее развитие в виде встроенного механизма Automatic Workload Repository (AWR). Надо сказать, что в отличие от STATSPACK, этот механизм требует отдельного лицензирования для его использования, наряду с такими технологиями как Automatic Database Diagnostic Monitor (ADDM) и др. К счастью, у администраторов БД все же осталась возможность использования пакета STATSPACK в Oracle10g. Так что же представляет их себя механизм Automatic Workload Repository, который по праву можно считать приемником утилиты STATSPACK?
Automatic Workload Repository представляет из себя набор внутренних таблиц словаря данных БД Oracle и специальный фоновый процесс MMON, котрый появился в Oracle10g. Стоит отметить, что механизм AWR уже встроен в ядро БД Oracle10g и никакой дополнительной установки не требует.
Схематично архитектуру AWR и его взаимодействие с другими компонентами Oracle10g можно представить в виде схемы:
Схема 1 – Архитектура AWR.
Как видно из схемы, фоновый процесс MMON периодически опрашивает динамичексие представления V$ и таблицы X$ и переносит статистическую информацию в свои внутренние таблицы словаря данных с префиксом WRH$. По умолчанию, сбор статистики процессом MMON выполняется каждый час. Процесс MMON также ответственен за удаление устаревшой информации из репозитария AWR. В отличие от утилиты STATSPACK, AWR может хранить статистические данные в виде “скользящего окна”. Например, AWR хранит по умолчанию статистику только за последние семь дней, хотя его можно настроить так, что статистика будет удаляться только вручную, как это предусмотрено в STATSPACK. В обязанности фонового процесса MMON также входит сбор статистики ожиданий для новой компоненты Active Session History (ASH), которая позволяет просматривать историю работы каждой активной сессий БД.
Статистичиская информация, которая хранится в хранилище данных AWR, не лежит мертвым грузом, ожидая своего использования АБД, как это было в STATSPACK, а активно используется различными компонентами. Из схемы 1 видно, что эта информация активно используется как внутренними компонентами ядра БД Oracle10g, так и внешними клиентами. К внутренним потребителям статистики AWR можно отнести такие новые компоненты, как Automatic Database Diagnostic Monitor (ADDM), SQL Tuning Advisor, SQL Access Advisor, Automatic Segment Advisor и т.д. Все эти компоненты являются новыми в Oracle10g и служат для автоматизации и выработки рекомендаций по настройке производительности различных подсистем БД Oracle10g. Например, компонента ADDM автоматически выполняет анализ текущих данных в AWR после каждого нового снимка и показывает возможные проблемы в производительности БД (узкие места), а также предлагает свои рекомендации по возможным путям их решения. Внешними потребителями статистики AWR является Oracle10g Enterprise Manager, Statspack Viewer и т.д.
Хранилище данных механизма AWR содержит таблицы следующих типов:
- Таблицы с префиксом WRM$ хранят метаданные AWR. Например, таблица wrm$_snapshot хранит информацию обо всех снимках, которые хранятся в репозитории.
- Таблицы с префиксом WRH$ хранят собственно статистическую историю. Напрмер, таблица wrh$_sysstat содержит снимки динамического представления V$sysstat.
- Таблицы с префиксом WRI$ содержат служебную информацию различных компонент Oracle10g типа SQL Tuning Advisor, которые используют информацию AWR. Например, таблица wri$_adv_tasks содержит историю о всех запусках advisory компонент, использующих данные AWR.
Для доступа к данным AWR Oracle10g предоставляет набор административных (DBA) представлений, представленный ниже:
Приведенные представления позволяют просматривать такие статистические данные, как:
- Времена ожидания ресурсов БД (Foreground и background wait events). Эту статистическую информацию можно использовать для первоначального определения “узких” мест в производительности БД.
- Метрики, характеризующие скорость доступа к различным ресурсам БД. Например, процессорное время на вызов пользователя (CPU Tome Per User Call) или число логических чтений в секунду (Consistent Read Gets Per Sec). Этот вид статистики появился в Oracle10g и представляет собой набор автоматически вычисляемых процессом MMON статистик, которые раньше надо было вычислять вручную.
- Временные статистики, описывающие распределение использования процессорного времени (Time model). Этот вид статистик также является новшеством БД версии 10g и характеризует использование процессорного времени для выполнения определенных задач. Например, метрика
sql execute elapsed time характеризует фактическое время выполнения запросов SQL. Наибольший интерес представляет собой метрика DB time, которая описывает общее процессорное время, затраченное Oracle для обслуживания всех пользовательских операций. Эту метрику можно использовать для мониторинга общнй загрузки БД.
- Системные статистики, описывающие производительность экземпляра Oracle и операции ввода/вывода в табличные пространства/файлы данных БД.
- Статистика работы операционной системы. В последней версии сервера БД Oracle10g появилась возможность отслеживать основные параметры производительности операционной системы.
- Статистическая информация по SQL-запросам. Данный вид статистики позволяет АБД Oracle отслеживать ресурсоемкие SQL-запросы, используя различные критерии. Например, выбирать SQL-запросы, сделавшие наибольшое число операций ввода/вывода в файлы данных.
- Статистика по истории доступа к сегментам данных. Данный вид статистики позволяет выявить “горячие” сегменты данных используя различные критерии, напрмер, число логических или физических чтений данных сегмента.
Механизм AWR предоставляет стандартный скрипт awrrpt.sql, аналог скрипта STATSPACK spreport.sql, который позволяет получить стандартный отчет по производительности за выбранный временной интервал в виде текстового файла или HTML-страницы. Результирующий отчет AWR очень похож на отчет STATSPACK, так как имеет схожую структуру и секции, где представлены статистические данные. Также АБД Oracle могут использовать графический интерфейс Oracle10g Enterprise Manager для доступа к статистической информации, хранящейся внутри AWR. Большинство представлений DBA_HIST имеют схожую структуру с соответствующими таблицами пакета STATSPACK, что позволяет легко адаптировать для AWR существующие отчеты по анализу производительности, предназначенные для работы с хранилищем STATSPACK,
Для управления настройками AWR Oracle10g АБД Oracle может использовать стандартый пакет PL/SQL DBMS_WORKLOAD_REPOSITORY и графический интерфейс Oracle10g Enterprise Manager. Пакет DBMS_WORKLOAD_REPOSITORY имеет следущие вызовы:
- Процедура MODIFY_SNAPSHOT_SETTINGS позволяет настраивать частоту, с которой процесс MMON будет сохранять в хранилище AWR новый снимок статистик, а также временной интервал хранения этого снимка в БД. Текущие значения этих настроек можно посмотреть, используя следующий SQL-запрос:
select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval (Minutes)",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval (Minutes)"
from dba_hist_wr_control;
- Процедура CREATE_SNAPSHOT позволяет вручную сохранить в репозитории AWR новый статистический снимок. Единственный параметр этой процедуры задает детализацию статистической информации, сохраняемой в БД. Значение по умолчанию для этого параметра задается параметром инициализации экземпляра Oracle STATISTICS_LEVEL. Допустимые значения этого параметра – TYPICAL и ALL. Обычно рекомендуется использовать уровень статистики TYPICAL, который достаточен для мониторинга производительности БД.
- Процедура DROP_SNAPSHOT_RANGE позволяет в ручную удалять статистичексие данные для заданного набора снимков.
- Процедура CREATE_BASELINE позволяет создавать именованные наборы снимков для последующего сравнения производительности БД для различных временных интервалов.
- Функции AWR_REPORT_TEXT и AWR_REPORT_HTML дают возможность вручную строить отчеты для заданных временных инревалов.
Константы пакета DBMS_WORKLOAD_REPOSITORY MIN_INTEVAL и MAX_INTERVAL задают допустимые минимальные и максимальные значения для интервала сбора статистики в минутах соответственно, а MIN_RETENTION и MAX_RETENTION задают минимальное и максимальное время хранения статистики в БД соответственно.
Как было сказано выше, хранилище данных AWR служит источником данных о работе сервера БД для различных потребителей. За рамками этой статьи останется знакомство с новыми интеллектуальными компонентами по настройке производительности различных подсистем сервера БД Oracle10g, которые используют хранилище AWR для выработки своих рекомендаций. Я же хотел бы привести несколько примеров того, как можно использовать историческую информацию для анализа тенденций в работе сервера БД Oracle10g. Анализ тенденций, или трендов, позволяет увидеть картину того, какие характеристики производительности имеет БД по часам в течение дня или по дням недели. Обычно такая усредненная картина достаточно точно описывает поведение БД для конкретного набора приложений и конечных порльзователей. Это позволяет более эффективно определять узкие места в работе БД и моменты времени, когда возможна деградация производительности сервера БД.
Например, администратор БД хочет знать, какой в среднем объем физических чтений и в какие моменты времени в течение дня генерирует промышленная БД. Приведенный ниже простой запрос к хранилищу AWR отвечает на этот вопрос:
set pages 999
set pagesize 1000
break on snap_time skip 2
col snap_time format a19 heading “Day Hour”
col avg_value format 999,999,999 heading “Avg Physical Reads”
select
to_char(begin_interval_time,'hh24') snap_time,
avg(value) avg_value
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = 'physical reads'
group by
to_char(begin_interval_time,'hh24')
order by
to_char(begin_interval_time,'hh24')
;
Пример результата работы этого скрипта может выглядеть следующим образом:
Day Hour Avg Physical Reads
------------------- ------------------
00 75,114
01 76,200
02 75,395
03 70,028
04 76,677
05 75,874
06 170,540
07 77,164
08 78,073
09 281,816
10 382,518
11 581,654
12 786,035
13 472,357
14 271,752
15 267,471
16 174,219
17 273,576
18 269,362
19 274,765
20 174,083
21 169,860
22 75,207
23 74,534
Из приведенного выше отчета видно, что наибольший объем операций физического чтения приходится в среднем на период времени с 11 до 13 часов, что может вызвать нежелательные задержки в работе пользователей. Знание подобного рода информации позволяет АБД более точно локализовать причины подобного рода проблем.
Тот же отчет, но по дням недели выглядит следующим образом:
set pages 999
set pagesize 1000
break on snap_time skip 2
col snap_time format a19 heading “Week Day”
col avg_value format 999,999,999 heading “Avg Physical Reads”
select
to_char(begin_interval_time,'day') snap_time,
avg(value) avg_value
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = 'physical reads'
group by
to_char(begin_interval_time,'day')
order by
2 desc
;
Week Day Avg Physical Reads
------------------- ------------------
wednesday 539,627
monday 495,749
tuesday 483,313
thursday 305,815
friday 297,250
sunday 190,185
saturday 170,332
Еще один пример аналитического отчета, приведенный ниже, показывает усредненное время ожидания по часам суток, что позволяет определить, в какие моменты пользовательские сессии потенциально могут иметь задержку с ответом на свои запросы, вызванную ожиданием каких-либо ресуров БД:
select
TO_CHAR(h.sample_time,'HH24') "Hour",
Sum(h.wait_time/100) "Total Wait Time (Sec)"
from
v$active_session_history h,
v$event_name n
where
h.session_state = 'ON CPU'
and
h.session_type = 'FOREGROUND'
and
h.event_id = n.EVENT_ID
and
n.wait_class <> 'Idle'
group by
TO_CHAR(h.sample_time,'HH24');
Hour Total Wait Time (Sec)
---- ---------------------
11 219
12 302,998
13 60,982
14 169,716
15 39,593
16 299,953
17 122,933
18 5,147
Таким образом, механизм AWR наряду со стандартым набором отчетов предоставляет широкие возможности по более детальному анализу производительности БД в различных временных срезах. Использование AWR совместно с такими новыми технологиями Oracle10g как Active Session History, улучшенный Wait Event Interface и набором встроенных экспертов типа ADDM, SQL Tuning Advisor и другие значительно облегчает задачи администрирования, мониторинга и поддержания производительности больших систем Oracle на необходимом уровне.
|
