Профилируем экземпляр

Написано это было под сильным влиянием Oracle Advanced Performance Tuning Scripts

Вместо введения.

Oracle  большая и сложная C программа. Похожая на многие другие программы, только сложнее многих из них. Но все-таки, насколько сложной не была бы программа, общие принципы применимы и к ней. Администраторы Unix разбираются с Oracle за несколько минут. Смотрят в системную статистику, печально вздыхают -"опять этот Ваш Oracle что то читает, вот диски и не справляются", и дальше занимаются настоящим мужским делом - sendmail  конфигурят или man'ы читают. 

Что есть правда в их словах ? А то, что сколько не смотри за buffer cache ration, library cache pin - нужно понимать, что происходит со стороны ОС и аппаратуры. Основные аппаратные компоненты: процессоры, память, дисковая подсистема, сеть ( всего 4 компоненты)

Что прежде чем что-то изменить нужно осознать что измениться для внешнего по отношению к БД мира. Исключение составляет случай, когда в ОС все отлично, а пользователи жалуются. Тогда да, это скорее всего, внутренние блокировки в БД (еще одна  компонента). 

Я насчитал всего 5 компонент которые нам надо рассмотреть.  Попробуем ранжировать их в порядке "проблемности" для нас.

Итак предлагаемый мной путь прост и сводится к следующим шагам:

1. Определяем распределение времени ответа   конечного пользователя в целом для экземпляра. Отсюда уже понятно, ожидания какой из компонент нас беспокоит. 
2. Но Oracle все же сложная С программа, и процессы в ней разделяются на фоновые и пользовательские. Рассмотрим тоже самое для фоновых  процессов. Так как они выполняют большую работу по запросу пользовательских процессов эта информация нам пригодится.
3. Хорошо, все же нас беспокоят пользователи, они нам звонят, рассматриваем их ожидания для текущих сессий. Надо отметить что это ожидания которые уже завершились.
4. Итак, мы определили компоненту, которая является наиболее проблемной, можем отсортировать сессии в порядке ожидания  ресурсов той или иной компоненты. Включаем трассировку.
5. И наконец, нам необходимо знать что ожидают наши пользователи прямо сейчас.  Не забываем смотреть что происходит в ОС.

Что нужно знать

Перед дальнейшим прочтением Вам обязательно нужно познакомиться с архитектурой БД. Очень грубо важнейшие фоновые процессы Oracle описаны вот в этой  моей статье. Обратите внимание на главу Process Architecture из Concept Guide. 

Сам излагаемый метод основывается на классической статье Yet Another Performance Profiling Method (Or YAPP-Method). Следуя данной статье, основным метод поиска проблемы является изучение составляющих времени ответа: 

Response Time = Service Time + Wait Time

Для выполнения приводимых ниже шагов вам понадобится файл create_apt_tables.sql. Он создает необходимые вспомогательные таблицы и объекты для прочих скриптов.

Термин Major - принимает значение CPU |  Wait   и показывает  ожидание это  или  потребление CPU.

Термин Minor - показывает класс события. Они близки к тем самым "5 компонентам". Как я привязал классы событий к самим события можно найти в функции apt_event_class. 

Делаем все по шагам

Возьмем скрипт instance_responce_time.sql и посмотрим на что тратится время в нашем экземпляре:

(таблица обреза в целях удобочитаемости)

 В целом все вроде бы понятно: 25% - CPU, ~70% ожидания ввода-вывода. Нужно сосредоточиться на уменьшении ожидания ввода - вывода ? И да и нет. Прежде всего, помните что это статистика накапливается с момента старта экземпляра. В данный момент, у Вас могут быть другие проблемы. Вторая  проблема, которая нас ждет при анализе событий ожидания на уровне экземпляра - то, что некоторые ожидания  могут вызваться как в пользовательских (F) процессах, так и фоновых (B). Для иллюстрации предлагаю табличку из книги Oracle Wait Interfac

Т.е. если control file parallel write вызывается однозначно фоновым процессом, то db file sequential read - могут возникать как в пользовательских, так и в фоновых процессах. 

Итак, пока мы можем сделать, что 1/4 времени ответа для нашего конечного пользователя составляет CPU, 3/4 ввод-вывод. Что скорее всего нам придется заниматься вводом-выводом. Теперь, естественно нам надо рассмотреть отдельно пользовательские сессии и фоновые процессы.

Берем файл background_wait_time.sql 

(Таблица усечена для удобства чтения. 
Так она выглядит при запуске на платформе MS Windows)

Вспоминаем теорию, когда  процесс CKPT пишет в контрольные файлы.  Ищем, что же мы можем сделать, чтобы исправить эту ситуацию. В Note:223117.1 предлагаются следующие решения 

1. Reduce the number of controlfile copies to the minimum that ensures that not all copies can be lost at the same time.
2. Use Asynchronous I/O if available on your platform (только если у Вас raw device или Veritas FS !)
3. Move the controlfile copies to less saturated storage locations.

Надо ли нам следовать этим рекомендациям. Несомненно. Но внимание, вопрос (это первая критическая часть моего повествования !). Как это скажется на времени ответа конечного пользователя ? Для этого нужно узнать, есть ли у нас пользовательские сессии испытывающие защелку (enqueue) CF ('Control file schema global enqueue lock) ? Если да (только если да !) срочно боремся. 

Берем файл user_responce_time.sql. Он показывает нам распределение времени ответа текущих пользовательских сессий. Мы видим, что Minor = Locks у нас не встречается. Уф. Борьбу с control file parallel write пока можно отложить.

Ну что же, нас беспокоит Other CPU. Теперь нужно отсортировать сессии в порядке потребления Other CPU. Берем файл top_session.sql 

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

Но давайте узнаем, чего же ожидают наши пользователи прямо сейчас ! Берем файл session_waits.sql (он описан в этой  статье) 

1 138 SYS PLSQLDev.exe SELECT db file scattered read:SYS.SOURCE$ 554

Вот. В данный момент только пользователь sys читает таблицу sys.source и наверно делает это не эффективно (еще бы  select a.*,b.* from all_source a, all_source b) 

Таким образом, нам надо сосредоточиться на оптимизации данного запроса. Естественно, необходимо привлечь статистику ОС. Достаточно ли свободного CPU, успевают ли отрабатывать диски. (это вторая критическая часть моего повествования !). Определите сначала, какой из ресурсов ОС у вас наиболее загружен. Диски ? Хорошо, но не  надо бездумно бросаться на все запросы из "top sql by physical reads". А только на те из них, которые обращаются к объектам на наиболее загруженных дисках/томах. Все известные мне средства администрирования Oracle  готовы отсортировать сессии по кол-ву дисковых чтений, но никто их них не умеет (кроме Veritas I3) делать это с учетом дисковой структуры. А поэтому Вы можете провести не один день оптимизируя просто не тот sql (!)  

Найти все sql запросы связанные c тем, или иным диском можно так: вы знаете какие физические  файлы располагаются на данном диске, а значит вы можете составить список сегментов. Далее по v$sql_plan можно составить список запросов, обращающихся к данным сегментам. Именно эти запросы Вам и нужно оптимизировать.  

Я очень надеюсь что перечисленные шаги помогут Вам понять, как реально повлиять на время отклика, как успокоиться и начать оптимизировать только критические места вашей БД. 

Я понимаю, что предложенные подход выглядит уж больно просто - но поверьте, что 

1. Это так
2. После того как вы обнаружили проблему Вам предстоит ее решить. Для сравнения, обнаружив что проблема экземпляра в парсинге за 1 день, я искал подходящее решение около 4 дней ! 

ToDo:

Использовать для доступа на sys.v$ а perfstat.stat$

Ссылки:

Yet Another Performance Profiling Method (Or YAPP-Method)

Steve Adams http://ixora.com.au/, Oracle Advanced Performance Tuning Scripts

Oracle Wait Interface by Oracle Press