Perfstat oracle что это

Обновлено: 06.07.2024

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

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

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

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

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

Утилита STATSPACK впервые появилась в Oracle8 i (версия 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.

Листинг 1 - Таблицы пакета STATSPACK

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

Мы видим, что в 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'*

*Чтобы заменить этот сценарий на '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. Для этого пройдем следующие этапы:

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

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

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

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

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

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

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

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

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

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

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

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

Авторизуясь в LiveJournal с помощью стороннего сервиса вы принимаете условия Пользовательского соглашения LiveJournal

Сайзинг под базы Oracle есть критически важный параметр для создания системы хранения достаточной производительности. Сбор данных perfstat* и Oracle STATSPACK позволит выбрать правильно сконфигурированную систему хранения NetApp, как в плане размера, так и производительности.

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

Собирайте данные, когда система под большой нагрузкой
Собирайте данные со всех хостов инфраструктуры
Собирайте данные в perfstat и STATSPACK за один период времени

Установка Oracle STATSPACK.

STATSPACK это утилита диагностики произвдительности СУБД, которая доступна начиная с Oracle8i. STATSPACK это дальнейшее развитие утилит BSTAT и ESTAT. Рекомендуется установить timed_statistics в true. Для установки STATSPACK выполните следующие шаги:

1. Создайте PERFSTAT Tablespace:

2. Запустите catdbsyn.sql и dbmspool.sql как SYS из SQLPLUS

$ sqlplus "/ as sysdba"

3. Запустите скрипт

$ sqlplus "/ as sysdba"

Можете начинать пользоваться Oracle STATSPACK.

Сбор Oracle Statspack с помощью Perfstat с хоста Unix.

Как я могу собирать данные Oracle Statspack и Perfstat одновременно?

Для Perfstat версии 6.28 или позднее мы можем собирать данные Oracle Statspack из Perfstat используя единую команду Perfstat. Это работает для версии perfstat под unix, и не работает для версии perfstat под Windows.

Небольшая выжимка их манов к Perfstat:

1. Perfstat может собирать данные Oracle STATSPACK только с одного хоста.
2. Инстанс Oracle должен быть запущен до того, как будет вызван Perfstat.
3. Используйте флаг -o чтобы передать специфические опции statspack в perfstat.

Опции и значения по умолчанию:

-oracle_host - умолчание для localhost, может быть одним из хостов, определенных с помощью -h из команд perfstat

-runas - может использоваться для запуска sqlplus от имени другого пользователя (то есть пользователь Unix, где установлен Oracle, обычно не root.)

-sysdba - может использоваться для подключения к oracle как sysdba, игнорируя параметр oracle_login. Ключ sysdba вводит следующие команды в логин к sqlplus:

a. sqlplus /nolog
b. connect / as sysdba.

perfstat.sh -a oracle
perfstat.sh -h host1 -a oracle -o oracle_host=host1
perfstat.sh -h host1 -a oracle -o oracle_host=host1,sqlplus=/oracle/bin/sq lplus/
perfstat.sh -a oracle -o oracle_login=user/pass
perfstat.sh -a oracle -o runas=oracle,sysdba=true

Более сложные примеры:

perfstat.sh -a oracle -t 5 -i 5 -f filer -o runas=oracle, sysdba=true >/opt/perfstat.out

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

Пример приведенный выше запускает perfstat и statspack 5 раз по 5 минут. Он переключает пользователя в oracle (с помощью команды su - oracle), и входит в sqlplus с помощью:

Лишенные радости использования AWR (Automatic Workload Repository) в Enterprise Edition администраторы SE могут использовать набор утилит мониторинга производительности Statspack.

Установить Statspack
@%ORACLE_HOME%\rdbms\admin\spcreate

Хорошо, установили, но статистика от этого собирается просто так не станет.
Что бы автоматом собиралась статистика:
@?/rdbms/admin/spauto.sql

Он покажет номер задания:
The next scheduled run for this job is:
SQL> select job, next_date, next_sec
2 from user_jobs
3 where job = :jobno;

JOB NEXT_DATE NEXT_SEC
---------- --------- --------
1 07-APR-17 11:15:00

Получить отчёт:
@?/rdbms/admin/spreport.sql
Всё собранная статистика будет бережно храниться с табличном пространстве, которое мы назначили при установке. Что бы старые данные можно не копились, их можно (а чаще всего нужно) удалить. Облегчить жизнь администратору и автоматически удалять статистику каждые 15 дней, нам поможет:
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'perfstat.statspack_every60',
repeat_interval => 'FREQ=HOURLY; INTERVAL=1');
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'perfstat.sp_snapshot',
job_type => 'STORED_PROCEDURE',
job_action => 'perfstat.statspack.snap',
schedule_name => 'perfstat.statspack_every60',
comments => 'Statspack collection');
DBMS_SCHEDULER.ENABLE('perfstat.sp_snapshot');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"PERFSTAT"."SP_PURGE_15"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
statspack.purge( i_purge_before_date => (sysdate-15));
end; ',
number_of_arguments => 0,
start_date => NULL,
repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Purge SP older then 15 days');
DBMS_SCHEDULER.enable(name => '"PERFSTAT"."SP_PURGE_15"');
END;
/

Сделать снепшот вручную:
execute statspack.snap

Удаление Statspack

Подняли тест с продакшена? Изменился DBID?
Statspack не работает, а в алертлоге множество:
"ORA-12012: error on auto execute of job "PERFSTAT"."SP_SNAPSHOT"
ORA-04063: package body "PERFSTAT.STATSPACK" has errors"

Манипуляции с sppurge.sql по статье от Pythian не помогли и закончились тем что:
ERROR at line 1:
ORA-04063: package body "PERFSTAT.STATSPACK" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"PERFSTAT.STATSPACK"
ORA-06512: at line 4
т.е. безрезультатно.

Простым решением может быть переустановка StatsPack.

Для удаления этого диагностического пакета, подключитель пользователем с привелегиями SYSDBA и запустите SPDROP сценарий из SQLPlus. Например:
SQL> CONNECT / AS SYSDBA
SQL> @?/rdbms/admin/spdrop

Пакет Statspack должен устанавливаться при подключении к базе данных от имени пользователя SYS (CONNECT 1 AS SYSDBA) или пользователя, которому выданы привилегии SYSDBA. Во многих средах установка Statspack будет задачей, требующей участия администратора базы данных или системного администратора.

Установка Statspack не представляет сложности. Вы просто запускаете сценарий @spcreate . sql. Он находится в каталоге $0RACLE_HOME/rdbms/admin и должен выполняться после подключения от имени SYS через SQL*Pius.

Перед запуском сценария spcreate . sql необходимо знать следующие три единицы информации:

пароль, который вы хотите применять ля создаваемой схемы PERFSTAT;
стандартное табличное пространство ля схемы PERFSTAT;
временное табличное пространство, которое вы хотите использовать для схемы PERFSTAT.

Выполнение этого сценария дает примерно такой вывод:

$ sqlplus / as sysdba

Oracle Database 12с Enterprise Edition Release 12 . 1 . 0 . 1 . 0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@ORA12CR1> @spcreate

Choose the PERFSTAT user's password

Not specifying а password will result in the installation FAILING Enter value for perfstat_password:

Выберите пароль для пользователя PERFSTAT

Не указание пароля приведет к неудачному завершению установки Введите значение для perfstat_password:

. . . <для краткости остальной вывод не показан> . . .

Во время выполнения сценарий запросит необходимую информацию. В случае опечатки или случайного прекращения установки перед попыткой установить Statspack повторно вы должны с помощью сценария spdrop.sql, находящегося в $0RACLE_НОМЕ/rdbms_admin, удалить пользователя и установленные представления. Процесс установки Statspack создаст файл spcpkg.lis. Его необходимо просмотреть на предмет выяснения любых ошибок, которые могли произойти. Однако пользователь, представления и код PL/SQL должны установиться без каких-либо проблем при условии, что вы указали допустимые имена табличных пространств (и пользователь PERFSTAT пока еще не существует).

На заметку! Документация по Statspack находится в текстовом файле $0RACLE_НОМЕ/rdbms_admin/spdoc.txt.

Замечу, для будущих программистов, - если хотите стать профи в разработке приложений под Oracle, то нужно знать возможности и использовать этот пакет на "Ять".

Читайте также: