Настройка производительности. Использование STATSPACK

STATSPACK – набор скриптов, позволяющих собирать, сохранять и отображать данные о производительности системы. По сравнению с использованием скриптов UTLBSTAT/UTLESTAT STATSPACK обеспечивает больший объем обрабатываемых данных, расчитывает на основе собранных данных часто использующиеся коэффициенты (что приходилось делать вручную), хранит собранную статистику в таблицах, откуда информация может быть извлечена, используя все возможности SQL, включая данные, собиравшиеся ранее.

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

С помощью специального скрипта создается схема пользователя PERFSTAT, где и будут сохраняться все необходимые объекты для работы STATSPACK. Этому пользователю даются права на чтение необходимых V$-представлений словаря.

При использовании STATSPACK используется понятие снимка (snapshot), в данном контексте означающего отдельный набор данных производительности, собранный с помощью STATSPACK (не путать со снимками - объектами БД). Каждый снимок получает уникальный идентификатор (SNAP_ID). STATSPACK может также использоваться в среде Real Application Clusters, где SNAP_ID вместе с идентификатором (DBID) и номером экземпляра (INSTANCE_NUMBER) уникально определяют снимок, позволяя хранить данные STATSPACK в одной схеме.

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

Установка и настройка STATSPACK

Для объектов STATSPACK требуется примерно 64Мб места на диске.

Для установки STATSPACK можно использовать один из способов:

- Интерактивная установка;

- Пакетная установка.

Интерактивная установка

Первый шаг – создание пользователя PERFSTAT. Необходимо указать основное и временное табличные пространства для создаваемого пользователя. Нельзя использовать для STATSPACK ТП SYSTEM. Во время установки STATSPACK создаются пакеты DBMS_SHARED_POOL и DBMS_JOB (если они еще не были созданы в системе).

Чтобы установить объекты STATSPACK, необходимо обратиться к хранилищу скриптов СУБД Oracle 10g - ORACLE_HOME/rdbms/admin и найти скрипт SPCREATE.SQL. Далее запустить SQL*Plus, присоединиться как SYSDBA, и запустить этот скрипт на выполнение.

В UNIX:

SQL> CONNECT / AS SYSDBA

SQL> @? /rdbms/admin/spcreate

В NT(2000):

SQL> CONNECT / AS SYSDBA

SQL> @%ORACLE_HOME%\rdbms\admin\spcreate

Этот скрипт запускает дополнительные:

SPCUSR.SQL: Создает пользователя и выдает привилегии;

SPCTAB.SQL: Создает таблицы;

SPCPKG.SQL: Создает пакет.

Для того, чтобы убедиться в корректной отработке скриптов, пронализируйте выходные файлы (SPCUSR.LIS, SPCTAB.LIS, SPCPKG.LIS) на предмет наличия ошибок.

Пакетная установка

Чтобы провести установку в пакетном режиме, необходимо в среде SQL*Plus описать переменные для основного и временного ТП для пользователя REPSTAT.

Переменную DEFAULT_TABLESPACE: для основного ТП;

Переменную TEMPORARY_TABLESPACE: для временного ТП.

Например,

В UNIX:

SQL> CONNECT / AS SYSDBA

SQL> define default_tablespace='tools'

SQL> define temporary_tablespace='temp'

SQL> @? /rdbms/admin/spcreate

В случае неудачной установки, либо при желании удалить объекты STATSPACK, воспользуйтесь скриптом rdbms/admin/spdrop.sql

Использование STATSPACK

Снятие снимков

Самый простой способ получить снимок данных производительности – в среде SQL*Plus, присоединившись как PERFSTAT, запустить процедуру STATSPACK.SNAP. Например,

SQL> CONNECT perfstat/perfstat

SQL> EXECUTE statspack.snap;

Это приведет к тому, что в стат-таблицах схемы PERFSTAT появятся данные первого снимка. Это – основа для последующих сравнений. Рекомендуется при этом установить параметр TIMED_STATISTICS в TRUE, это включит подробные временные отметки в собираемую информацию.

Чтобы получить впоследствии отчет, необходимо указывать идентификаторы снимков (SNAP_ID), которые определяют временной интервал, в рамках которого нужно получить информацию о производительности системы. Информацию о вывполенных снимках можно получить из таблицы схемы PERFSTAT - stats$snapshot.

SQL> select snap_id, snap_time from stats$snapshot

2 /

SNAP_ID SNAP_TIME

---------- ---------

1 08-JUN-03

2 08-JUN-03

Это можно также и сделать с помощью функции

STATSPACK.SNAP и связанной переменной. Следует учесть, что при этом осуществляется снятие снимка.

SQL> variable snap number;

SQL> begin: snap: = statspack.snap; end;

2 /

PL/SQL procedure successfully completed.

SQL> print snap

SNAP

----------

Автоматический сбор статистики при помощи STATSPACK

Для систематического сравнения производительности снятие снимков следует проводить регулярно: раз в день, раз в неделю и т.д. Лучше всего это реализуется через автоматизацию процесса снятия снимков через регулярные интервалы. Для этого можно воспользоваться либо пакетом СУБД Oracle 10gDBMS_JOB, либо средствами ОС, например, утилитами 'cron' в UNIX или 'at' в NT(2000).

Пример использования пакета DBMS_JOB для автоматизации сбора статистики БД представлен в скрипте rdbms/admin/spauto.sql. В нем настроено почасовое снятие снимков. Этим скриптом можно воспользоваться, чтобы настроить собственные процедуры сбора статистики. Использование пакета DBMS_JOB требует, чтобы значение параметра инициализации JOB_QUEUE_PROCESSES было равным числу одновременно работающих в системе заданий (jobs).

Создание отчета о производительности

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

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

Столбец STARTUP_TIME таблицы STATS$SNAPSHOT содержит время запуска сеанса экземпляра, в течение работы которого производился снимок. Например,

SQL> select snap_id,

2 snap_time,

3 to_char(startup_time, 'DD.MON.YYYY HH: MI: SS') from stats$snapshot

4 /

SNAP_ID SNAP_TIME TO_CHAR(STARTUP_TIME

---------- --------- --------------------

1 08-JUN-03 08.JUN.2003 10: 10: 59

2 08-JUN-03 08.JUN.2003 10: 10: 59

3 08-JUN-03 08.JUN.2003 10: 35: 24

В этом примере снимки 1 и 2 сделаны в рамках одного сеанса, снимок 3 – уже в другом.

Формирование отчета осуществляется с помощью скрипта SPREPORT.SQL. Он должен выполняться под учетной записью пользователя PERFSTAT. Во время формирования отчета будут запрошены начальный и конечный SNAP_ID, также имя файла отчета.

SQL> connect perfstat/oracle

Connected.

@? /rdbms/admin/spreport

Запрос номера начального снимка:

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1

… конечного снимка:

Enter value for end_snap: 2

Запрос имени файла отчета (по умолчанию для отчетов используется каталог /bin и имя sp_< begin_snap > _< end_snap >.lst):

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_1_2. To use this name,

press < return> to continue, otherwise enter an alternative.

Enter value for report_name:

Больше вопросов не задается и формируется файл отчета.

Чтобы получить файл отчета вообще без вопросов, необходимо перед запуском прописать 3 переменные:

SQL> define begin_snap=1

SQL> define end_snap=2

SQL> define report_name=batch_run

И запустить SPREPORT.SQL.

В отчете, помимо общей информации о состоянии экземпляра, содержаться SQL-выражения, затраты ресурсов на выполнение которых может составлять значительную часть в общей картине производительности. Можно проанализировать подобные выражения более детально, используя их хэш адреса, которые содержаться в файле отчета, полученном с помощью SPREPORT. Для этого существует другой сценарий: SPREPSQL.SQL.

Для его работы, помимо тех же трех параметров, что указывались для сценария SPREPORT, необходимо указать значение хэш-адреса (hash value) SQL-выражения в разделяемом пуле.

Пример отчета для небольшого SQL-выражения:

SQL> @? /rdbms/admin/sprepsql.sql

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

2921971007 MTS 1 mts

Completed Snapshots

Snap Snap

Instance DB Name Id Snap Started Level Comment

------------ ------------ ----- ----------------- ----- ----------------------

mts MTS 1 08 Jun 2004 10: 17 5

2 08 Jun 2004 10: 17 5

3 08 Jun 2004 10: 37 5

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1

Enter value for end_snap: 2

End Snapshot Id specified: 2

Specify the Hash Value

~~~~~~~~~~~~~~~~~~~~~~

Enter value for hash_value: 2065408759

Hash Value specified is: 2065408759

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_1_2_2065408759. To use this name,

press < return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name sp_1_2_2065408759

STATSPACK SQL report for Hash Value: 2065408759

DB Name DB Id Instance Inst Num Release Cluster Host

------------ ----------- ------------ -------- ----------- ------- ------------

MTS 2921971007 mts 1 9.2.0.4.0 NO ASAEV

Start Id Start Time End Id End Time Duration(mins)

--------- ------------------- --------- ------------------- --------------

1 08-Jun-04 10: 17: 16 2 08-Jun-04 10: 17: 39 0.38

SQL Statistics

~~~~~~~~~~~~~~

-> CPU and Elapsed Time are in seconds (s) for Statement Total and in

milliseconds (ms) for Per Execute

% Snap

Statement Total Per Execute Total

--------------- --------------- ------

Buffer Gets: 1 1.0.11

Disk Reads: 0 0.0

Rows processed: 0 0.0

CPU Time(s/ms): 0.0

Elapsed Time(s/ms): 0.1

Sorts: 0.0

Parse Calls: 1 1.0

Invalidations: 0

Version count: 2

Sharable Mem(K): 19

Executions: 1

SQL Text

~~~~~~~~