Эти материалы являются объектом авторского права и защищены законами РФ и международными соглашениями о защите авторских прав. Перед использованием материалов вы обязаны принять условия лицензионного договора на использование этих материалов, или же вы не имеете права использовать настоящие материалы

Авторская площадка "Наши орбиты" состоит из ряда тематических подразделов, являющихся моими лабораторными дневниками, содержащими записи за разное, иногда продолжительно отличающееся, время. Эти материалы призваны рассказать о прошедшем опыте, они никого ни к чему не призывают и совершенно не обязательно могут быть применимы кем-то ещё. Это только лишь истории о прошлом

список рассматриваемых событий

• Событие ожидания db file async I/O submit
• Событие ожидания log file parallel write
• Событие ожидания control file parallel write
• Событие ожидания log_file_sync
• Событие ожидания os thread startup
• Событие ожидания Log archive I/O
• Событие ожидания SQL*Net message from dblink
• Событие ожидания log file switch completion
• Событие ожидания db file parallel read
• Событие ожидания log file sequential read
• Событие ожидания control file sequential read
• Событие ожидания enq: CR - block range reuse ckpt
• Событие ожидания SQL*Net more data to client
• Событие ожидания Disk file operations I/O
• Событие ожидания ADR block file read
• Событие ожидания SGA: allocation forcing component growth
• Событие ожидания latch: shared pool

события памяти SGA - детализация - кеш буферов

Событие ожидания buffer busy waits [описание готовилось для версии 9i] Это событие отражает конкуренцию за какой либо блок в одном из кэшей. Статистику по классам буферов (основные классы data block, segment header, undo header, and undo block) можно получить из V$WAITSTAT, а из V$SESSION_WAIT можно получить данные о файле (P1) и блоке в нем (параметр P2), по которым можно вычислить сегмент и его тип:

SELECT class, count FROM V$WAITSTAT WHERE count > 0 ORDER BY count DESC;
SELECT segment_owner, segment_name FROM DBA_EXTENTS
       WHERE file_id =  AND  BETWEEN block_id AND block_id + blocks - 1;

Реакция на событие зависит от класса блоков:

• заголовок сегмента (segment header) - скорее всего связан с утилизацией FREELIST. По возможности необходимо перевести управление сегментами в автоматический режим, что исключит необходимость установки параметров PCTUSED, FREELISTS, and FREELIST GROUPS. Freelist - список свободных (свободного места больше PCTFREE) блоков в экстентах которого сегмента, определяется модификатором параметров хранения FREELISTS, по умолчанию один. Методом решения является увеличение количества freelists, а при недостаточности - введение групп списков (даже для одного экземпляра, в RAC у каждого экземпляра должна быть своя группа). Текущие настройки для сегмента можно увидеть запросом:
  SELECT SEGMENT_NAME, FREELISTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = segment name AND SEGMENT_TYPE = segment type;
• блок данных (data block) - для таблицы или индекса необходимо исключить использование неселективных индексов, проверить нагруженные индексы (когда вставка инициируется многими процессами, например, последовательности), перейти на автоматическое управление сегментами или увеличить количество freelist
• заголовок отмены (undo header) - если не используется автоматическое управление пространством отмены, нужно увеличить количество сегментов отката
• блок отмены (undo block) - если не используется автоматическое управление пространством отмены, нужно увеличить размер сегментов отката

Событие ожидания free buffer waits [описание готовилось для версии 9i] Возникает, когда серверный процесс не может найти свободный буфер и запрашивает сброс процессом грязных (в которых были изменены данные) буферов на диск. Причины - медленный I/O, ожидание ресурсов (например, защелки), слишком маленький буфер - процесс DBRW не успевает обработать или же слишком большой - один DBRW не справляется

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

Если это записи, смотреть в V$FILESTAT и статистику ОС на предмет допустимости нагрузки и последующая оптимизация или расширение подсистемы ввода/вывода

Если малый кеш - коэффициент попадания будет низкий, а новый оптимальный размер можно подсмотреть в V$DB_CACHE_ADVICE. Если размер кеша адекватен, но проблема остается - можно перевести DBRW в асинхронный режим или активировать несколько DBRW параметром DB_WRITER_PROCESSES (количество сканеров LRU списка кеша, опция предпочтительна на SMP системах с асинхронным I/O) или параметром DBWR_IO_SLAVES (непосредственно дочки, осуществляющие ввод/вывод, допустимо при одном CPU или для эмуляции асинхронного I/O, который предпочтителен)

При отсутствии поддержки асинхронного I/O его необходимо отключить в движке DISK_ASYNCH_IO = false. Также нужно использовать только один из параметров DB_WRITER_PROCESSES и DBWR_IO_SLAVES

события памяти SGA - детализация - буфер оперативных журналов

Событие ожидания log buffer space [описание готовилось для версии 9i] Cобытие подразумевает ожидание серверным процессом свободного места в журнальном буфере, по причине наполнения буфера настолько быстрого, что LGWR не успевает записывать данные в redo log. Если подсистема воода/вывода для оперативных журналов адекватна, то необходимо увеличить размер журнального буффера до момента, пока событие станет редким или перестанет появляться, до этого выверять и тюнить подсистему I/O

ввод/вывод оперативных журналов

Событие ожидания log file switch (archiving needed) [описание готовилось для версии 9i] группа из двух событий - log file switch (archiving needed) и log file switch (checkpoint incomplete) - подразумевает, что LGRW не может переключиться на следующий журнал, и все COMMIT запросы ожидают этого переключения

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

Для события log file switch (archiving needed) необходимо выявить причину, по которой архивер не может записать архивную информацию в разумные сроки. Причины могут быть - нехватка места для архивного журнала, архивер не может читать из оперативного журнала достаточно быстро (связано с LGWR), архивер не может отписывать достаточно быстро (в этом случае нужно проверить пропускную способность целевого хранилища, потом - его заполненность, потом - увеличивать количество процессов архивирования ARCHn)

Событие ожидания log file switch (checkpoint incomplete) [описание готовилось для версии 9i] Группа из двух событий - log file switch (archiving needed) и log file switch (checkpoint incomplete) - подразумевает, что LGRW не может переключиться на следующий журнал, и все COMMIT запросы ожидают этого переключения

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

Важно помнить, что статус журнала "активный" устанавливается сразу после переключения на новый журнал, и снимается тогда, когда DBWRxx полностью сбросит грязные буфера, соответствующие контрольной точке, порождённой переключением журнала, в файлы БД

Также является симптомом недостаточного размера групп оперативных журналов, полезно проверить частоту переключения журналов. Рекомендованное вендором значение - одно переключение в 20 минут. Если со скоростью переключения всё в порядке, то это симптом медленных устройств физического ввода/вывода или неэффективной архитектуры физического ввода/вывода

Для события log file switch (checkpoint incomplete) необходимо выявить причины, по которым DBWR отрабатывает медленно - это или медленное хранилище, или неоптимальное распределение данных в хранилище. Если не DBWR - причиной может быть малое количество журнальных групп или малый размер оперативных журналов, когда цикл переключения оперативных журналов проходится быстрее, чем DBWR отрабатывает запись данных по контрольной точке - в этом случае нужно учеличить количество журнальных групп и/или размер групп соответственно

прямой ввод/вывод минуя SGA

Событие ожидания direct path read [описание готовилось для версии 9i] Насколько я понимаю, эти ожидания прямых чтений в обычном случае dedicated сервера - в первую очередь операции сортировки, соединений и т.п. Различные загрузки SQL Loader'ом и т.п. тоже будут относиться сюда же, но они есть далеко не всегда

события 'direct path read' и 'direct path read (lob)' характеризуются прямым чтением пользовательским процессом данных в PGA, минуя SGA. Если система не поддерживает асинхронный ввод/ывод, каждое новое ожидание соответствует новому запросу ввода/вывода. Иначе при каждом обращении к блоку, еще не прочитанному в буфер, возникает запрос на чтение и обновляется статистика событий ожидания. Однако, в отличие от scattered и sequental read, количество событий ожидания может быть не равно количеству запросов на чтение. Параметры V$SESSION_WAIT являются P1 - абсолютный номер файла, P2 - номер блока, P3 - количество блоков. Причины возникновения событий - сортировки не помещаются в памяти и уходят на диск (потом забираются пользователем через direct read), для сканирования используются параллельные дочки, серверный процесс обрабатывает буфера быстрее, чем подсистема ввода вывода возвращает буфера (сигнал о перегрузке I/O подсистемы). Это довольно ожидаемо для warehouse, но для DSS требует расследования

Для дисковых сортировок можно вычислить породившие сортировку запросы в V$TEMPSEG_USAGE, и размер сортировки в V$SESSTAT, далее можно уменьшить сортировку тюнингом SQL. Если WORKAREA_SIZE_POLICY = MANUAL, можно вручную увеличить SORT_AREA_SIZE, для автоматического режима нужно увеличивать PGA_AGGREGATE_TARGET. Для table full scan возможны искажения оптимизатора при большом коэффициенте параллелизма таблицы, необходимо проверить, что чтение производится корректно (?). Для запросов с hash join чрезмерный ввод/вывод возможен при малом размере HASH_AREA_SIZE, который имеет смысл увеличить (для автоматического режима нужно увеличивать PGA_AGGREGATE_TARGET

Событие ожидания direct path read (lob) [описание готовилось для версии 9i] Насколько я понимаю, эти ожидания прямых чтений в обычном случае dedicated сервера - в первую очередь операции сортировки, соединений и т.п. Различные загрузки SQL Loader'ом и т.п. тоже будут относиться сюда же, но они есть далеко не всегда

события 'direct path read' и 'direct path read (lob)' характеризуются прямым чтением пользовательским процессом данных в PGA, минуя SGA. Если система не поддерживает асинхронный ввод/ывод, каждое новое ожидание соответствует новому запросу ввода/вывода. Иначе при каждом обращении к блоку, еще не прочитанному в буфер, возникает запрос на чтение и обновляется статистика событий ожидания. Однако, в отличие от scattered и sequental read, количество событий ожидания может быть не равно количеству запросов на чтение. Параметры V$SESSION_WAIT являются P1 - абсолютный номер файла, P2 - номер блока, P3 - количество блоков. Причины возникновения событий - сортировки не помещаются в памяти и уходят на диск (потом забираются пользователем через direct read), для сканирования используются параллельные дочки, серверный процесс обрабатывает буфера быстрее, чем подсистема ввода вывода возвращает буфера (сигнал о перегрузке I/O подсистемы). Это довольно ожидаемо для warehouse, но для DSS требует расследования

Для дисковых сортировок можно вычислить породившие сортировку запросы в V$TEMPSEG_USAGE, и размер сортировки в V$SESSTAT, далее можно уменьшить сортировку тюнингом SQL. Если WORKAREA_SIZE_POLICY = MANUAL, можно вручную увеличить SORT_AREA_SIZE, для автоматического режима нужно увеличивать PGA_AGGREGATE_TARGET. Для table full scan возможны искажения оптимизатора при большом коэффициенте параллелизма таблицы, необходимо проверить, что чтение производится корректно (?). Для запросов с hash join чрезмерный ввод/вывод возможен при малом размере HASH_AREA_SIZE, который имеет смысл увеличить (для автоматического режима нужно увеличивать PGA_AGGREGATE_TARGET

Cобытие ожидания direct path write [описание готовилось для версии 9i] Отражает ожидания при прямой записи из PGA минуя DBWR (запись сортировок на диск, параллельные DML операции, прямые (direct-path) вставки и параллельные create table as select, а также некоторые LOB операции. Как и для direct path read при работе ФС в асинхронном режиме количество ожиданий не равняется количеству запросов на запись. Сессии ждут, когда все буфера PGA обработаны и нельзя продолжать выполнение до завершения операций ввода/вывода

Параметры V$SESSION_WAIT - P1 (файл для записи), P2 (стартовый блок для записи), P3 (количество записываемых блоков). Причины возникновения - сортировки не помещаются в память или для создания и наполнения объектов используются параллельные операции. Для больших сотрировок можно вычислить породившие сортировку запросы в V$TEMPSEG_USAGE, и размер сортировки в V$SESSTAT, далее можно уменьшить сортировку тюнингов SQL. Если WORKAREA_SIZE_POLICY = MANUAL, можно вручную увеличить SORT_AREA_SIZE, для автоматического режима нужно увеличивать PGA_AGGREGATE_TARGET

Для параллельных DML операций нужно проверить распределение данных по дискам и выверить разметку (sizing) ФС на соответствие коэффициенту паралеллизма

ввод/вывод из данных в SGA

Событие ожидания db file sequential read [описание готовилось для версии 9i] Относится к вводу/выводу и отражает ожидания последовательного чтения, характерные для доступа к данным по индексам. Вполне ожидаемое и штатное событие - система ждёт окончания операций ввода/вывода

Это событие подразумевает, что пользовательский процесс производит чтение в буффер, и в процессе ожидает I/O операций. От предыдущего отличается тем, что чтение в последовательную область и, обычно это одноблоковое чтение. Обычно это результат использования индексов, редко full scan усекается до одного блока

Параметрами события в V$SESSION_WAIT являются P1 - абсолютный номер файла, P2 - номер блока, P3 - количество блоков. На здоровой системе события вполне ожидаемы после ожиданий простоя (idle), однако появление db file sequential read свидетельствует о необходимости предпочтительного использования на больших warehouse операций полного сканирования таблиц в параллельном режиме

Событие ожидания db file scattered read [описание готовилось для версии 9i] Это событие "случайное чтение" отражает ввод/вывод при full-scan доступе к таблицам данных, и может быть вполне ожидаемым. Однако если длительность события велика, имеет смысл посмотреть в сторону оптимизации доступа к данным, например введением фильтров, созданием индексов и т.п.

Cобытие подразумевает, что пользовательский процесс производит чтение в буффер, и в процессе ожидает I/O операций. Обычно это чтение в несвязанные разделы буфера, обычно это чтение multiblock, оно может подразумевать fast-scan операции, а не только полное сканирование таблицы

Параметрами V$SESSION_WAIT являются P1 - абсолютный номер файла, P2 - номер блока, P3 - количество блоков

На здоровой системе вполне ожидаемы после ожиданий простоя (idle), однако появление ожиданий прямого чтения (direct read подразумевает full scan в параллельным запросом) и db file scattered read говорят о недостаточности доступа по индексам. Дополнительные симптомы - низкий коэффициент попадания в кэш и большое время на события ожидания для пользователей с проблемным временем отклика. Это ситуация чрезмерного ввода/вывода, для ее разрешения необходимо снизить I/O тюнингом запросов, конфигурированием workarea, добавлением дисков или разнесением данных на разные диски

Для начала необходимо пробовать тюнить SQL, для чего нужно найти запросы как по событиям ожидания сессий, как и по размеру физического ввода/вывода в V$SQLAREA. Причинами могут быть плохо оптимизированный SQL, неиспользование индексов, высокопараллельный запрос или недостаточно аккуратная статистика оптимизатора. Также возможно некорректное распределение денных по дискам, что выявляется аналитикой нагрузки на диски. Ниже примеры вычисления SQL и сегментов, порождающих событие:

SELECT s.sql_address, s.sql_hash_value FROM V$SESSION s, V$SESSION_WAIT w
       WHERE w.event LIKE 'db file%read' AND w.sid = s.sid ;
SELECT segment_owner, segment_name FROM DBA_EXTENTS
       WHERE file_id = &p1 AND &p2 between block_id AND block_id + blocks - 1 ;

очереди

Событие ожидания enqueue [описание готовилось для версии 9i] Блокируют серийный доступ к ресурсам БД. Это событие показывает, что сессия ожидает снятия блокировки, подвешенной другой сессией. Параметрами V$SESSION_WAIT являются P1 (тип блокировки, то же в 'V$LOCK.TYPE = chr(bitand(P1,-16777216) / 16777215) || chr(bitand(P1,16711680) / 65535))', для запроса блокировки вычисляется как request = mod(P1, 65536)), P2 (то же, что и V$LOCK.ID1), P3 (V$LOCK.ID2). Сессии, держащие (hold) блокировки, можно получить из представления V$LOCK, как и ожидающие (для этих поле REQUEST != 0)

# отразить ждущие освобождения блокировки
SELECT * FROM V$LOCK WHERE request > 0 ;
# отразить блокирующие, для которых есть ждущие
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode,request, type
       FROM V$LOCK
       WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
       ORDER BY id1, request;

Реакция зависит от типа блокировки

• ST enqueue - блокировка возникает только для управляемых словарем бабличних пространств при задержке автоматического выделения места. Для временных табличных пространств нужно убедиться в использовании TEMPFILES. Для динамически растущих сегментов оптимально перейти на LMT, при невозможности - увеличит размер next_extent для избегания постоянного выделения места, или же предварительно выделить место для сегмента командой ALTER TABLE ALLOCATE EXTENT
• HW enqueue - блокировка гарантирует последовательное выделение места за пределами HIGH WATER MARK сегмента. Параметры позволяют вычислить табличное пространство (V$SESSION_WAIT.P2 / V$LOCK.ID1) и заголовок увеличиваемого сегмента (V$SESSION_WAIT.P2 / V$LOCK.ID2). Решением является ручное довыделение места
• TM enqueue - эта стек DML блокировок, чаще всего событие ожидания возникает при отсутствии издекса для поля внешнего ключа, и создание индекса позволяет избегать такого события ожидания
• TX enqueue - эксклюзивная блокировка с начала первого изменения транзакции до операций COMMIT или ROLLBACK. Mode 6 возникает при ожидании строки, занятой другой сессией и разрешается командами COMMIT/ROLLBACK в удерживаеющей сессии. Mode 4 есть ожидание ITL block, возникает при попытке заблокировать строку в блоке, в котором находятся другие заблокированные строки и свободных транзакционных слотов нет, решается увеличением количества транзакционных слотов (ITL, параметры INITTRANS или MAXTRANS для таблицы). Также возникает при ожидании сессией возможных задваиваний значения в уникальном индексе, когда при попытке записи одного значения двумя сессиями вторая ждет, возбуждать или не ORA-0001, решается COMMIT/ROLLBACK первой сессии. Также возникает пр ожидании фрагмента bitmap индекса, когда несколько сессий меняют строки, покрываемые одним фрагментом bitmap индекса, решение то же - COMMIT/ROLLBACK в первой сессии. Также возникает при ожидании PREPARED транзакции

защёлки

Событие ожидания latch free [описание готовилось для версии 9i] Защёлки являются низкоуровневыми внутренними блокировками для защиты структур памяти, аналогичными блокировкам на прикладном уровне. Использование защёлки обычно идёт по пути - попытка вбить костыль (pins), при удаче - 'взведение' защёлки (latch), а при неудаче - ожидание. Счетчик события обновляется, когда при попытке получения защелки (latch) она не отдаёся с первого раза. Обращать внимание стоит, если время ожидания по событию составляет большую часть общего времени ожидания для системы или пользователя

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

Параметры V$SESSION_WAIT - P1 (адрес защёлки), P2 (номер защелки), P3 (время ожидания процессом защёлки)

# текущие ожидания защёлок
SELECT n.name, SUM(w.p3) Sleeps FROM V$SESSION_WAIT w, V$LATCHNAME n
       WHERE w.event = 'latch free' AND w.p2 = n.latch# GROUP BY n.name ;

Обычно проблемы с защелками shared pool или библиотечного кэша связаны с парсингом

# сравнить вручную
SELECT sql_text FROM V$SQLAREA WHERE executions < 4 ORDER BY sql_text;
      
# найти отличающиеся литералами полуавтоматом
SELECT SUBSTR(sql_text,1, 60), COUNT(*) FROM V$SQLAREA
       WHERE executions < 4
       GROUP BY SUBSTR(sql_text, 1, 60)
       HAVING COUNT(*) > 1;
                                   
# поиск постоянно перепарсиваемых для тюнинга
# когда PARSE_CALLS стремится к EXECUTIONS
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
       FROM V$SQLAREA
       ORDER BY PARSE_CALLS ;
                                             
# поиск сессий с постоянным перепарсиванием
SELECT pa.sid, pa.value "Hard Parses", ex.value "Execute Count"
       FROM v$sesstat pa, v$sesstat ex
       WHERE pa.sid=ex.sid 
             AND pa.statistic# = (select statistic# FROM v$statname
                                         where name='parse count (hard)')
             AND ex.statistic# = (select statistic# FROM v$statname
                                         where name='execute count')
             AND pa.value > 0 ;

Белонин С.С. (С), май 2007 года