Анализ тенденций базы данных Oracle с использованием утилиты STATSPACK

Oracle Database Trend Analysis Using STATSPACK, by Donald K. Burleson

Источник: журнал Oracle Magazine, Publish Online (http://www.oracle.com/oramag/webcolumns/2000/index.html?statspackrpts.html

Дональд К. Бурлесон

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

Утилита STATSPACK для выпусков Oracle8

Утилита STATSPACK впервые появилась в Oracle8i (версия 8.1.6), однако, Oracle предлагает доступную для загрузки “заплатку” (patch), посредством которой STATSPACK можно использовать с любой базой данных Oracle8. После того, как этот пакет установлен на Oracle8, АБД готов начать сбор и анализ полезных данных о производительности.

Как работает STATSPACK

Утилита STATSPACK является набором скриптов, с помощью которых выполняется специальная версия утилит Oracle Begin-Statistics (BSTAT) и End-Statistics (ESTAT). Эти утилиты собирают статистику использованного времени по более чем 100 показателям производительности. Однако, в отличие от утилит< BSTAT/ESTAT, STATSPACK собирает данные о производительности и хранит их в специальных таблицах Oracle. Все таблицы STATSPACK принадлежат пользователю< PERFSTAT, а все имена таблиц начинаются с STATS$. В листинге 1 приводится полный список названий таблиц STATSPACK.

STATS$BG_EVENT_SUMMARY 

STATS$BUFFER_POOL

STATS$BUFFER_POOL_STATISTICS 

STATS$DATABASE_INSTANCE

STATS$ENQUEUESTAT

STATS$FILESTATXS 

STATS$IDLE_EVENT

STATS$LATCH 

STATS$LATCH_CHILDREN

STATS$LATCH_MISSES_SUMMARY 

STATS$LEVEL_DESCRIPTION 

STATS$LIBRARYCACHE 

STATS$PARAMETER 

STATS$ROLLSTAT 

STATS$ROWCACHE_SUMMARY 

STATS$SESSION_EVENT 

STATS$SESSTAT 

STATS$SGASTAT_SUMMARY 

STATS$SGAXS 

STATS$SNAPSHOT 

STATS$SQL_SUMMARY 

STATS$STATSPACK_PARAMETER 

STATS$SYSSTAT 

STATS$SYSTEM_EVENT 

STATS$WAITSTAT

Чтобы узнать названия столбцов этих таблиц, мы можем просто выбрать имя таблицы и использовать команду SQL*Plus 'DESCRIBE', как показано в листинге 2.

Листннг 2 SQL> desc STATS$BUFFER_POOL_STATISTICS; Name Null? Type ------------------------------- -------- ---- SNAP_ID NOT NULL NUMBER(6) DBID NOT NULL NUMBER INSTANCE_NUMBER NOT NULL NUMBER ID NOT NULL NUMBER NAME VARCHAR2(20) SET_MSIZE NUMBER CNUM_REPL NUMBER CNUM_WRITE NUMBER CNUM_SET NUMBER BUF_GOT NUMBER SUM_WRITE NUMBER SUM_SCAN NUMBER FREE_BUFFER_WAIT NUMBER WRITE_COMPLETE_WAIT NUMBER BUFFER_BUSY_WAIT NUMBER FREE_BUFFER_INSPECTED NUMBER DIRTY_BUFFERS_INSPECTED NUMBER DB_BLOCK_CHANGE NUMBER DB_BLOCK_GETS NUMBER CONSISTENT_GETS NUMBER PHYSICAL_READS NUMBER PHYSICAL_WRITES NUMBER

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

Анализ тенденций с использованием STATSPACK

Поскольку утилита STATSPACK появилась совсем недавно, Oracle поддерживает для нее только один отчет, который называется 'STATSREP80.SQL'. Хотя он и является замечательным отчетом для просмотра изменений, произошедших между двумя определенными моментами времени, он, тем не менее, не обеспечивает данных, необходимых для создания отчета о тенденциях. Опытный АБД знает, что измерение данных о производительности в зависимости от времени может обеспечить глубокое понимание почасовых, ежедневных и еженедельных "сигнатур". Точно так же, как каждый субъект имеет уникальную сигнатуру, каждая база данных имеет уникальную сигнатуру для ввода/вывода, сортировки, коэффициента удач для буфера данных и так далее. Эти сигнатуры, в свою очередь, сообщают АБД, когда определенные задачи “напрягают” базу данных.

Цель составления графиков временного ряда состоит в том, чтобы разработать сигнатуры для различных показателей базы данных. Например, мы могли бы заметить, что объем ввода/вывода всегда велик во вторник около девяти часов утра, или что коэффициент удач для буфера данных всегда понижается около полудня в среду. Имея в своем распоряжении эту информацию о сигнатуре, АБД может должным образом планировать точки пиков и минимумов в производительности базыданных. Давайте исследуем эту концепцию, рассмотрев некоторые фактические диаграммы STATSPACK.

Итоговые отчеты о временных рядах

Эти отчеты показывают полный тренд базы данных за определенный интервал времени. В примере на рисунке 1 мы видим отчет о полном объеме базы данных в байтах, который построен в MS-Excel с добавленной строкой линейной регрессии, показывающей темп роста базы данных.

Рисунок 1 – Отчет об изменении во времени (временном ряде) размера базы данных

Ежедневные отчеты

Эти отчеты демонстрируют почасовые тенденции, сгруппированные по времени суток. В примере на рисунке 2 мы видим сигнатуру для среднего числа операций чтения и записи (ввода/вывода), усредненного по часам за сутки. Мы можем ясно видеть, что у этой базы данных имеется сигнатура чтения (ввод/вывод) с пиками в 7:00 утра и в 8:00 пополудни. Мы также видим, что эта база данных имеет относительно постоянную сигнатуру записи.

Рисунок 2 - График почасового среднего объема ввода/вывода в течение суток

Еженедельные отчеты

На рисунке 3 мы видим объемы дисковых сортировок, усредненных по дням недели. Здесь мы видим четкую сигнатуру, в которой объем дисковых сортировок велик по понедельникам, имеет пик по вторникам, снова велик по средам и уменьшается по четвергам и пятницам. Это может служить предостережением для АБД относительно потенциально высокого влияния на табличное пространство TEMP.

Рисунок 3 – Отчет о ежедневных тенденциях

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

Так как же мы проводим усреднение по часам или суткам? На самом деле, это для нас весьма просто. Рассмотрим листинг 3, который печатает средний объем дискового ввода/вывода по дням недели

Листинг 3 - STATSPACK Query to Average by 'day of the week'* set pages 9999; column reads format 999,999,999 column writes format 999,999,999 select to_char(snap_time,'day'), avg(physical_reads) reads, avg(physical_writes) writes from perfstat.stats$buffer_pool_statistics fs, perfstat.stats$snapshot sn where fs.snap_id = sn.snap_id group by to_char(snap_time,'day') ; *Чтобы заменить этот сценарий на 'average by hour of the day' (среднее по часам суток), просто замените 'day' на 'HH24'.

Какие из данных STATSPACK являются лучшими для анализа тенденций?

Если учесть, что посредством STATSPACK доступны сотни статистик, возникает вопрос: какие метрики являются наиболее полезными? Следуя приведенному в моей книге "High Performance Oracle8 Tuning" ("Высокопроизводительная настройка Oracle8") совету, были выбраны следующие показатели:

Коэффициент удач для буфера данных

Этот коэффициент измеряет эффективность буфера данных Oracle, определенного параметром db_block_buffers в файле init.ora

Сортировки

Они важны для мониторинга количества сортировок в базе данных, особенно количества сортировок, которые выполняются в табличном пространстве TEMP (sorts (disk)).

Физическое чтение диска

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

Физическая запись на диск

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

Ожидание ввода/вывода

Это – очень важный показатель, который может использоваться для идентификации и исправления конфликтных ситуаций ввода/вывода. Значительное ожидание ввода/вывода происходит, когда происходит одновременное обращение к двум или более файлам данных, и перемещения головок чтения/записи запоминающего устройства прямого доступа заставляют диск ждать завершения ввода/вывода. Детали можно найти в статьях Turning the Tables on Disk I/O (Oracle Magazine, January, 2000) и Tuning Disk I/O in Oracle8 (Oracle Magazine, November, 1999).

Ожидания “Буфер Занят”

Ошибка ожидания буфера происходит, когда блок данных Oracle постоянно хранится в буфере, но запрашивающая его программа должна ожидать доступа к блоку данных. Такие ожидания могут обычно происходить, когда для таблицы разрешены одновременно выполняющиеся (параллельные) операции 'UPDATE' или 'INSERT DML', но для нее определен только один список свободной памяти.

Запросы пространства для регистрации изменений

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

Процент удачи “защелок”

Процент удач экземпляра базы данных дают хорошее представление относительно установки размеров параметра shared_pool_size в файле< init.ora.

Строка Table Fetch Continued

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

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

Построение диаграмм тенденций Oracle с использованием электронных таблиц

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

В качестве простого примера, давайте сделаем простую выборку и составим для нее диаграмму, используя для этого мастер диаграмм MS-Excel. Для этого пройдем следующие этапы:

1. Запрос SQL*PLUS для получения данных STATSPACK.
2. "Вырезать и вставить" результат в электронную таблицу
3. В MS-EXCEL для подсвеченных данных, которые вы только что вставили, выберите из раскрывающегося меню "DATA", а затем 'Text to Columns’. В результате столбцы будут помещены в отдельные (видимые) ячейки.
4. Нажать кнопку мастера диаграмм и создать линейную диаграмму, выполнив следующие шаги:

Первый шаг. Сначала мы копируем данные, используя клавиши .

Второй шаг. Затем мы открываем MS-EXCEL и вставляем данные, используя клавиши .

Третий шаг. Для нашего подсвеченного столбца данных, выберите из раскрывающегося меню "DATA", а затем ' Text to Columns'. Выполните мастер текстов для раздела с фиксированной шириной.

Четвертый шаг. Затем нажмите кнопку мастера диаграмм и выберите линейную диаграмму.

Пятый шаг. Теперь мы имеем готовую к завершению диаграмму.

Теперь, когда мы ознакомились с применением графических инструментальных средств, давайте исследуем универсальный скрипт предупреждения об опасности (alert), который использует данные STATSPACK для автоматического предупреждения АБД о надвигающихся проблемах.

Настраиваемые отчеты о предупреждении исключительных ситуаций для АБД

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

Ниже приведены пороги, измеряемые в 'ALERT.SQL':

* Buffer hit ratio < 90%

* Redo log space requests > 20/hr

* Disk sorts > 20/hr

* Buffer bus waits > 10/hr

* Table fetch continued rows > 10,000/hr

* High I/O waits > 5,000/hr

В некоторых вычислительных центрах расширяют STATSPACK и сохраняют в его таблицах показатели производительности UNIX для предупреждающих отчетов. Подробности этой методики изложены в статье "Автоматизация настройки Oracle" (Automating Oracle Tuning, Oracle Magazine - July, 1996). Конечно, АБД может легко настроить скрипт 'ALERT.SQL' в соответствии со своими конкретными потребностями. На некоторых ВЦ этот скрипт устанавливается и используется администрацией для контроля эффективности АБД при поддержании надлежащего состояния базы данных.

На ВЦ, где сервер UNIX подключен к электронной почте, ежедневные предупреждающие отчеты посылаются АБД. Это освобождает их от утомительной рутинной работы по постоянному мониторингу, и позволяет АБДсконцентрироваться на более сложных задачах.

Приложение

Сценарий ff">'STATSPACK_ALERT.SQL' script

Дональд Бурлесон (Donald Burleson) является главным редактором Oracle Internals – популярного журнала для АБД Oracle, а также автором многочисленных книг по тематике баз данных, в том числе, High-Performance Oracle8 Tuning, High-Performance Oracle Data Warehousing (Coriolis Publishing, 1997) и Oracle SAP Administration (O'Reilly & Associates, 1999). Адрес Дона в Интернет – http://www.dba-oracle.com/