Оценка экземпляра Oracle: ожидания, события, представления, SQL и прочее

Одной из самых сложных задач в работе администратора баз данных (DBA) является точная оценка производительности экземпляра Oracle. Специалисты и руководства советуют проводить тщательную проактивную настройку, но в реальности в большинстве случаев удается осуществлять только реактивную настройку, которая обычно требует приложения массы усилий по исправлению существующих проблем, ощутимо замедляющих работу базы данных и увеличивающих количество жалоб со стороны пользователей.

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

В Oracle Database 11g и 12c для определения того, насколько хорошо работает экземпляр, применяется концепция времени БД. Выяснить, насколько эффективно функционирует база данных, можно путем просмотра некоторых статистических данных. Эти статистические данных делятся на две группы: статистические данные по коэффициентам попаданий и статистические данные по количеству событий ожидания в базе данных. Наличие составляющих свыше 90% показателей по различным коэффициентам попаданий обычно должно означать, что база данных работает нормально.

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

Статистические данные по коэффициентам попаданий в базе данных

Коэффициенты попаданий в базе данных являются самыми часто используемыми средствами измерения производительности. В их число входит коэффициент попаданий в кэш буферов, коэффициенты попаданий в библиотечный кэш и кэш словаря данных, коэффициент получения защелок и коэффициенты дисковых операций сортировки. Они не свидетельствуют о том, насколько хорошо работает система. Они представляют собой общие показатели надлежащей настройки SGA и могут быть высокими даже тогда, когда система в целом работает плохо. Важно запомнить то, что коэффициенты попаданий отражают только вещи вроде того, сколько операций physical reads выполняется по сравнению с операциями logical reads, и сколько времени проанализированная версия оператора находится в памяти. О том, являются ли сами операторы эффективными, они ничего сказать не могут. При замедлении работы системы из-за образования узких мест, от коэффициентов попаданий мало толку, и потому в таком случае вместо них следует тщательно изучать статистические данные по ожиданиям.

Внимание! Даже если коэффициент попаданий в кэш буферов составляет 99,9%, в приложении все равно могут происходить серьезные неэффективные вещи. Например, что если количество “ненужных” операций logical reads является чрезвычайно высоким? Коэффициент попаданий в кэш буферов в таком случае будет выглядеть прекрасно, поскольку он отражает число операций physical reads свыше суммы операций logical reads. И хотя может показаться, что приложение должно работать быстрее, поскольку большинство чтений выполняется из памяти, а не с диска, на самом деле этого может и не происходить. Объясняется это тем, что даже при выполнении операций physical reads единицы ЦП все равно тратятся на выполнение ненужных операций logical reads. По сути, рьяная забота о коэффициенте попаданий в кэш буферов при желании разгрузить подсистему ввода-вывода может выливаться в упущение проблемы с использованием ЦП. Более подробно о том, почему высокий уровень логических операций ввода-вывода (logical reads) может представлять серьезную проблему, можно прочитать в написанной Кэрри Милсэпом (Cary Millsap) интересной статье Why You Should Focus on LIOs Instead of PIOs (Почему следует обращать больше внимания на количество логических, а не физический операций ввода-вывода), которая доступна по адресу.

При столкновении с замедлением работы базы данных или потребностью в сокращении времени отклика, раньше администраторы баз данных Oracle старались увеличивать показатели по коэффициентам попаданий и настраивать базу данных за счет регулирования ряда параметров инициализации (вроде тех, что отвечают за выделение памяти различным компонентам SGA). В последнее время стало известно, что главное внимание нужно уделять устранению в базе данных узких мест, которые приводят к увеличению времени отклика.

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

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

Статистические данные по событиям ожидания

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

На заметку! О подходе к анализу событий ожидания Oracle (интерфейсе событий ожидания) очень интересно рассказано в одном из ранних посвященной этой теме документов под названием Yet Another Performance Profiling Method (or YAPP-Method) (Еше один интересный метод профилирования), написанном Анжо Колком (Anjo Kolk), Шари Ямагучи (Shari Yamaguchi) и Джимом Вискучи (Jim Viscusi) и доступном на веб-сайте OraPerf по адресу http://www.oraperf.com (для входа на сайт понадобится пройти бесплатную регистрацию).

После начала выполнения SQL-оператора процессу Oracle не всегда удается работать над его выполнением безо всяких прерываний. Зачастую ему приходится приостанавливаться или ожидать освобождения какого-нибудь ресурса, прежде чем продолжать выполнение. Следовательно, в любой заданный момент времени процесс Oracle может делать следующее:

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

Поэтому время отклика — общее время, которое уходит у Oracle на выполнение работы — правильно вычисляется так:

response time = service time + wait time
время отклика = время обслуживания + время ожидания

При выяснении, сколько всего времени ушло на осуществление транзакции, может оказаться, что только часть этого времени была потрачена сервером Oracle на фактическую работу. Остальную часть времени сервер мог просто ожидать освобождения какого-то ресурса или получения запроса на выполнение какого-нибудь действия. Таким занятым ресурсом может быть медленно работающий процесс записи в журналы или процесс записи в базу данных. Кроме того, причиной возникновения событий ожидания могут быть недоступные буферы или защелки. События ожидания, отображающиеся в представлении V$SYSTEM_EVENT (ожидания на уровне экземпляра) и представлении V$SESSION_EVENT (ожидания на уровне сеансов) показывают, из-за чего возникало время ожидания (полного сканирования таблиц, большого количества защелок в библиотечном кэше и т.д.). Помимо того, из-за чего возникало время ожидания в базе данных, они еще сообщают информацию об узких местах в сети и приложении.

На заметку! Важно понимать, что события ожидания являются лишь симптомами проблем, по большей части тех, которые находятся внутри кода приложения. События ожидания показывают, что тормозит производительность, но не то, почему события ожидания возникают в большом количестве. Администратор баз данных должен самостоятельно изучать SQL-код и выяснять настоящую причину проблем с производительностью.

Информация о событиях ожидания содержится в четырех следующих динамических представлениях производительности: V$SESSION, V$SYSTEM_EVENT, V$SESSION_EVENT и V$SESSION_WAIT. В этих четырех представлениях перечислены практически все события, которые экземпляру приходилось ожидать, и насколько долго. Понимание этих событий ожидания является существенно важным при устранении проблем с производительностью. Поэтому в последующих разделах наиболее типичные события ожидания рассматриваются более подробно. Следует иметь в виду, что в четырех указанных представлениях отображается похожая информация, но акцент делается на разных аспектах базы данных, как можно будет увидеть дальше. Больше всего пользы события ожидания приносят при включении функции сбора для них статистических данных по времени. В противном случае отображается только количество раз, которое они возникали, но не время, которое они занимали. Не видя, сколько времени занимали события, невозможно сказать, на самом деле ли они послужили причиной замедления работы системы.

Совет. Представления с событиями ожидания (интерфейс событий ожидания) удобно использовать для изучения текущих и недавних проблем с производительностью в экземпляре. Для проведения более глубокого анализа большинства связанных с производительностью проблем необходимо применять инструмент ADDM, который анализирует ежечасные снимки AWR.

Анализу интерфейса событий ожидания Oracle в последние годы было уделено довольно много внимания. По событиям Oracle написаны целые книги. Поэтому в этой статье будет рассказываться только о наиболее важной части процесса анализа ожиданий в Oracle, а именно — о той, что касается производительности (в разделе “Анализ производительности экземпляра”). В идеале все сеансы должны получать ресурсы ЦП и не тратить время на ожидание ресурсов наподобие ресурсов подсистемы ввода-вывода. Следует, однако, иметь в виду, что в каждом работающем экземпляре все равно будут происходить ожидания какого-нибудь рода. Нереально рассчитывать, что система будет работать с практически нулевым временем ожидания. Главным вопросом должно быть не то, возникают ли в Oracle события ожидания, а то, не возникает ли в Oracle чрезмерное количество таких событий.

События ожидания и классы событий ожидания

Всякий раз, когда процесс сервера ожидает завершения какого-нибудь события, это классифицируется как событие ожидания (wait event). В Oracle Database 11g существует свыше 950 событий ожидания. Наиболее распространенными из них являются те, которые возникают из-за состязания за ресурсы, например, состязания за защелки, состязания за буфер и состязания за ввод-вывод.

Группа взаимосвязанных событий ожидания называется классом событий ожидания (wait class), и каждое событие обязательно относится к какому-нибудь из таких классов. Наиболее важные классы событий ожидания — это Administrative, Application, Concurrency, Configuration, Idle, Network, System I/O и User I/O. Например, к классу Administrative относятся события ожидания, вызываемые блокировкой на уровне строк, а к классу User I/O — события ожидания, вызываемые чтением блоков с диска. Использование классов событий ожидания помогает быстро переходить к исходной причине проблемы в базе данных за счет ограничения фокуса дальнейшего анализа. Ниже приведено краткое описание основных классов ожидания в Oracle Database 11g.

Класс Administrative (Административные события ожидания) включает в себя события ожидания, вызываемые административными командами, подобными, например, перестройке индекса.
Класс Application (События ожидания приложения) включает в себя события ожидания, возникающие из-за кода приложения.
Класс Cluster (События ожидания кластера) включает в себя события ожидания, связанные с управлением кластером RAC (Real Cluster Application).
Класс Commit (События ожидания фиксации) включает в себя одно единственное событие ожидания log file sync (событие ожидания синхронизации файлов журналов), которое вызывают выполняемые в базе данных операции фиксации.
Класс Idle (События простоя) включает в себя события ожидания, которые происходят тогда, когда сеанс является не активным, как, например, событие SQL*Net message from client (событие ожидания сообщения SQL*Net от клиента).
Класс Network (Сетевые события ожидания) включает в себя события, возникающие во время обмена сообщениями по сети.
Класс Other (Другие события ожидания) включает в себя разнородные события ожидания.
Класс Scheduler (События ожидания планировщика) включает в себя события ожидания, связанные с работой приложения Resource Manager (Диспетчер ресурсов).
Класс System I/O (События ожидания системного ввода-вывода) включает в себя события ожидания, связанные с фоновыми процессами ввода-вывода, в том числе событие db file parallel write (событие ожидания выполнения параллельной записи в файлы базы данных), которого ожидает фоновый процесс записи в базу данных, и события, связанные с чтением и записью в архивные журналы и журналы повторного выполнения.
Класс User I/O (События ожидания пользовательского ввода-вывода) включает в себя события ожидания, связанные с пользовательскими процессами ввода-вывода, в том числе db file sequential read (событие ожидания выполнения последовательного чтения из файлов базы данных) и db file scattered read (событие ожидания выполнения чтения из файлов базы данных “вразброс”).

Анализ производительности экземпляра

Первое, что можно сделать для измерения эффективности работы экземпляра — определить, сколько всего времени база данных затрачивает на выполнение работы, а сколько просто на ожидание получения ресурсов. Представление V$SYSMETRIC отображает метрические показатели системы за самый текущий интервал времени. Ниже приведен пример запроса к этому представлению, который показывает, что у экземпляра базы данных больше времени уходит на ожидание ресурсов, чем на использование ЦП для выполнения работы:

SQL> SELECT METRIC_NAME, VALUE
FROM V$SYSMETRIC
WHERE METRIC_NAME IN ('Database CPU Time Ratio',
'Database Wait Time Ratio') AND
INTSIZE_CSEC =
(select max(INTSIZE_CSEC) from V$SYSMETRIC);
METRIC_NAME               VALUE
------------------------  ------
Database Wait Time Ratio  72
Database CPU Time Ratio   28
SQL>

Узнав, что общий показатель времени ожидания экземпляра значительно превышает показатель времени использования ЦП, можно приступать к более глубокому анализу. Классы событий ожидания представляют собой быстрый способ для выяснения того, почему экземпляр базы данных работает медленно. В примере, приведенном в листинге 20.14, можно легко заметить, что причиной длительного времени ожидания по большей части являются ожидания ресурсов ввода-вывода. Понять это можно, взглянув на столбец PCT_TIME, который показывает, сколько (в процентах) времени ассоциируется с каждым классом событий ожидания. Общие показатели по событиям ожидания обычно вводят в заблуждение, в чем можно убедиться, взглянув на класс событий ожидания NETWORK. В процентном отношении события ожидания сетевых ресурсов составляют только 1%, хотя общий показатель по этим события ожидания превышает 51% от общего количества ожиданий в данном экземпляре.

SQL> SELECT WAIT_CLASS,
2 TOTAL_WAITS,
3 round(100 * (TOT_WAITS / SUM_WAITS),2) PCT_TOTWAITS,
4 ROUND((TIME_WAITED / 100),2) TOT_TIME_WAITED,
5 round(100 * (TOT_TIME_WAITED / SUM_TIME),2) PCT_TIME
6 FROM
7 (select WAIT_CLASS,
8 TOT_WAITS,
9 TOT_TIME_WAITED
10 FROM V$SYSTEM_WAIT_CLASS
11 WHERE WAIT_CLASS != 'Idle'),
12 (select sum(TOT_WAITS) SUM_WAITS,
13 sum(TOT_TIME_WAITED) SUM_TIME
14 from V$SYSTEM_WAIT_CLASS
15 where WAIT_CLASS != 'Idle')
16* ORDER BY PCT_TIME DESC;
WAIT_CLASS      TOTAL_WAITS   PCT_TOT_WAITS   TOT_TIME_WAITED   PCT_TIME
-------------   -----------   -------------   ---------------   --------
User I/O         6649535191           45.07        46305770.5      84.42
Other             394490128            2.67        5375324.17        9.8
Concurrency        78768788             .53         1626254.9       2.96
Network          7546925506           51.15         547128.66          1
Application         2012092             .01          449945.5        .82
Commit             15526036             .11          351043.3        .64
Configuration      12898465             .09         116029.85        .21
System I/O         53005529             .36          78783.64        .14
Administrative           25               0               7.6          0
Scheduler              1925               0               .15          0
10 rows selected.
SQL>

Использование таблиц V$ для поиска информации об ожиданиях

Ключевыми динамическими таблицами для поиска информации об ожиданиях являются представления V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT и V$SESSION. Первые два представления показывают время ожидания по различным событиям.

Представление V$SYSTEM_EVENT показывает общее время ожидания по всем событиям во всей системе, начиная с момента запуска экземпляра. На отдельных сеансах с ожиданиями оно не фокусируется и потому дает общее высокоуровневое представление об ожиданиях в системе. Его можно применять для выяснения того, как выглядят главные события ожидания в масштабах экземпляра. Вычислять главные n событий ожидания в системе можно просто за счет разделения времени ожидания, ассоциируемого с одним событием, на общее время ожидания, ассоциируемое со всеми событиями.

Ключевую роль в представлении V$SYSTEM_EVENT играют три столбца: total_waits, в котором отображается общее количество ожиданий; time_waited, где отображается общее время ожидания, затраченное на каждый сеанс с момента запуска экземпляра; и average_wait, в котором отображается среднее время ожидания, потраченное во всех сеансах на каждое событие.

Представление V$SESSION_EVENT похоже на V$SYSTEM_EVENT и показывает информацию о том, сколько всего времени было потрачено на ожидание в каждом сеансе. Все события ожидания, имеющие место в отдельном сеансе, фиксируются в этом представлении на протяжении всего сеанса. Поэтому с помощью запроса к этому представлению можно легко выявить узкие места, возникавшие в каждом сеансе.

Третье динамическое представление V$SESSION_WAIT показывает информацию о текущих или только что завершившихся ожиданиях в сеансах. Сведения об ожиданиях в этом представлении постоянно меняются в зависимости от видов ожиданий, которые возникают в системе. Благодаря тому, что информация в это представление поступает в реальном времени, оно позволяет получать ясную картину о том, что же задерживает работу базы данных прямо сейчас. Представление V$SESSION_WAIT выдает детальную информацию по событиям ожидания вместе с номерами соответствующих им файлов, защелок и блоков. Такая детальная информация в представлении V$SESSION_WAIT позволяет выявлять точные узкие места, замедляющие работу базы данных, а более низкоуровневая — исходную причину проблем с производительностью.

Ниже перечислены столбцы из представления V$SESSION_WAIT, играющие важную роль при выявлении и устранении проблем с производительностью.

EVENT. В этом столбце отображаются различные события ожидания (например, события ожидания освобождения защелки и события ожидания занятых буферов), о которых более подробно будет рассказываться в следующем разделе.
P1, P2, P3. Здесь отображаются дополнительные параметры, которые представляют разные элементы, в зависимости от конкретного события ожидания. Например, в случае события ожидания последовательного считывания файла базы данных (db file sequential read) P1 будет представлять номер файла, P2 — номер блока, а P3 — количество блоков, а в случае события ожидания освобождения защелки (latch free) P1 будет представлять адрес защелки, P2 — номер защелки, а P3 — количество предпринятых попыток.
WAIT_CLASS_ID. В этом столбце отображается идентификатор класса событий ожидания.
WAIT_CLASS#. В этом столбце отображается номер класса событий ожидания.
WAIT_CLASS. В этом столбце отображается имя класса событий ожидания.
WAIT_TIME. В этом столбце отображается время ожидания в секундах, если в столбце STATE содержится значение is waited known time.
SECONDS_IN_WAIT. В этом столбце отображается время ожидания в секундах, если в столбце STATE содержится значение waiting.
STATE. В этом столбце может отображаться значение waited short time (ожидалось короткое время), waited known time (ожидалось известное количество времени) или waiting (ожидается), если сеанс ожидает события.

Четвертым связанным с ожиданиями представлением является V$SESSION. Это представление хранит не только много деталей о сеансе, но и также множество важной информации об ожиданиях. В нем содержатся все те же столбцы, что и в представлении V$SESSION_WAIT, плюс ряд других важных столбцов, имеющих отношение к сеансу. Из-за такого перекрытия информации об ожиданиях в представлениях V$SESSION и V$SESSION_WAIT, для просмотра большинства связанной с ожиданиями информации можно сразу использовать представление V$SESSION без обращения к V$SESSION_WAIT. Начать анализ событий ожидания в системе может оказаться удобнее, сначала выполнив запрос к представлению V$SYSTEM_EVENT для выяснения того, происходят ли вообще в базе данных какие-нибудь серьезные события ожидания. В листинге 20.15 приведен пример такого запроса.

SQL> SELECT event, time_waited, average_wait
2 FROM V$SYSTEM_EVENT
3 GROUP BY event, time_waited, average_wait
4* ORDER BY time_waited DESC;
EVENT                          TIME_WAITED     AVERAGE_WAIT
----------------------------  ------------  ---------------
rdbms ipc message                 24483121        216.71465
SQL*Net message from client       18622096        106.19049
PX Idle Wait                      12485418        205.01844
pmon timer                         3120909        306.93440
smon timer                         3093214      29459.18100
PL/SQL lock timer                  3024203       1536.68852
db file sequential read             831831           .25480
db file scattered read              107253           .90554 
free buffer waits                    52955         43.08787
log file parallel write              19958          2.02639
latch free 5884 1.47505
. . .
58 rows selected.
SQL>

В этом примере показана простая система, в которой не происходит почти никаких событий ожидания кроме событий простоя и событий ожидания SQL*Net. Серьезных событий ожидания, связанных с вводом-выводом или состязанием за получение защелки, в этой базе данных не наблюдается. Событие db file sequential read (вызываемое чтением индексов) и событие db file scattered (вызываемое полным сканирование таблиц) действительно кажутся значительными, но если сравнить причиненное ими время ожидания с общим временем, которое было потрачено на ожидание с момента запуска экземпляра, то в принципе они не так уж и выделяются. Более того, столбец AVERAGE_WAIT показывает, что оба они имеют низкий показатель по среднему времени ожидания (вызванному считыванием индексов). Более подробно об этих двух событиях, а также о нескольких других событиях ожидания в Oracle будет рассказываться позже в этом блоге, в разделе “Важные события ожидания в Oracle”. Однако в случае получения высоких показателей по любым не связанным с простоем событиям ожидания при выполнении запроса в реальной производственной системе, пожалуй, лучше выяснить, какие именно SQL-операторы служат причиной возникновения этих ожиданий, и постараться их сократить. Получать информацию об ассоциируемых с ожиданиями SQL-операторами можно несколькими способами, о которых речь пойдет в следующем разделе.

Получение информации об ожиданиях

Получать информацию об ожиданиях можно столь же легко, как выполнять запросы к соответствующим динамическим таблицам производительности. Например, при желании быстро выяснить, ожидания какого типа возникают в сеансах различных пользователей (информация уровня сеанса) и какие SQL-операторы при этом выполняются, можно с помощью следующего запроса:

SQL> SELECT s.username,
2 t.sql_text, s.event
3 FROM V$SESSION s, V$SQLTEXT t
4 WHERE s.sql_hash_value = t.hash_value
5 AND s.sql_address = t.address
6 AND s.type <> 'BACKGROUND'
7* ORDER BY s.sid,t.hash_value,t.piece;

На заметку! Перед этим необходимо обязательно включить механизм сбора статистических данных, либо установив в TRUE параметр TIMED_STATISTICS, либо установив в TYPICAL или ALL параметр инициализации STATISCTICS_LEVEL.

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

SQL> SELECT event, total_waits,time_waited
2 FROM V$SYSTEM_EVENT
3 WHERE event NOT IN
4 ('pmon timer','smon timer','rdbms ipc reply','parallel deque
5 wait','virtual circuit','%SQL*Net%','client message','NULL event')
6* ORDER BY time_waited DESC;
EVENT                    TOTAL_WAITS  TIME_WAITED
------------------------ ----------- ------------
db file sequential read     35051309     15965640
latch free                   1373973      1913357
db file scattered read       2958367      1840810
enqueue                         2837       370871
buffer busy waits             444743       252664
log file parallel write       146221       123435
SQL>

Вывод приведенного выше запроса показывает, что большую часть ожиданий в данном экземпляре причиняют ожидания, связанные с событием db file scattered read (чтение из файлов базы данных “вразброс”). Событие ожидания db file sequential read, как будет объясняться позже, вызывают операции полного сканирования таблиц. Поначалу легко может возникнуть путаница при использовании связанных с ожиданиями представлений V$, которые выглядят похоже. Поэтому давайте рассмотрим, как работать с ключевыми динамическими представлениями производительности в Oracle Database 11g.

Сначала необходимо заглянуть в представление V$SYSTEM_EVENT и выявить самые серьезные события ожидания по показателю общего и среднего ассоциируемого с ними времени ожидания. Начинать вычисление серьезных ожиданий следует с процентного показателя общего времени ожидания. Кроме того, можно заглянуть в отчеты AWR, которые могут генерироваться, потому что AWR тоже перечисляет пять самых серьезных событий ожидания в экземпляре.

Далее следует узнать больше деталей о том серьезном событии ожидания, которое идет первым в списке. Например, если первым идет событие buffer busy waits, тогда нужно заглянуть в представление V$WAITSTAT и выяснить, какие конкретно блоки в буферах (блоки данных, блоки отката и т.д.) вызывают его (простой запрос SELECT* к представлению V$WAITSTAT позволит получить всю необходимую информацию). Например, если причиной возникновения такого события по большей части служат блоки отката, тогда, значит, что-то не так с сегментами отката, а не с блоками данных.

И, наконец, напоследок нужно воспользоваться представлением V$SESSION и выяснять, какие точно объекты могут являться источником проблемы. Например, при наличии большого количества ожиданий типа db file scattered read, представление V$SESSION позволит узнать номера файлов и блоков, которые с ними связаны. В следующем примере представление V$SESSION служит для выяснения того, кто выполняет операции полного сканирования таблиц, которые сейчас вызывают самые серьезные события ожидания. Как объяснялось ранее, операции полного сканирования таблиц приводят к возникновению событий ожидания типа db file scattered read.

SQL> SELECT sid, sql_address, sql_hash_value
FROM V$SESSION WHERE event = 'db file scattered read';
Ниже приведен пример получения информации о текущем событии ожидания в определенном сеансе:

SQL> SELECT sid, state, event, wait_time, seconds_in_wait
2 FROM v$session
3* WHERE sid=1418;
SID   STATE    EVENT                   WAIT_TIME  SECONDS_IN_WAIT
---   ------   ----------------------- ---------  ---------------
1418  WAITING  db file sequential read    0             0
SQL>

Значение 0 в столбце WAIT_TIME свидетельствует о том, что в данном сеансе в текущий момент имеет место событие ожидания db file sequential read. По окончании этого события в столбцах WAIT_TIME и SECONDS_IN_WAIT появятся соответствующие значения.

Посредством представления V$SQLAREA можно выяснить, какие именно SQL-операторы ответственны за высокий показатель по операциям чтения с диска. Если преобладают события ожидания освобождения защелки, следует заглядывать в представление V$LATCH для получения более детальной информации о том, защелка какого типа служит причиной высокого показателя по времени ожидания освобождения защелки:

SQL> SELECT sid, blocking_session, username,
2 event, seconds_in_wait siw
3 FROM V$SESSION
4* WHERE blocking_session_status = 'VALID';
SID  BLOCKING_SESS USERNAME EVENT                           SIW
---- ------------- -------- ----------------------------- -----
1218     1527      UCR_USER enq: TX - row lock contention    23
1400     1400      APPOWNER latch free                        0
SQL>

Представление V$SESSION_WAIT_HISTORY

В представлении V$SESSION_WAIT_HISTORY содержится информация о десяти последних событиях ожидания в каждом активном сеансе. Во всех остальных связанных с ожиданиями представлениях, вроде V$SESSION и V$SESSION_WAIT, содержится информация только о самом недавнем событии ожидания, которое может быть коротким и потому не показательным. Ниже приведен пример выполнения запроса к представлению V$SESSION_WAIT_HISTORY:

SQL> SELECT seq#, event, wait_time, p1, p2, p3
2 FROM V$SESSION_WAIT_HISTORY
3 WHERE sid = 988
4* ORDER BY seq#;
SEQ#          EVENT          WAIT_TIME     P1        P2     P3
---- ----------------------- --------- ---------- ----- ------
   1 db file sequential read     0                   52  21944
   2 db file sequential read     0                   50  19262
   3 latch: shared pool          0 1.3835E+19       198      0
   4 db file sequential read     0                  205  21605
   5 db file sequential read     4                   52  13924
   6 db file sequential read     1                   49  29222
   7 db file sequential read     2                   52  14591
   8 db file sequential read     2                   52  12723
   9 db file sequential read     0                  205  11883
  10 db file sequential read     0                  205  21604
10 rows selected.
SQL>

Обратите внимание на то, что нулевое значение в столбце WAIT_TIME означает ожидание сеансом какого-то конкретного события. Ненулевое значение указывает на время, которое было потрачено на ожидание последнего события.

Анализ ожиданий с помощью компонента Active Session History

Представление V$SESSION_WAIT сообщает, какой ресурс ожидает сеанс. Представление V$SESSION тоже предоставляет важную информацию о событиях ожидания, происходящих в активных сеансах. Однако ни одно из этих представлений не дает хронологических данных о возникающих в экземпляре ожиданиях. По прошествии события ожидания просматривать информацию о нем с помощью представления V$SESSION_WAIT становится невозможным. Ожидания настолько скоротечны, что к моменту выполнения запроса к указанным представлениям они в большинстве случаев уже заканчиваются. Новый компонент Active Session History (Хронология активных сеансов), или ASH, за счет фиксирования информации сеансов позволяет возвращаться обратно во времени и просматривать хронологию возникновения в базе данных того или иного узкого с точки зрения производительности места. Хотя компонент AWR по умолчанию производит каждый час снимки экземпляра, анализировать события, которые возникали пять или десять минут назад, на основе его данных не возможно. Вот здесь очень кстати оказывается информация средства ASH, которое каждую секунду производит выборку данных из представления V$SESSION и таким образом собирает информацию по событиям ожидания всех активных сеансов. Активным называется такой сеанс, который либо уже использует ресурсы ЦП, либо ожидает их получения. Просматривать собираемые ASH статистические данные можно через представление V$ACTIVE_SESSION_HISTORY, в котором предусмотрено по одной строке для каждого активного сеанса в экземпляре. ASH представляет собой своего рода непрерывно обновляемый буфер в памяти, в котором более старая информация постоянно перезаписывается новыми данными сеансов.

Каждые 60 минут фоновый процесс MMON сбрасывает отфильтрованные данные ASH на диск в виде части ежечасных снимков AWR. Если буфер ASH полон, фоновый процесс MMNL выполняет сброс данных из него. После сбрасывания данных ASH на диск увидеть их в представлении V$ACTIVE_SESSION_HISTORY больше невозможно. С этого момента для их просмотра необходимо применять представление DBA_HIST_ACTIVE_SESS_HISTORY.

В следующих разделах показано, как выполнять запросы к представлению V$ACTIVE_SESSION_HISTORY для анализа текущих (недавних) данных Active Session History.

Использование представления V$ACTIVE_SESSION_HISTORY

Представление V$ACTIVE_SESSION_HISTORY предлагает окно для просмотра удерживаемых в памяти данных ASH по экземпляру Oracle до того, как они будут сброшены на диск в виде части ежечасных снимков AWR. Его можно использовать для получения информации, например, об SQL-операторах, которые потребляют большую часть ресурсов в базе данных, о конкретных объектах, которые служат причиной возникновения большинства ожиданий, и о пользователях, которым приходится проводить в ожидании больше всего времени.

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

Объекты с самыми высокими показателями по событиям ожидания

С помощью следующего запроса можно выяснить, какие объекты имеют самые высокие показатели по событиям ожидания, и что за события были у них в последние 15 минут:

SQL> SELECT o.object_name, o.object_type, a.event,
2 SUM(a.wait_time +
3 a.time_waited) total_wait_time
4 FROM v$active_session_history a,
5 dba_objects o
6 WHERE a.sample_time between sysdate - 30/2880 and sysdate
7 AND a.current_obj# = o.object_id
8 GROUP BY o.object_name, o.object_type, a.event
9* ORDER BY total_wait_time;
OBJECT_NAME  OBJECT_TYPE EVENT                      TOTAL_WAIT_TIME
------------ ----------- ------------------------- ----------------
UC_ADDRESS   TABLE       SQL*Net message to client                2
PERS_PHONES  TABLE       db file sequential read               8836
PAY_FK_I     INDEX       db file sequential read               9587
UC_STAGING   TABLE       log file sync                        23633 
PERSONNEL    TABLE       db file sequential read              43612
SQL>

Самые важные события ожидания

Посредством следующего запроса можно получать список самых важных событий ожидания, которые произошли в базе данных в последние 15 минут:

SQL> SELECT a.event,
2 SUM(a.wait_time +
3 a.time_waited) total_wait_time
4 FROM v$active_session_history a
5 WHERE a.sample_time between
6 sysdate - 30/2880 and sysdate
7 GROUP BY a.event
8* ORDER BY total_wait_time DESC;
EVENT                              TOTAL_WAIT_TIME
------------------------------  ------------------
wait for SGA component shrink            878774247
smon timer                               300006992
PL/SQL lock timer                        210117722
SQL*Net message from client               21588571
db file scattered read                     1062608
db file sequential read                     105271
log file sync                                13019
latch free                                     274
SQL*Net more data to client                     35
null event                                       6
17 rows selected.
SQL>

Пользователи с наиболее высокими показателями по времени ожидания

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

SQL> SELECT s.sid, s.username,
2 SUM(a.wait_time +
3 a.time_waited) total_wait_time
4 FROM v$active_session_history a,
5 v$session s
6 WHERE a.sample_time between sysdate - 30/2880 and sysdate
7 AND a.session_id=s.sid
8 GROUP BY s.sid, s.username
9* ORDER BY total_wait_time DESC; 
SID   USERNAME   TOTAL_WAIT_TIME
----  ---------  ---------------
1696  SYSOWNER         165104515
885   SYSOWNER          21575902
1087  BLONDI             5019123
1318  UCRSL               569723
1334  REBLOOM             376354
1489  FRAME                  395
15 rows selected.
SQL>

Определение SQL-кода с наиболее высокими показателями по времени ожидания

Посредством следующего запроса можно определить SQL-код, которому пришлось ожидать выполнения дольше всего. В данном примере время выборки охватывает только последние 15 минут.

SQL> SELECT a.user_id,d.username,s.sql_text,
2 SUM(a.wait_time + a.time_waited) total_wait_time
3 FROM v$active_session_history a,
4 v$sqlarea s,
5 dba_users d
6 WHERE a.sample_time between sysdate - 30/2880 and sysdate
7 AND a.sql_id = s.sql_id
8 AND a.user_id = d.user_id
9* GROUP BY a.user_id,s.sql_text, d.username;
USER_ID     USERNAME           SQL_TEXT             TOTAL_WAIT_TIME
----------  --------  ----------------------------  ----------------
    0       SYS       BEGIN dbms_stats . . .; END;           9024233
. . .
SQL>

Классы событий ожидания и связанные с ними представления

Представление V$SESSION_WAIT показывает события и ресурсы, которых ожидают активные сеансы. С помощью этого представления также можно узнавать и о том, к классам какого типа относятся происходящие в сеансах события ожидания. Ниже приведен соответствующий пример:

SQL> SELECT wait_class, event, sid, state, wait_time, seconds_in_wait
FROM v$session_wait
ORDER BY wait_class, event, sid;
WAIT_CLASS   EVENT                        SID   STATE     WAIT_TIM   SEC_IN_WAIT
----------   --------------------------   ---   -------   --------   -----------
Application  enq: TX -                    269 WAITING     0                   73
             row lock contention
Idle         Queue Monitor Wait           270 WAITING     0                   40
Idle         SQL*Net message from client  265 WAITING     0                   73
Idle         jobq slave wait              259 WAITING     0                 8485
Idle         pmon timer                   280 WAITING     0                   73
Idle         rdbms ipc message            267 WAITING     0               184770
Idle         wakeup time manager          268 WAITING     0                   40
Network      SQL*Net message to client    272 WAITED SHORT TIME                1
SQL>

В выводе показанного выше запроса можно видеть, что самое важное событие ожидания относится к классу Application.

Представление V$SYSTEM_WAIT_CLASS позволяет просматривать показатели по времени ожидания в разбитом по классам виде:

SQL> SELECT wait_class, time_waited
FROM v$system_wait_class
ORDER BY time_waited DESC;
WAIT_CLASS             TIME_WAITED
--------------------  ------------
Idle                    1.0770E+11
User I/O                4728148400
Other                    548221433
Concurrency              167154949
Network                   56271499
Application               46336445
Commit                    35742104
Configuration             11667683
System I/O                 8045920
Administrative                 760
Scheduler                       16
11 rows selected.
SQL>

Представление V$SESSION_WAIT_CLASS позволяет узнать, сколько всего времени было потрачено на события ожидания каждого класса в отдельном сеансе, например:

SQL> SELECT wait_class, time_waited
2 FROM v$session_wait_class
3 WHERE sid = 1053
4* ORDER BY time_waited DESC;
WAIT_CLASS         TIME_WAITED
-----------------  -----------
Idle                     21190
User I/O                  8487
Other                       70
Concurrency                 13
Application                  0
Network                      0
6 rows selected.
SQL>

Представление V$WAITCLASSMETRIC позволяет просматривать метрические значения классов событий ожидания за прошедшие 60 секунд. В этом представлении информация хранится вплоть до одного часа. Ниже приведен пример выполнения запроса к этому представлению:

SQL> SELECT WAIT_CLASS#, WAIT_CLASS_ID
2 dbtime_in_wait,time_waited,wait_count
3 FROM v$waitclassmetric
4* ORDER BY time_waited DESC;
WAIT_CLASS#  DBTIME_IN_WAIT  TIME_WAITED  WAIT_COUNT
-----------  --------------  -----------  ----------
          6      2723168908       170497       51249
          0      1893977003         5832          58
          8      1740759767          717        1351
          5      3386400367           11          68
          7      2000153315            8       52906
          9      4108307767            6          99
          1      4217450380            0           4
          2      3290255840            0           0
          3      4166625743            0           0
         11      3871361733            0           0
         10      2396326234            0           0
          4      3875070507            0           0
12 rows selected.
SQL>

Как не трудно заметить, WAIT_CLASS 6 возглавляет список, а это говорит о том, что в текущий момент большую часть времени занимают события ожидания класса Idle.

Просмотр статистических данных на уровне сегментов

При использовании данных AWR и данных из связанных с событиями ожидания представлений V$ информацию о том, где именно происходит конкретное событие ожидания, найти не получится. Например, по данным из представления V$SYSTEM_EVENT можно увидеть, что проблему представляют события ожидания занятых буферов (buffer busy), которые, как известно, можно сократить за счет переключения с ручного на автоматическое управление пространством в сегментах (Automatic Segment Space Management — ASSM). Однако, ни в данных AWR, ни в данных этого представления V$, не будет никаких сведений о том, какие именно таблицы или индексы следует проанализировать для снижения высокого показателя по событиям ожидания. Для оказания помощи в этом вопросе в Oracle предлагаются три специальных представления V$, которые позволяют просматривать данные на уровне сегментов.

В частности, динамические представления производительности, позволяющие просматривать данные на уровне сегментов, выглядят так: V$SEGSTAT_NAME, V$SEGSTAT и V$SEGMENT_STATISTICS. С помощью этих представлений легко выяснить, какие из таблиц и индексов стали причиной высоких показателей потребления ресурсов или времени ожидания. Узнав о наличии проблемы с производительностью из-за высоких показателей по количеству ожиданий, можно использовать эти представления для получения информации о том, какая конкретно таблица или индекс служит источником данной проблемы, вносить в этот объект необходимые поправки и тем самым сокращать ожидания и увеличивать производительности базы данных.

Представление V$SEGMENT_NAME предоставляет список всех уровней сегментов, которые могут подвергаться сбору информации, и сообщает, производится ли выборка статистических данных на этих уровнях в текущий момент.

Давайте посмотрим, как применять эти представления сегментного уровня при столкновении с высоким показателем по количеству событий ожидания в системе. Предположим, что после запроса представления V$SYSTEM_EVENT оказывается, что в системе происходит слишком большое количество событий ожидания типа buffer busy wait. Тогда далее достаточно выполнить показанный ниже запрос к представлению V$SEGMENT_STATISTICS, выяснив, какой объект служит причиной столь высокого показателя по количеству событий типа busy buffer waits, и затем выбрать подходящие меры для события этого типа, о которых будет более подробно рассказываться в разделе “Важные события ожидания в Oracle” настоящей статьи.

SQL> SELECT owner, object_name, object_type, tablespace_name
2 FROM V$SEGMENT_STATISTICS
3 WHERE statistic_name='buffer busy waits'
4* ORDER BY value DESC;
OWNER      OBJECT_NAME     OBJECT_TYPE  TABLESPACE_NAME
---------  --------------  -----------  ---------------
SYSOWNER   LAB_DATA        TABLE        LAB_DATA_D
SYSOWNER   LAB_ADDR_I      INDEX        LAB_DATAS_I
SYSOWNER   PERS_SUMMARIES  TABLE        PERS_SUMMARIES_D
. . .
SQL>

Сбор детализированной информации о событиях ожидания

Выбирать данные из динамических представлений производительности V$ и интерпретировать их понятным образом не всегда удается легко. Из-за того, что эти представления являются динамическими по своей природе, содержащаяся в них информация постоянно меняется по мере того, как Oracle обновляет базовые таблицы для каждого события ожидания. Все только что рассмотренные динамические представления производительности не предоставляют важных данных вроде информации о переменных связывания. Для получения детализированной информации о событиях ожидания можно воспользоваться одним из методов, которые описаны в последующих разделах.

Метод 1: использование события Oracle 10046 для трассировки SQL-кода

Получать все виды информации о переменных связывания можно за счет использования специальной трассировки 10046, которая гораздо совершенней утилиты SQL Trace. Применение этой трассировки приводит к записи файла вывода в каталог трассировки. Трассировку 10046 можно настраивать по-разному за счет указания различных уровней, каждый из которых предусматривает получение более детализированной информации. (Уровень 12 используется в показанном ниже случае только для примера: он может предоставлять гораздо больше информации, чем необходимо. Уровень 4 позволяет получать детальную информацию о значениях переменных связывания, а уровень 8 — о событиях ожидания.)

В частности, активизировать трассировку 10046 можно либо оператором ALTER SESSION:

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever level 12';
Session altered.
SQL>

либо добавлением в файл init.ora следующей строки:

event = 10046 trace name context forever, level 12

Метод 2: использование утилиты oradebug для выполнения трассировки

Для трассировки можно применять утилиту oradebug, как показано в следующем примере:

SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER LEVEL 8;
Statement processed.
SQL>

В этом примере параметр SETMYPID указывает, что требуется выполнить трассировку текущего сеанса. Если необходимо провести трассировку для другого сеанса, достаточно заменить этот параметр параметром SETOSPID <идентификатор_процесса>.

Метод 3: использование пакета DBMS_SYSTEM для настройки трассировки

С помощью процедуры SET_EV из пакета DBMS_SYSTEN можно активизировать трассировку в любом сеансе, как показано в следующем примере:

SQL> EXECUTE SYS.DBMS_SYSTEM.SET_EV (9,271,10046,12,'');
PL/SQL procedure successfully completed.
SQL>

Метод 4: использование пакета DBMS_MONITOR

Пакет DBMS_MONITOR предлагает простой способ сбора расширенной трассировочной информации по сеансам. Активизировать трассировку в сеансе пользователя можно с помощью процедуры SESSION_TRACE_ENABLE из этого пакета. Структура этой процедуры выглядит следующим образом:

DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id      IN BINARY_INTEGER DEFAULT NULL,
serial_num      IN BINARY_INTEGER DEFAULT NULL,
waits           IN BOOLEAN DEFAULT TRUE,
binds           IN BOOLEAN DEFAULT FALSE)

В случае установки для параметра WAITS значения TRUE трассировка будет включать информацию о событиях ожидания, а в случае установки значения TRUE для параметра BINDS — соответственно, информацию о переменных связывания.

В случае не указания значения для параметра SESSION_ID или указания для него значения NULL, трассировке будет подвергаться ваш собственный сеанс. Ниже показано, как выполнять трассировку своего сеанса с помощью пакета DBMS_MONITOR:

SQL> EXECUTE dbms_monitor.session_trace_enable (waits=>TRUE, binds=>TRUE);

Помимо всех перечисленных выше методов для сбора информации по событиям ожидания, еще доступен удобный инструмент OEM Database Control, который позволяет углубляться в детали различных элементов с домашней страницы Database Control.

На заметку! В отчете AWR, который можно получать посредством сценария awrrpt.sql, и отчете ADDM, который можно получать с помощью сценария addmrpt.sql, тоже содержатся обширные объемы информации о событиях ожидания.

Важные события ожидания в Oracle

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

На заметку! С помощью запроса SELECT NAME FROM V$EVENT_NAME можно получать полный перечень возможных событий ожидания в Oracle.

Событие ожидания buffer busy waits

Событие ожидания buffer busy waits (событие ожидания занятых буферов) возникает в области кэша буферов тогда, когда к одному и тому же буферу получить доступ пытаются сразу несколько процессов. Один сеанс ожидает, когда другой сеанс завершит считывать буфер в кэше буферов. Такое событие ожидания также может возникать и тогда, когда буфер находится в кэше буферов, но какой-то другой сеанс изменяет его.

На заметку! Начиная с выпуска Oracle Database 10.2, события ожидания занятых буферов были поделены на несколько событий: теперь может существовать совсем немного событий ожидания занятых буферов, но очень много событий ожидания завершения считывания другими сеансами, которые ранее тоже преподносились как события ожидания занятых буферов.

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

Двумя наиболее типичными причинами большого количества событий ожидания buffer busy waits является возникновение состязаний на уровне блоков данных, принадлежащих таблицам и индексам, и возникновение состязаний на уровне блоков заголовков сегментов. В случае использования управляемых словарем табличных пространств или локально управляемых табличных пространств с ручным управлением пространством в сегментах, нужно предпринимать следующие меры.

Если события ожидания происходят в основном на уровне блоков данных — пробовать повышать значение параметра PCTFREE для снижения количества строк в каждом блоке данных. (При желании можно также попытаться повысить значение параметра INITRANS для уменьшения состязания со стороны конкурирующих транзакций.)
Если события ожидания происходят в основном на уровне заголовков сегментов — увеличивать количество списков или групп списков свободных блоков для запрашиваемого сегмента или рассматривать вариант увеличения размера экстентов для таблицы или индекса.

Наилучшим способом для сокращения событий ожидания buffer busy waits, происходящих из-за состязаний на уровне заголовков сегментов, является применение локально управляемых табличных пространств с автоматическим управлением пространством в сегментах (ASSM). Автоматическое управление пространством в сегментах также решает и проблему возникновения состязания за блоки данных в таблицах и индексах.

Помимо состязаний за заголовки сегментов и блоки данных, могут возникать состязания за заголовки сегментов отката (rollback segment headers) и блоки сегментов отката (rollback segment blocks). В случае использования технологии автоматического управления пространством отката (Automatic Undo Management — AUM), однако, для устранения проблемы с состязаниями за заголовки и блоки отката достаточно гарантировать, что табличное пространство, отвечающее за управление данными отката, имеет достаточно места. В таком случае останется следить только за тем, чтобы не возникало состязаний за блоки данных и за заголовки сегментов в таблицах и индексах. Ниже для примера приведен запрос, вывод которого четко показывает, что этой базе данных больше всего событий ожидания buffer busy waits происходит на уровне блоков данных:

SQL> SELECT class, count FROM V$WAITSTAT
2 WHERE COUNT > 0
3* ORDER BY COUNT DESC;
CLASS                 COUNT
---------------  ----------
data block           519731
undo block             5829
undo header            2026
segment header           25
SQL>

Если события ожидания buffer busy waits на уровне блоков данных представляют серьезную проблему даже при использовании ASSM, причина может заключаться в плохо подобранных индексах, приводящих к выполнению сканирования больших диапазонов. Для устранения событий ожидания в таком случае можно попробовать использовать глобальные индексы, секционированные по хеш-значениям, или настроить подходящим образом сами SQL-операторы. В Oracle заявляют, что в случае применения AUM вместо традиционных сегментов отката два типа событий ожидания buffer busy waits, возникающих из-за состязания за блоки и заголовки сегментов отката, исчезнут. На практике, однако, этого не происходит, как показывает следующий пример, выполненный в базе данных с AUM:

CLASS               COUNT
--------------- ---------
undo header         29891
data block             52
segment header          1

Иногда всплески по количеству событий ожидания buffer busy waits могут случаться вроде бы безо всякой причины. Вывод утилиты sar (sar -d) может сообщать о высоких показателях по очередям запросов и времени обслуживания. Подобное часто происходит при загрузке контроллеров дисков большим количеством операций ввода- вывода. Обычно в это время наблюдаются излишние операции дампа ядра и, если они мешают работе подсистеме ввода-вывода, можно предпринять следующие действия.

Переместить каталог данных дампа ядра в менее загруженную файловую систему, где он будет находиться сам по себе.
Использовать показанные ниже параметры в файле init.ora или SPFILE для управления операциями дампа ядра в системе. Установка значений для этих параметров может приводить к сокращению размера дампа ядра с гигабайта до нескольких мегабайт, а то и больше:

      SHADOW_CORE_DUMP = PARTIAL /* или NONE */
      BACKGROUND_CORE_DUMP = PARTIAL /* или NONE */

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

Событие ожидания сheckpoint сompleted

Событие ожидания сheckpoint сompleted (событие ожидания создания контрольной точки) означает, что сеанс ожидает окончания операции создания контрольной точки, и может возникать как при останове экземпляра базы данных, так и во время создания обычных контрольных точек.

Событие ожидания db file scattered read

Событие ожидания db file scattered read (событие ожидания выполнения чтения из файлов базы данных “вразброс”) свидетельствует о том, что в базе данных происходят операции полного сканирования таблиц (или операции быстрого полного сканирования индексов). Параметр инициализации DB_FILE_MULTIBLOCK_READ_COUNT задает количество блоков, которое Oracle может считывать за раз. База данных будет автоматически настраивать этот параметр в случае не указания для него значения в файле параметров. Хотя Oracle и считывает данные многоблочными фрагментами, они разбрасываются по буферам в кэше непоследовательно. Если операции полного сканирования таблиц выполняются редко и в основном в отношении небольших таблиц, о событии db file scattered read можно не волноваться.

Однако если это событие оказывается важным событием ожидания, необходимо относится к нему как к сигналу о наличии проблем с вводом-выводом, т.е. о том, что базе данных не удается справляться с чрезмерным спросом на физические операции ввода-вывода. В таком случае есть два возможных решения. Первое заключается в сокращении спроса на физические операции ввода-вывода, а второе — в увеличении пропускной способности системы таким образом, чтобы она могла справляться с большим количеством подобных операций. Сокращать спрос на физические операции ввода-вывода можно путем проведения дальнейшего анализа и выяснения того, не сработает ли одно из следующих решений. Увеличение компонента кэша буферов в SGA обычно помогает снижать количество физических операций ввода-вывода. Однако в случае, если размер кэша буферов уже является оптимальным в результате использования технологии автоматического управления разделяемой памятью (Automatic Shared Memory Management) за счет установки параметра инициализации SGA_TARGET, можно пробовать проделать следующее:

добавить недостающие индексы на уровне ключевых таблиц (что маловероятно в производственной системе);
оптимизировать SQL-операторы, если они не следуют эффективному плану выполнения.

При отсутствии возможности сократить потребность в физических операциях ввода-вывода, остается только увеличивать количество дисков в системе. Также необходимо не забыть сокращать количество “горячих” точек в системе за счет правильного распределения интенсивно запрашиваемых таблиц и индексов между доступными дисками. Определять файлы данных, в которые происходят операции полного сканирования таблиц или полного быстрого сканирования индексов, можно посредством запроса к представлению V$FILESTAT. В этом представлении очень полезными являются два следующих столбца:

phyrds, в котором отображается количество выполненных физических операций чтения;
phyblkrd, в котором отображается количество считанных физическим образом блоков.

Очевидно, что значение в столбце phyrds должно равняться или быть близким к значению в столбце phyblkrd, потому что практически все операции чтения подразумевают считывание одного единственного блока. Наличие в столбце phyrds гораздо меньшего значения, чем в столбце phyblkrds, свидетельствует о выполнении Oracle считывания множества блоков за одну операцию, что возможно, например, при полном сканировании таблицы или полном быстром сканировании индекса. Ниже приведен пример запроса к представлению V$FILESTAT:

SQL> SELECT file#, phyrds,phyblkrd
2 FROMV$FILESTAT
3* WHERE phyrds != phyblkrd;
     FILE#  PHYRDS  PHYBLKRD
----------  ------  --------
         1    4458     36533
         7   67923    494433
        15   28794    378676
        16   53849    408981
SQL>

Событие ожидания db file sequential read

Событие ожидания db file sequential read (событие ожидания завершения последовательного чтения из файлов базы данных) свидетельствует о том, что в базе данных происходят операции считывания одного блока в кэш буферов. Оно возникает при выполнении индексного чтения (indexed read) и ожидании возврата физического вызова ввода-вывода. Волноваться здесь не о чем, поскольку база данных должна дожидаться файлового ввода-вывода. Если статистический показатель по данному событию кажется чрезмерно высоким, следует изучить данные по дисковому вводу-выводу. При наличии слишком большого количества дисковых сортировок, это количество можно снизить за счет увеличения значения параметра инициализации PGA_AGGREGATE_TARGET. Поскольку само присутствие данного события является свидетельством того, что приложение очень интенсивно использует индексы, для сокращения спроса в физических операциях ввода-вывода в таком случае мало что можно делать в отличие от случая с событием db file scattered read. Увеличение количества дисков и распределения индексов между ними может оказаться наилучшим вариантом для сокращения событий ожидания db file sequential read. Если объекты имеют не слишком большой размер, можно использовать пулы буферов DEFAULT и KEEP для их сохранения в памяти. Однако если объекты имеют большой размер, такой вариант может быть не доступен. Операции индексного чтения будут сопровождаться в большинстве систем событиями ожидания и это вовсе не обязательно плохо, поскольку индексы требуются в большинстве случаев для более быстрого извлечения данных.

События ожидания direct path read и direct path write

События ожидания direct path read (событие ожидания выполнения чтения в прямом режиме) и direct path write (событие ожидания выполнения записи в прямом режиме) возникают при выполнения чтения или записи напрямую в PGA без использования кэша буферов SGA. Наличие событий direct path read свидетельствует о том, что операции сортировки в системе выполняются на диске, а не в памяти. Кроме того, это может свидетельствовать о загруженности системы ввода-вывода. В случае применения автоматической настройки PGA подобная проблема не должна возникать слишком часто.

Автоматическая настройка PGA самой Oracle должна сокращать выполнение сортировок на диске из-за низкого объема выделяемой под PGA памяти. Другим решением может быть увеличение количества дисков, поскольку данная проблема также возникает из-за неспособности системы ввода-вывода справляться с возрастающими запросами на чтение блоков в PGA. Разумеется, настройка самих SQL-операторов для сокращения количества сортировок в таком случае совершенно не повредит.

События ожидания free buffer waits

События ожидания free buffer waits (события ожидания свободных буферов) обычно возникают тогда, когда процесс записи данных в базу данных (database writer —DBWR) работает слишком медленно. Он просто не успевает обрабатывать запросы на обслуживание кэша буферов. Количество “грязных” (dirty) буферов в кэше, ожидающих записи содержимого на диск, превышает число буферов, которое этот процесс может обрабатывать одновременно. В это время сеансам приходится ожидать, поскольку они не могут получить доступ к свободным буферам для выполнения в них записи своих данных. В такой ситуации первым делом нужно выяснить, не является ли кэш буферов слишком маленьким, и проверить показатели ввода-вывода на сервере, особенно по времени записи, с помощью соответствующей утилиты операционной системы.

События постановки в очередь

События постановки в очередь (enqueue) похожи на блокировки тем, что они тоже представляют собой внутренние механизмы, которые управляют доступом к ресурсам. Высокий показатель по таким событиям свидетельствует о том, что большое количество сеансов ожидает снятия блокировок, удерживаемых другими сеансами. Для выяснения того, на какие из них уходит больше всего времени, можно использовать динамическое представление производительности V$ENQUEUE_STAT, а именно — содержащийся в нем столбец cum_wait_time, в котором отображается общее время, потраченное на ожидание из-за событий enqueue.

Обратите внимание на то, что применение локально управляемых табличных пространств исключает возникновение нескольких типов таких событий, наподобие space transactions (ST) enqueues. В системе с обширной базой одновременных пользователей события постановки в очередь чаще всего происходят из-за нечастого выполнения фиксации (или отката) транзакций, что вынуждает другие транзакции ожидать снятия блокировок, удерживаемых более ранними транзакциями. Кроме того, они еще могут возникать из-за слишком маленького количества слотов для списка заинтересованных транзакций (interested transactions list — ITL) и тогда имеют вид событий transaction (TX) enqueue. Использование локально управляемых табличных пространств позволяет избегать большинства связанных с пространством типов событий enqueue.

Событие ожидания latch free

Защелками (latches) называются внутренние механизмы сериализации, используемые для защиты разделяемых структур данных в Oracle SGA. В принципе, защелку можно считать особой разновидностью блокировки, которая удерживается на протяжении чрезвычайно короткого периода времени. В Oracle существует несколько типов защелок, каждый из которых охраняет доступ к определенному набору данных. Событие ожидания latch free (событие ожидания освобождения защелки) возникает, когда процессу не удается получить защелку с первой попытки. Если требуемая защелка Oracle недоступна, процесс, запрашивающий ее, продолжает ходить по кругу и повторять попытки получить к ней доступ. Это хождение по кругу увеличивает как время ожидания, так и степень использования ресурсов ЦП в системе. В Oracle применяется около 500 защелок, но двумя наиболее важными из них являются shared pool latch (защелка разделяемого пула) вместе с защелками библиотечного кэша и cache buffers LRU chain; обе они могут фигурировать в статистических данных по событиям ожидания. Частое возникновение события ожидания освобождения защелки является нормальным для экземпляра. Начинать беспокоится об этом событии следует только в том случае, если на него в общем тратится слишком большое количество времени.

Информация о большом количестве событий ожидания защелок будет появляться в отчетах AWR, но еще можно использовать показанный в листинге 20.17 запрос для определения соотношения удачных и неудачных попыток получения доступа к защелке (latch hit ratio).

SQL> SELECT a.name "Latch Name",
a.gets "Gets (Wait)",
a.misses "Misses (Wait)",
(1 - (misses / gets)) * 100 "Latch Hit Ratio %"
FROM V$LATCH a
WHERE a.gets != 0
UNION
SELECT a.name "Latch Name",
a.gets "Gets (Wait)",
a.misses "Misses (Wait)",
100 "Latch Hit Ratio"
FROM V$LATCH a
WHERE a.gets = 0
ORDER BY 1;
SQL>

Если коэффициент соотношения далеко не равен одному (1), значит, пришла пора подумать о настройке состязания за защелки в экземпляре. Защелка shared pool latch применяется в базе данных только в одном экземпляре и обеспечивает защиту от выделения памяти в библиотечном кэше. Защелка library cache latch координирует доступ к представленным в библиотечном кэше объектам. Любому SQL-оператору, PL/SQL-коду, процедуре, функции или пакету необходимо получать эту защелку перед выполнением. Если показатели по защелкам shared pool latch и library cache latch являются высокими, чаще всего причина заключается в высоких показателях по количеству операций синтаксического анализа. Показатели по количеству операций синтаксического анализа могут быть высокими из-за следующих факторов:

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

Причиной возникновения события ожидания cache buffers LRU chain latch free является высокий коэффициент использования кэша буферов, выполнение операций полного сканирования таблиц или применение неизбирательных (unselective) индексов, которые вынуждают сканировать слишком большие диапазоны индексных данных.

Неизбирательные индексы могут также приводить к возникновению еще одного вида событий ожидания — событий cache buffer chain latch free. Такие события часто происходят из-за присутствия “горячих” блоков, поэтому для выяснения того, почему они могут возникать, понадобится проводить анализ. Высокий показатель по событиям ожидания row cache object latch waits свидетельствует о состязании за кэш словаря данных и требует увеличения объема памяти, выделяемой под разделяемый пул.

В большинстве случаев ожидания, связанные с защелками, проявляются в виде событий ожидания и попадаются на глаза администраторам баз данных в списке всех событий ожидания. Как и в случае остальных событий ожидания в Oracle, прежде чем предпринимать что-либо по этому поводу, нужно сначала задать себе вопрос: “Составляют ли эти события ожидания освобождения защелки значительную часть общего времени ожидания?”. Если нет, тогда совершенно не стоит о них беспокоиться: задачей администратора баз данных не является попытка устранения всех событий ожидания в экземпляре, потому что это просто не возможно.

События ожидания log buffer space

События ожидания log buffer space (события ожидания освобождения пространства в журнальном буфере) свидетельствует об ожидании процессом освобождения пространства в журнальном буфере. Они возникают либо из-за того, что журнальный буфер имеет слишком маленький размер, либо из-за того, что данные повторного выполнения записываются быстрее, чем процесс записи данных в журналы успевает записывать их в буфер журналов повторного выполнения. Если размер буфера журналов повторного выполнения уже достаточно большой, тогда необходимо проанализировать показатели ввода-вывода на диске, где размещаются файлы журналов повторного выполнения.

Возможно, имеют место состязания за диск и тогда необходимо их снизить. События такого типа обычно появляются, когда буфер журналов слишком мал, в случае чего достаточно просто увеличить размер этого буфера. Большой буфер журналов имеет тенденцию в целом сокращать количество операций ввода-вывода, связанных с журналами повторного выполнения. Обратите внимание, что по умолчанию в Oracle размер этого буфера составляет несколько мегабайт. При наличии большого количества огромных транзакций, может быть лучше повысить принятое по умолчанию значение, должным образом установив параметр инициализации LOG_BUFFER, хотя слишком высокое значение чревато записью в файлы журналов повторного выполнения слишком большого количества данных.

События ожидания log file switch

Событие ожидания log file switch (событие ожидания переключения файлов журналов) может возникать, когда сеанс вынужден ожидать переключения журнального файла из-за того, что тот еще не был заархивирован, а также, когда для переключения требуется ожидать завершения создания контрольной точки.

Если проблема состоит не в переполнении целевого каталога архива, это означает, что процессу архивирования не удается справиться со скоростью, с которой происходит архивирование журналов повторного выполнения. В таком случае необходимо просто увеличить количество процессов архивирования (ARCn). По умолчанию для параметра ARCn установлено значение 2. Этот параметр является статическим, поэтому устранить проблему замедления работы сразу же не получится.

Вдобавок необходимо проверять, не служит ли причиной возникновения событий log file switch слишком маленький размер файлов журналов повторного выполнения. Если переключение файлов журналов не происходит из-за ожидания завершения создания контрольной точки, очевидно, что файлы журналов имеют слишком маленький размер и, следовательно, быстро заполняются. В таком случае потребуется просто увеличить размер файлов журналов повторного выполнения. Файлы журналов повторного выполнения добавляются и удаляются в оперативном режиме, поэтому можно считать это динамическим изменением.

Наличие высоких значений показателя log space requests (запросы на выделение пространства в журналах) в представлении V$SYSSTAT свидетельствует о том, что пользовательским процессам приходится ожидать выделения пространства в буфере журналов повторного выполнения. Подобное происходит из-за того, что процессу записи в журналы (LGWR) не удается найти свободный файл журнала повторного выполнения для высвобождения в него содержимого журнального буфера. В таком случае нужно, опять-таки, изменить размер журналов повторного выполнения и добиться того, чтобы переключение между ними происходило каждые 15–30 минут.

События ожидания log file sync

Высокий показатель по количеству событий ожидания log file sync (события ожидания синхронизации файлов журналов) появляется, если серверным процессам приходится часто дожидаться того, пока процесс записи в журналы завершит запись зафиксированных транзакций (данных повторного выполнения) в файлы журналов повторного выполнения из журнального буфера. Обычно подобное происходит из-за слишком частого выполнения фиксаций, и потому сократить данный показатель можно за счет применения массовых фиксаций вместо выполнения фиксации каждой транзакции по отдельности. Событие log file sync также может появляться в результате образования узкого места в подсистеме ввода-вывода.

События простоя

Ряд событий ожидания можно отнести к категории событий простоя (idle events). Некоторые из них могут быть совершенно безвредными и просто показывать, что процессу Oracle пришлось ожидать действий. Эти события не свидетельствуют ни об образовании узких мест в базе данных, ни о возникновении состязаний за ресурсы Oracle. Например, система может просто ожидать предоставления клиентским процессом SQL- операторов для выполнения. Ниже перечислены некоторые наиболее часто встречающиеся события простоя.

Rdbms ipc message. Это событие используют фоновые процессы, подобные процессу записи в журналы и процессу PMON, для обозначения того, что они пребывают в состоянии простоя.
SMON timer. Это событие указывает на простаивание процесса SMON.
PMON timer. Это событие указывает на простаивание процесса PMON.
SQL*Net message from client. Это событие указывает на простаивание пользовательского процесса.

Во время настройки экземпляра на многие из событий простоя не следует обращать внимание. Однако некоторые из них, например, событие SQL*Net message from client, могут служить признаком того, что в приложении используется неэффективная стратегия подключения к базе данных. В таком случае необходимо предпринять что-нибудь для того, чтобы сократить количество этих событий, например, избавиться от частого осуществления приложениями входа и выхода.

Анализ производительности системы

Для анализа производительности системы можно пользоваться различными инструментами, которые предлагаются в составе самой операционной системы. Вдобавок для просмотра связанных с производительностью характеристик системы можно применять и новое динамическое представление V$OSSTAT, которое отображает статистические показатели занятости операционной системы на уровне сотых секунды (busy ticks).

Главные заслуживающие внимания столбцы в этом представлении описаны ниже.

NUM_CPUS. В этом столбце отображается количество процессоров.

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

BUSY_TICKS. В этом столбце отображается количество сотых секунды, на протяжении которых все процессоры были заняты выполнением кода.

USER_TICKS. В этом столбце отображается количество сотых секунды, на протяжении которых все процессоры были заняты выполнением пользовательского кода.

SYS_TICKS. В этом столбце отображается количество сотых секунды, на протяжении которых все процессоры были заняты выполнением кода ядра.

IOWAIT_TICKS. В этом столбце отображается количество сотых секунды, на протяжении которых все процессоры ожидали завершения ввода-вывода.

В столбцах AVG_IDLE_WAITS, AVG_BUSY_TICKS, AVG_USER_TICKS, AVG_SYS_TICKS и AVG_IOWAIT_TICKS отображаются соответствующие средние показатели по всем процессорам. Ниже приведен простой пример просмотра статистических данных по использованию системы, которые содержатся в представлении V$OSSTAT:

SQL> SELECT * FROM V$OSSTAT;
STAT_NAME                     VALUE  OSSTAT_ID
-----------------------  ----------  ---------
NUM_CPUS                         16          0
IDLE_TICKS                    17812          1
BUSY_TICKS               2686882247          2
USER_TICKS               1936724603          3
SYS_TICKS                 750157644          4
IOWAIT_TICKS             1933617293          5
AVG_IDLE_TICKS            545952047          7
AVG_BUSY_TICKS            167700614          8
AVG_USER_TICKS            120815895          9
AVG_SYS_TICKS              46655696         10
AVG_IOWAIT_TICKS          120621649         11
OS_CPU_WAIT_TIME         5.3432E+13         13
RSRC_MGR_CPU_WAIT_TIME            0         14
IN_BYTES                 6.2794E+10       1000
OUT_BYTES                         0       1001
AVG_IN_BYTES             1.7294E+19       1004
AVG_OUT_BYTES                     0       1005
17 rows selected.
SQL>

Анализ приложения

Специалисты полагаются на статистические данные либо по коэффициентам попаданий, либо по событиям ожидания, или на те и другие вместе, но бывают такие ситуации, когда оба вида показателей могут подводить. Например, предположим, что все коэффициенты попаданий составляют около 99% и что статистические данные по событиям ожидания не показывают наличия ни серьезного времени ожидания ресурсов, ни состязания за защелки. Означает ли это, что система работает оптимально? Да, она делает то, что ее попросили, чрезвычайно хорошо, но нет никакой гарантии, что SQL- код обрабатывает вещи эффективным образом. При выполнении запросом необычайно большого количества операций logical reads коэффициенты попаданий будут выглядеть замечательно. События ожидания тоже не будут показывать ничего особенного, поскольку они не охватывают время, затрачиваемое на фактическое использование ЦП. Однако времени ЦП как раз и будет тратиться очень много из-за выполнения запросом слишком большого количества операций logical reads.

Этот пример показывает, почему так важно не полагаться лишь на коэффициенты попаданий и статистику по событиям ожидания, а также внимательно изучать основных потребителей ресурсов в экземпляре. Нужно обязательно заглядывать в список Top Sessions (который можно сортировать в соответствии с различными критериями) и проверять, является ли большое потребление ресурсов представленными в нем сеансами оправданным.

Больше всего необходимо стараться не путать симптомы плохой производительности с ее причинами. В случае обнаружения высокого показателя по защелкам может возникать желание сразу же отрегулировать параметры инициализации, ведь, в конце то концов, разве Oracle не является легко конфигурируемой базой данных? Иногда одна лишь настройка параметров инициализации действительно помогает, но, возможно, прежде чем прибегать к ней, лучше остановиться и задать себе вопрос о том, а почему показатель по защелкам является настолько высоким. Скорее всего, причина такого высокого показателя заключается не в неправильной настройке конкретного параметра, а в наличии проблем в коде приложения. Аналогичным образом, причиной нехватки ресурсов ЦП в системе могут служить не медленные или неадекватные ЦП, а опять-таки приложение, выполняющее слишком много ненужных операций ввода-вывода пусть даже в кэше буферов базы данных, а не на диске.

При изучении данных по событиям ожидания необходимо понимать, что пытаться устранить все эти события не нужно, потому что такого никогда не произойдет. Следует не обращать внимания на неважные, рутинные и неизбежные события ожидания. Как рассказывалось в предыдущем разделе, события ожидания вроде SQL*Net message from client отражают ожидания, происходящие за пределами базы данных, потому относить их на счет плохо работающей базы данных не нужно. Лучше фокусироваться на общем времени ожидания, чем на количестве событий ожидания, появляющихся в таблицах производительности и отчетах AWR. Также, если события ожидания составляют лишь незначительную часть времени отклика, беспокоиться о них нет никакого смысла. Как бы сказал Эйнштейн, важность событий ожидания является относительной — относительной по сравнению с общим временем отклика и общим временем выполнения ЦП.

Недавно прошла большая волна публикаций по достоинствам подхода настройки производительности на основе анализа событий ожидания (также называемого подходом с использованием интерфейса событий ожидания). Коэффициенты попаданий в кэш и другие подобные показатели всегда можно применять для получения общего представления о том, как система использует память и другие ресурсы Oracle, но анализ событий ожидания все равно является более эффективным вариантом в том, что касается улучшения производительности. Решение проблем с событиями ожидания также позволяет решать и проблемы с традиционными коэффициентами попаданий. Например, при желании устранить проблему, связанную с большим количеством событий ожидания free buffer waits, может быть достаточно увеличить размер кэша буферов. Аналогично, при наличии проблем с событиями ожидания latch free, одним из решений будет проверить, не нужно ли выделить больше памяти разделяемому пулу. Устранить проблему, связанную с высоким показателем по количеству событий ожидания direct path read получится просто за счет увеличения значения параметра PGA_AGGREGATE_TARGET.

Анализ показателя по времени отклика SQL с помощью Database Control

Для быстрого получения представления о том, сколько в текущий момент составляет показатель по времени отклика SQL по сравнению с нормальным “опорным” показателем по времени отклика SQL, можно использовать OEM Database Control. Интерфейс Database Control вычисляет процент времени отклика SQL путем деления значения опорного показателя по времени отклика на значение текущего показателя по времени отклика SQL (оба исчисляются в микросекундах). Если этот процент оказывается выше 100, значит, экземпляр обрабатывает SQL-операторы медленнее того, как должно быть согласно показателям базовой линии. Если этот процент оказывается приблизительно равным 100, значит, текущий и опорный показатели по времени отклика совпадают и, следовательно, экземпляр обрабатывает SQL-операторы нормально. Раздел SQL Response Time (Время отклика SQL) находится в правой части домашней страницы Database Control.

Использование ADDM для анализа проблем с производительностью

Вне всяких сомнений, новый инструмент ADDM должен играть ключевую роль во всех связанных с настройкой производительности усилиях. В блогах уже упомяналось, как вручную получать отчет ADDM и применять OEM Database Control для просмотра результатов анализа ADDM. Сведения и рекомендации советника ADDM очень помогают отлаживать производительность базы данных. В листинге 20.18 для примера приведена часть отчета ADDM (полученного в результате выполнения сценария addmrpt.sql, находящегося в каталоге $ORACLE_HOME/rdbms/admin).

DETAILED ADDM REPORT FOR TASK 'TASK_1493' WITH ID 1493
-------------------------------------------------------------------------
Analysis Period: 22-JUL-2008 from 07:01:02 to 17:00:36
Database ID/Instance: 877170026/1
Database/Instance Names: NINA/nina
Host Name: finance1
Database Version: 10.2.0.0
Snapshot Range: from 930 to 940
Database Time: 801313 seconds
Average Database Load: 22.3 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 24% impact (193288 seconds)
--------------------------------------
The buffer cache was undersized causing significant additional read I/O.
RECOMMENDATION 1: DB Configuration, 24% benefit (193288 seconds)
ACTION: Increase SGA target size by increasing the value of parameter
"sga_target" by 1232 M.
SYMPTOMS THAT LED TO THE FINDING:
Wait class "User I/O" was consuming significant database time. (54%
impact [436541 seconds])
FINDING 2: 19% impact (150807 seconds)
--------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 4.4% benefit (34936 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"b3bkjk3ybcp5p".
RELEVANT OBJECT: SQL statement with SQL_ID b3bkjk3ybcp5p and
PLAN_HASH 954860671
. . .

Иногда ADDM может рекомендовать запускать для некоторых сегментов утилиту Segment Advisor, а для некоторых SQL-операторов — утилиту Automatic SQL Advisor.

Использование отчетов AWR для отдельных SQL-операторов

Отчеты AWR являются замечательным источником информации по показателям, связанным с событиями ожидания и другими характеристиками производительности экземпляра. Однако AWR еще также можно применять и для получения отчетов, отображающих статистические данные по производительности только одного SQL-оператора за период, охватываемый определенными снимками. В листинге 20.19 показан пример получения отчета AWR по конкретному SQL-оператору.

На заметку! Сценарий awrsqrpt.sql, похоже, выполняется медленнее сценария awrrpt.sql, который генерирует отчет AWR по всему экземпляру.

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
Current Instance
Текущий экземпляр
~~~~~~~~~~~~~~~~
   DB Id   DB Name      Inst Num     Instance
---------- ------------ -------- ------------
877170026  PASPROD        1           pasprod
Specify the Report Type
Указание типа отчета
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
В каком формате требуется получить отчет — в формате HTML и простого текста?
Enter 'html' for an HTML report, or 'text' for plain text
Введите 'html' для выбора формата HTML или 'text' для выбора формата простого текста
Defaults to 'html'
По умолчанию выбирается вариант 'html'
Enter value for report_type: text
Введите значение для типа_отчета:
Type Specified: text
Указан тип: text
Instances in this Workload Repository schema
Экземпляры в данной схеме репозитория рабочей нагрузки
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     Inst Num      DB Name     Instance         Host
------------ -------- ------------ ------------ ------------
* 877170026         1 PASPROD           pasprod        prod1
Using 877170026 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
Указание количества дней, когда создавались подлежащие выбору снимки
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing  without
specifying a number lists all completed snapshots.
Ввод количества дней (n) приведет к перечислению самых последних дней, когда
создавались снимки. Нажатие клавиши  без указания количества дней
приведет к отображению списка всех сделанных снимков
Enter value for num_days: 3
Введите значение для количества_дней: 3
Listing the last 3 days of Completed Snapshots
Перечисление 3 последних дней, когда были сделаны снимки
   Instance  DB Name      Snap Id  Snap  Started            Level
----------- ------------ --------- ----- ----------------- ------
    pasprod  PASPROD             1  3829 23 Apr 2008 00:01      1
                                    3830 23 Apr 2008 02:00      1
                                    3832 23 Apr 2008 03:00      1
                                    3833 23 Apr 2008 04:00      1
                                    3834 23 Apr 2008 05:00      1
                                    3835 23 Apr 2008 06:00      1
Specify the Begin and End Snapshot Ids
Указание идентификаторов начального и конечного снимка
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3830
Введите значение для начального_снимка: 3830
Begin Snapshot Id specified: 3830
Указан начальный снимок с идентификатором: 3830
Enter value for end_snap: 3835
Введите значение для конечного_снимка: 3835
End Snapshot Id specified: 3835
Указан конечный снимок с идентификатором 3835
Specify the Report Name
Указание имени для отчета
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is 1_3830_3835. To use this name,
press  to continue, otherwise enter an alternative.
По умолчанию для файла отчета предлагается имя 1_3830_3835. При желании оставить
этом имя, нажмите клавишу  для продолжения, в противном случае введите
другое имя.
Enter value for report_name
Введите значение для имени_отчета
Using the report name 1_3830_3835
Указано использовать для отчета имя 1_3830_3835
Specify the SQL Id
Указание идентификатора SQL-оператора
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 9a64dvpzyrzza:
Введите значение для идентификатора_sql: 9a64dvpzyrzza:

Управление памятью операционной системы

Для выяснения того, хватает ли в системе свободной памяти, можно применять утилиту vmstat. Если в системе происходит страничный обмен (paging) и подкачка (swapping), производительность базы данных будет ухудшаться, поэтому нужно анализировать причины выполнения этих операций. Если интенсивное потребление памяти связано с каким-нибудь не принадлежащим Oracle процессом, может быть лучше перенести выполнение этого процесса на время меньшей загруженности системы, а также рассмотреть вариант увеличения объема общей доступной операционной системе памяти. Для осуществления мониторинга за виртуальной памятью в системе UNIX удобно применять команду vmstat, а для анализа потребления ресурсов ЦП и памяти в системе — утилиту top.

Анализ активности недавних сеансов с помощью отчета ASH

В представлении V$ACTIVE_SESSION_HISTORY фиксируется информация об активных сеансах за счет осуществления ежесекундной выборки. Данные в столбцах представления V$ACTIVE_SESSION_HISTORY похожи на те, что содержатся в представлении V$SESSION, но включают в себя только данные, которые присутствовали в активных сеансах на момент выборки. На момент выборки сеанс мог как использовать ресурсы ЦП, так и ожидать завершения какого-то события, не являющегося частью класса событий ожидания типа idle. При создании AWR своего снимка данные в представлении V$ACTIVE_SESSION_HISTORY сбрасываются на диск в виде части данных снимка AWR. Однако после сброса они навсегда не утрачиваются. Есть еще одно представление — DBA_HIST_ACTIVE_SESS_HISTORY, — которое хранит снимки данных представления V$ACTIVE_SESSION_HISTORY.

Применять любое из этих двух представлений для анализа хронологии сеансов вовсе не обязательно. Можно просто сгенерировать отчет ASH, содержащий как текущие данные активных сеансов из представления V$ACTIVE_SESSION_HISTORY, так и хронологические — из представления DBA_HIST_ACTIVE_SESS_HISTORY. В отчете ASH приводится SQL-идентификатор SQL-операторов, информация об объектах, информация о сеансах и информация о соответствующих событиях ожидания.

Получать отчет ASH можно либо через интерфейс OEM Database Control, либо путем выполнения соответствующего сценария, поставляемого Oracle. В Oracle доступны два связанных с ASH сценария:

сценарий ashrpt.sql, который генерирует отчет ASH за указываемый период времени по принятой по умолчанию базе данных;
сценарий ashrpti.sql, который генерирует точно такой же отчет, как и ashrpt.sql, но позволяет указывать желаемый экземпляр базы данных.

По сути, сценарий ashrpt.sql по умолчанию использует в качестве идентификатора DBID и номера экземпляра значения текущего экземпляра и просто запускает сценарий ashrpti.sql. Оба этих сценария находятся в каталоге $ORACLE_HOME/rdbms/admin. Команда, которую нужно ввести для генерации отчета ASH по экземпляру, выглядит так:

SQL> @ORACLE_HOME/rdbms/admin/ashrpt.sql

После выполнения этой команды можно просматривать отчет ASH, который помещается в тот же каталог, из которого запускался сценарий ashrpt.sql.

Зависание базы данных

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

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

Застревание процесса архивирования

В случае заполнения целевого каталога архивных журналов и отсутствия в нем места для архивирования следующих журналов повторного выполнения, происходит так называемое застревание (stuck) процесса архивирования. В таком случае база не лишь замедляет свою работу, а тотчас же зависает. Как уже известно, в режиме архивирования журналов база данных просто отказывается перезаписывать файлы журналов повторного выполнения до тех пор, пока они не будут успешно заархивированы. Именно поэтому она и начинает зависать при заполнении каталога архивных журналов и остается в таком состоянии до тех пор, пока администратор вручную не удалит из этого каталога какие-нибудь архивные журналы.

Процесс архивирования

Процесс архивирования (archiver process — ARCH) отвечает за архивирование заполненных журналов повторного выполнения. Для выяснения того, не появились ли какие-то заполненные журналы повторного выполнения, подлежащие архивированию, он считывает содержимое управляющих файлов и затем проверяет заголовки и блоки журналов повторного выполнения для оценки их действительности перед архивированием. Связанные с архивированием проблемы могут появляться, если работа ведется в режиме архивирования журналов, а процесс архивирования по какой-то причине не работает. В таком случае понадобится запустить процесс архивирования посредством следующей команды:

SQL> ALTER SYSTEM ARCHIVE LOG START;

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

Застревание архивирования

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

В журнале предупреждений появляются сгенерированные Oracle сообщения об ошибках, информирующие о том, что процесс архивирования застрял из-за нехватки места на диске. Еще можно выполнить запрос к представлению V$ARCHIVE, в котором содержится информация обо всех журналах повторного выполнения, нуждающихся в архивировании. Если количество этих журналов велико и быстро растет, значит, процесс архивирования застрял, и его необходимо вручную очистить. В листинге 20.20 показаны сообщения об ошибках, которые будут появляться при застревании процесса архивирования.

$ sqlplus system/system_passwd
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
ошибка в процессе архивирования. Возможны только внутренние
подключения до тех пор, пока проблема не будет устранена
$
$ oerr ora 257
00257, 00000, "archiver error. Connect internal only, until freed."
"ошибка в процессе архивирования. Возможны только внутренние
подключения до тех пор, пока проблема не будет устранена."
//*Cause: The archiver process received an error while trying to
// archive a redo log. If the problem is not resolved soon, the
// database will stop executing transactions. The most likely cause
// of this message is the destination device is out of space to
// store the redo log file.
// *Action: Check archiver trace file for a detailed description
// of the problem. Also verify that the device specified in the
// initialization parameter ARCHIVE_LOG_DEST is set up properly for
// archiving.
//*Причина: Процесс архивирования получил ошибки при попытке заархивировать
// журнал повторного выполнения. Если проблема не будет быстро устранена, база
// данных перестанет выполнять транзакции. Скорее всего, это сообщение появилось
// из-за того, что на целевом устройстве не хватило места для сохранения файла
// журнала повторного выполнения.
// *Действие: Загляните в файл трассировочных данных процесса архивирования для
// получения детального описания проблемы. Также удостоверьтесь в том, что
// устройство, указанное в параметре инициализации ARCHIVE_LOG_DEST, настроено
// надлежащим для выполнения архивации образом.
$

В подобных обстоятельствах необходимо предпринять одно из описанных ниже действий.

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

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

Наличие в журнале предупреждений слишком большого количества сообщений checkpoint not complete (не завершено создание контрольной точки) будет свидетельствовать о том, что причиной проблемы является не процесс архивирования, а журналы повторного выполнения, из-за того, что им не удается справляться с большим количеством обновлений. В таком случае можно попробовать облегчить проблему, увеличив размер журналов повторного выполнения в оперативном режиме.

На заметку! База данных фиксирует все подключения от имени SYS в журнале аудита по умолчанию, который обычно находится в каталоге $ORACLE_HOME/rdbms/audit. При наличии неадекватного количества места в этом каталоге, он может со временем заполняться и приводить к появлению ошибок при попытке подключения от имени SYS. В случае возникновения подобной проблемы следует либо удалить старые файлы журналов аудита, либо выбрать для них другое место.

Проблемы с использованием системы

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

Нужно удостовериться в том, что система не страдает от серьезной проблемы со страничным обменом и подкачкой, которая может приводить к замедлению работы базы данных.
Нужно с помощью top, sar, vmstat или других подобных утилит уровня операционной системы проверить, каким образом используются ресурсы. Большие запросы, операции сортировки и операции по управлению пространством могут приводить к увеличению использования ресурсов ЦП.
Быстро разрастающиеся процессы и чрезмерные процессы создания снимков (snapshot processes — SNP) могут отнимать чрезмерное количество ресурсов ЦП. Поэтому нужно следить за любые процессами репликации (создания снимков) и процессами DBMS_JOB, поскольку те и другие предполагают использование ресурсоемких процессов SNP. В случае всплесков в использовании ЦП нужно проверять, не выполняются ли в базе данных какие-то неожиданные задания. Даже если в текущий момент в базе данных не выполняется никаких заданий, процессы SNP все равно могут потреблять приличную часть ресурсов ЦП, поскольку им приходится постоянно запрашивать очередь заданий.
Большое количество очередей выполнения свидетельствует о том, что системе не хватает ресурсов ЦП, из-за чего процессам приходится ожидать, пока какой-нибудь процессор станет доступным.
Если показатель по дисковому вводу-выводу близок или равен 100%, и несколько самых ресурсоемких сеансов пользователей уже было уничтожено, возможно, проблема связана с контроллером дисков. Например, загруженный на 100% дисковый пакет может использовать контроллер, сконфигурированный на 16 битов, а не на 32, как все остальные контроллеры, и тем самым вызывать серьезное замедление в работе подсистемы ввода-вывода.

Чрезмерное состязание за ресурсы

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

SQL> SELECT event, count(*)
2 from v$session_wait
3 group by event;
EVENT                         COUNT(*)
----------------------------  --------
PL/SQL lock timer                    2
Queue Monitor Wait                   1
SQL*Net message from client         61
SQL*Net message to client            1
jobq slave wait                      1
pmon timer                           1
rdbms ipc message                   11
smon timer                           1
wakeup time manager                  1
9 rows selected.
SQL>

Вывод показанного выше запроса не раскрывает никаких серьезных состязаний за ресурсы: все ожидания связаны с событиями простоя.

В случае выполнения в базе данных чрезвычайно большого количества обновлений, состязание за ресурсы типа сегментов отката или защелок потенциально может служить основной причиной замедления работы по всей базе данных и иногда даже создавать впечатление, что база данных вообще зависла. В начале этой статьи уже рассказывалось о том, как анализировать базу данных на предмет наличия проблем, связанных с состязанием за ресурсы и ожиданиями, с помощь представления V$SESSION_WAIT и вывода AWR. На серверах Windows выявлять потенциально высокие показатели по потреблению ресурсов можно также с применением утилит Performance Monitor (Системный монитор) и Event Monitor (Монитор событий).

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

Проблемы с блокировкой

В случае блокирования важной таблицы или таблиц без вашего ведома, работа базы данных может значительно замедлиться. Например, возьмем команду вроде SELECT * FROM persons, в которой persons — самая большая таблица в базе данных, участвующая практически во всех SQL-операторах. Если неизвестно, какие таблицы (если вообще какие-либо) могут быть заблокированы, для выявления того, какая таблица или индекс блокируется и тем самым вызывает замедление в работе базы данных, можно применить следующий запрос:

SQL> SELECT l.object_id, l.session_id,
2 l.oracle_username, l.locked_mode,
3 o.object_name
4 FROM V$LOCKED_OBJECT l,
5 DBA_OBJECTS o
6* WHERE o.object_id=l.object_id;
OBJECT_ID  SESSION_ID  ORACLE_USERNAME  LOCKED_MODE  OBJECT_NAME
---------  ----------  ---------------  -----------  -----------
6699       22          NICHOLAS         6              EMPLOYEES
SQL>

Вывод приведенного выше запроса показывает, что пользователь NICHOLAS заблокировал таблицу EMPLOYEES. Если это мешает другим пользователям получать доступ к данной таблице, такую блокировку необходимо быстро удалить, уничтожив сеанс пользователя, который ее создал. Идентификатор сеанса причиняющего блокировку пользователя можно взять из столбца session_id предыдущего вывода, а идущий с ним номер SERIAL# — из представления V$SESSION. Далее останется только выдать команду ALTER SYSTEM KILL... и уничтожить вызывающий проблему сеанс. Точно так же следует поступать и в случае блокировки какого-нибудь индекса, лишающей пользователей возможности получать доступ к базовой таблице. Например, попытка создать индекс или перестроить его, в то время как пользователи получают доступ к лежащей в его основе таблицы, запросто может завершиться случайной блокировкой этой таблицы.

Наличие повреждений в таблице или индексе тоже может вызывать проблемы с получением доступа к этому объекту (или объектам). Быстро проводить проверку на предмет наличия повреждений можно с помощью следующего оператора:

SQL> ANALYZE TABLE employees VALIDATE STRUCTURE CASCADE;
Table analyzed.
SQL>

Ненормальное увеличение размера процессов

Иногда проблема может возникать из-за чрезмерного увеличения размера одного или нескольких процессов Oracle. При определении размера процессов Oracle следует проявлять осторожность, поскольку традиционные инструменты UNIX могут давать неправильное представление об этих размерах. В следующих подразделах объясняется, как точно замерять объем используемой процессом Oracle памяти.

Что находится внутри процесса Oracle

Процесс Oracle в памяти включает в себя несколько компонентов, которые описаны ниже.

Разделяемая память (shared memory). Этот компонент представляет собой уже столь хорошо знакомую область SGA.
Исполняемый модуль (executable). Этот компонент, также называемый компонентом TEXT, состоит из машинных команд. Его страницы в памяти помечаются как
доступные только для чтения.
Частные данные (private data). Этот компонент, также называемый компонентом DATA или кучей, состоит из PGA и UGA (User Global Area — глобальная область пользователей). Его страницы являются доступными для записи и не разделяются между процессами.
Разделяемые библиотеки (shared libraries). Эти компоненты могут быть частными или общедоступными.

При запуске нового процесса он требует выделения в памяти места только под компонент DATA (куча). Oracle использует UNIX-реализацию разделяемой памяти. Компоненты SGA и TEXT являются видимыми и доступными для совместного использования всем процессам Oracle и не входят в стоимость создания новых процессов Oracle. Если, например, 1000 пользователей использует Oracle Forms, для выполняемого модуля Forms требуется только один набор страниц TEXT.

К сожалению, утилиты, поставляемые в составе операционной системы, наподобие ps и top, по большей части дают неправильное представление о размере процессов, поскольку они включают в размер отдельных процессов и размер общих разделяемых страниц TEXT. Иногда они могут даже включать в него и размер SGA. Утилита pmap от Solaris и утилита glance от HP работают в этом отношении лучше, поскольку предоставляют более точную картину по использованию памяти на уровне процессов.

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

Определение объема используемой процессами памяти

Из-за проблем, описанных в предыдущем разделе, для получения правдивой информации об используемой процессами памяти лучше полагаться на Oracle. Например, при желании узнать, сколько всего памяти занимает компонент DATA, можно применить следующий запрос:

SQL> SELECT value, n.name|| '('||s.statistic#||')', sid
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name like '%ga memory%'
ORDER BY value;

Если необходимо узнать, сколько всего памяти выделяется вместе для PGA и UGA, можно воспользоваться командой, показанной в следующем примере. Вывод показывает, что для этих процессов вместе выделяется более 367 Мбайт памяти. Обратите внимание на то, что эта память выделяется вдобавок к той, что выделена для SGA, поэтому необходимо обязательно брать в расчет оба эти типа памяти во избежание проблем со страничным обменом и подкачкой.

SQL> SELECT SUM(value)
FROM V$SESSSTAT s, V$STATNAME n
WHERE s.statistic# = n.statistic#
AND n.name like '%ga memory%';
SUM(VALUE)
---------------
3674019536
1 row selected.
SQL>

Если запрос показывает, что общий объем используемой сеансом памяти со временем ненормально увеличивается, возможно, имеет место проблема типа утечки памяти. Признаком утечки памяти является использование Oracle памяти в объеме, значительно превышающем тот, что был выделен в параметрах инициализации. В таком случае процессам Oracle не удается возвращать память операционной системе. Если процессы продолжают расти в размере, со временем они могут достигать каких-то системных пределов по памяти и прекращать выполнение выдачей ошибки 4030:

$ oerr ora 4030
04030, 00000, "out of process memory when trying to allocate %s bytes (%s,%s)"
"не хватило памяти при попытке выделить % байт"
// *Cause: Operating system process private memory has been exhausted
// *Причина: частная память процессов операционной системы исчерпана
$

Обратите внимание, что для устранения проблемы с утечкой памяти служба технической поддержки Oracle может попросить подготовить дамп кучи (heap dump) всех неисправных процессов Oracle (утилитой oradebug).

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

Задержки из-за проблем с разделяемым пулом

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

Проблемы из-за негодных статистических данных

Как уже известно, используемому в Oracle оптимизатору по стоимости ( Oracle Cost- Based Optimizer — CBO) нужны актуальные статистические данные для того, чтобы он мог выбирать наиболее эффективный метод обработки запросов. В случае применения функции автоматического сбора статистических данных для оптимизатора (Automatic Optimizer Statistics Collection), Oracle будет поддерживать статистические данные в актуальном состоянии безо всяких усилий со стороны администратора баз данных. В случае отключения этой функции, однако, не исключена вероятность неполучения CBO репрезентативных статистических данных.

Если не собирать статистические данных регулярно при частом выполнении вставки в таблицы новых данных, старые статистические данных будут быстро становиться неактуальными и приводить к ухудшению производительности критически важных SQL-запросов. Администраторам баз данных часто приходится укладываться в очень жесткие временные рамки и собирать статистические данные только по ночам или на выходных. Поэтому иногда у них может возникать желание указывать небольшой объем выборки при использовании пакета DBMS_STATS для сбора статистики. Это чревато получением ненадежных статистических данных, ведущих к замедлению процесса обработки запросов.

Сбор информации во время зависания базы данных

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

Быстро собирать определенную информацию во время зависания базы данных важно по двум причинам. Во-первых, это позволит предотвратить возникновение аналогичной проблемы в будущем или помочь специалисту из службы технической поддержки Oracle (или какой-то частной фирмы) диагностировать проблему с помощью своих специализированных средств и приобрести практически опыт по ее решению. Во-вторых, скорее всего, быстрое завершение работы и перезапуск базы данных наверняка устранит проблему (как бывает, например, в некоторых ситуациях с блокировкой), но такой подход является слишком мощным оружием, чтобы применять его при каждой подобной ситуации. Правильное диагностирование проблемы может позволить предотвратить ее появление в будущем путем принятия простых мер или помочь устранить ее, если она все-таки возникнет. В следующих разделах описано, что необходимо делать для сбора информации по медленно работающей или зависшей базе данных.

Использование страницы Hang Analysis в Database Control

При замедлении работы экземпляра в интерфейсе OEM Database Control можно просмотреть информацию обо всех сеансах в базе данных в цветовой кодировке. В частности, на странице Hang Analysis (Анализ причин зависания) в Database Control предоставляется следующая информация:

мгновенно заблокированные сеансы;
сеансы, долго пребывающие в состоянии ожидания;
сеансы, которые зависли.

На рис. 1 показано, как выглядит страница Hang Analysis в Database Control, получать доступ к которой можно со страницы Performance (Производительность), выполнив щелчок на ссылке Hang Analysis (Анализ причин зависания) в разделе Additional Monitoring Links (Дополнительные ссылки мониторинга).

Сбор сообщений об ошибках

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

Получение данных дампа состояния системы

Дамп состояния системы (system state dump) представляет собой трассировочный файл, который сохраняется в каталоге дампа пользователя. Специалист из Oracle (или частной фирмы) может проанализировать такие данные дампа и сообщить о том, что произошло в базе данных, когда она зависла. Например, если медленно проходят операции входа в систему, можно собрать данные дампа состояния системы на этом этапе; они могут выявить, что ожидание больше всего вызывает определенный тип защелки библиотечного кэша. Для получения дампа состояния системы (на уровне 10) служит такая команда:

SQL> ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10';
Session altered.
SQL>

Внимание! Компания Oracle настоятельно не рекомендует клиентам самостоятельно настраивать события. В некоторых случаях это может приводить к появлению даже еще более серьезных проблем. Перед тем, как настраивать любое событие, лучше связываться со службой технической поддержки Oracle. Например, известно, что событие 10235 способно вызывать сильные состязания за защелки.

Результирующий вывод можно отправить специалистам Oracle, чтобы они проанализировали его и сообщили, в чем дело. Обратите внимание, что это требует отправки в службу технической поддержки Oracle запроса на техническое обслуживание (Technical Assistance Request — TAR) через службу MetaLink (http://metalink.oracle.com). (Проблема зависания базы данных имеет высокую степень важности при ответе, поэтому аналитик должен отозваться в течение нескольких минут.) Специалисты из технической службы поддержки могут попросить предоставить дополнительную информацию, например, дамп ядра, а также запустить отладчик или другое диагностическое средство и переслать вывод через FTP.

Использование утилиты hanganalyze

Операции получения дампа состояния системы, хотя и являются полезными, имеют несколько недостатков, вроде того, что включают слишком большое количество несущественной информации и занимают очень много времени, что ведет к образованию в данных дампа некоторых несоответствий. Утилита hanganalyze новее и сложнее их. Она предоставляет информацию о том, каких ресурсов ожидает каждый сеанс и что блокирует доступ к этим ресурсам. Вдобавок она предоставляет график зависимостей между активными сеансами в базе данных. Она не предназначена служить заменой операциям получения дампа состояния системы; вместо этого она скорее помогает делать данные дампа systemstate более значимым. Опять-таки, ее лучше использовать после получения консультации у специалистов из службы технической поддержки Oracle. Ниже приведен пример типичной команды HANGANALYZE.

SQL> ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level 3';

Ожидания и реальная производительность

Несколько лет назад Национальная иммиграционная служба США (Immigration and Naturalization Service — INS) создала новую систему обработки информации по обмену студентами (Student and Exchange Visitor Information System — SEVIS) стоимостью 36 миллионов долларов, призванную стать заменой старых бумажных методов, которыми она пользовалась много лет для отслеживания иностранных студентов в образовательных заведениях США. Более чем 5400 школам, колледжам и университетам было указано использовать систему SEVIS для ввода необходимой информации о принятых студентах из других стран.

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

Отдельным сотрудникам в Виржинии удавалось вводить данные только по утрам, до того, как к системе подключались заведения с западного побережья. В дневное время работа системы замедлялась практически до нуля.
Из университета в Миннесоте поступали жалобы о “полном отсутствии возможности пользоваться” системой. Сотрудники заявляли о том, что “система была по-настоящему завалена пользователями, пытающимися к ней подключиться”. Еще они жаловались на “неимоверно медленную” работу системы. Представитель иммиграционной службы подтвердил, что система работала “вяловато” и из-за этого у школ возникали проблемы с ее использованием.
Из университета Северной Каролины тоже жаловались на ситуацию, заявляя, что если так будет продолжаться и дальше, то это превратится “в настоящий кошмар”, и что “проблемы из-за этого уже начались”.
Один сотрудник из колледжа в Мичигане вообще в отчаянии попросил “объяснить, что он делает не так, или он попросту уволится”.

После всех этих жалоб в иммиграционной службе поняли, что колледжи и университеты не уложатся в срок, и объявили о дополнительной отсрочке после упоминания о том, что “в систему были внесены обновления”, которые значительно улучшили ее производительность.

В основе этой системы SEVIS лежала база данных Oracle, которая работала просто ужасно медленно. Судя по всему, степень масштабируемости системы оказалась недостаточной. Ведь при подключении большого количества пользователей та практически останавливалась. Очевидно, что система не была сконфигурирована так, чтобы она могла справляться с большим количеством одновременных операций. Был ли, например, рассмотрен вариант применения разделяемого сервера? Как обстояло дело со статистическими данными событиям ожидания? Детали автору не известны, но зато точно известно, что база данных Oracle вполне способна удовлетворять требования такого приложения, как это. Настоящий пример был выбран для того, чтобы показать, что даже в случаях большого масштаба администраторам баз данных иногда все равно приходится терпеть унижение, когда баз данных не настроена должным образом и потому ее производительность не отвечает ожиданиям конечных пользователей.

Вас заинтересует / Intresting for you:

Создание БД Oracle 12c с макси... 4292 просмотров Андрей Васенин Mon, 20 Aug 2018, 13:43:20

Настройка памяти базы данных O... 19360 просмотров Stas Belkov Sat, 07 Jul 2018, 15:44:14

Настройка производительности э... 4257 просмотров Antoniy Mon, 29 Jan 2018, 17:37:48

Мониторинг Oracle через метрик... 5085 просмотров sepia Tue, 21 Nov 2017, 13:18:05

Измерение производительности экземпляра Oracle

Антон Меринов