Statspack

Позже с помощью отчета  можно проанализировать что происходило между  запусками statspack. 

Почему это важно ? Потому что вся системная статистика накапливается Oracle с момента старта экземпляра БД. И вы просто можете не увидеть за этим фоном вашей истории  Ваших текущих проблем.

Важно. Statspack предназначен для анализа экземпляра в целом. Если Вам надо отладить один запрос, лучше воспользоваться tkprof. 

С каким версиями Oracle это работает ? Начиная с 8.0. Но для 8.0 нужно использовать этот архив, для 8.1.6 этот, если у Вас 8.1.7 или выше то Вы найдете его у себя в $ORACLE_HOME/rdbms/admin. В 8.1.7 имена файлов изменились и имеют префикс sp*, например spcreate.sql

Вновь создаваемому пользователю приедется указать табличные пространства. Очень рекомендую вновь создать  такое пространство размером между 300M и 1Gb в расчете на 1 сутки сбора статистики. Размер зависит от  того уровня сбора статистики, который вы закажете (см. ниже)

sh% cd $ORACLE_HOME/rdbms/admin

SQL> connect internal
SQL> define default_tablespace='tools'
SQL> define temporary_tablespace='temp'
SQL> @statscre   -- или spcreate.sql для 8.1.7 и выше

Ставить надо именно так как я написал. Т.е. с переходом в директорию. Это потому, что изнутри себя этот инсталлятор зовет установку двух  sys'овских пакетов  (dbms_job, и dbms_shared_pool). Очень обижается если не находит. 

• одна неприятность заключается в  том, что база может быть только локальной. Так как это явно предполагается в трех местах. 
• вторая, что пароль пользователя perfstat - по умолчанию такой же. Однажды мне сказали что политика безопасности не позволяет завести такой пароль и я ходил менял его по скриптам
• третья, что до версии 8.1.5 перед установкой требуется вручную создать view v$buffer_pool_statistics и дать гранты to public. Исходник view все в файле statscbps.sql

Приятная новость - можно запускать инсталлятор сколько хочешь. Он безобидный. 

Уф. Поставили ?. Давайте пользоваться. Для проверки того работает или нет запустим процедуру snap:

sh%sqlplus perfstat/perfstat

sql> exec statspack.snap

sql> exec statspack.snap

sql> @statsrep

Запускать надо snap именно 2 раза. В отчете (statsrep) вы увидите id 1 и 2. Значит все работает. Да. Если версия меньше 8i то отчет называется statsrep80.sql

Для того чтобы использовать все возможности statspack следует на время его работы включить два параметра timed_statistics = true и timed_os_statistics = <число секунд>. Можно прочитать об этих параметрах больше.

Теперь поставим сбор статистики с помощью job. Для этого конечно в Вашем init.ora должен быть установлен параметр job_queue_processes в число > 0.

sh% sqlplus perfstat/perfstat

variable jobno number;
begin
dbms_job.submit(:jobno, 'statspack.snap;', sysdate, 'SYSDATE+1/96', TRUE);
commit;
end;
/

print jobno

Не забудьте снять сбор статистики:

sqlplus perfstat/perfstat

sql> exec dbms_job.remove (<jobno>)

где <jobno> номер job распечатанный при установки job

Если Вы забыли jobno посмотрите его в user_jobs:

select job, what from user_jobs where what like '%statspack%';

Теперь получим отчет с помощью statsrep(80). Отчет по умолчанию имеет вид типа st_<id_from>_<id_to>.lst.

Для отчета имеет смысл выбрать те id, между которыми в БД происходило что-то интересное. Ориентироваться можно по времени. Рядом с каждым id пишется время, когда он был сделан.

Отчет присылайте мне. Я пришлю ответ что делать дальше :))

Можно также сделать export данных пользователя perfstat и пересдать его мне. Это чуть менее удобно чем, отчет (хотя бы по объему данных) хотя и содержит более полную информацию. Проблема здесь в том что стандартные отчет statspack не покажет данных, если имя текущего инстанса не совпадает с именем инстанса в отчете. Но все можно исправить..

Подробность отчета зависит о уровня детализации. Изменить уровень детализации можно с помощью 

SQL> execute statspack.snap(i_snap_level=>10, i_modify_parameter=>'true'); 

Для 8.1.7 существуют следующие уровни:

Levels >= 0 General performance statistics
 such as: wait statistics, system events, system statistics, rollback segment data, row cache, SGA, 
background events, session events, lock statistics, buffer pool statistics, parent latch statistics.

Levels >= 5 Additional data: SQL Statements
This level includes all statistics gathered in the lower level(s), and additionally gathers the performance data on high resource  usage SQL statements.

Levels >= 10 Additional statistics: Parent and Child latches This level includes all statistics gathered in the lower levels, and additionally gathers Parent and Child Latch information. 

Существует также совершенно потрясающий сервис по автоматической обработке отчетов statspack по адресу http://www.oraperf.com/. Вы имеете возможность загрузить к ним свой отчет и немедленно получить его анализ. Я не все понял из их рекомендаций, но считаю этот сервис крайне полезным для первичного анализа. 

Ссылки:

1. Наиболее адекватное и короткое описание: части 1 и 2, Oracle corp.
2. Statspack.doc из поставки 8.1.7
3. Общий обзор от интерфейс.ру на русском, но с ошибками :))
4. Что нас ждет в версии 9.2 
5. Advanced Tuning with statspack (part 1, part 2)