Медленный запрос: углублённая диагностика

Медленный запрос или вся система. Сужайте поиск шаг за шагом: выполняйте проверку в каждом узле и выбирайте ветку по результату. В конце получаете первопричину + исправление.

Что медленно?

Медленный один или несколько конкретных запросов или вся система? Это разделяет расследование на одно из двух направлений: 'запрос' или 'ресурсы/узкое место'.

• Медленные один или несколько конкретных запросов
• Медленная вся система

Выполняется ли запрос прямо сейчас?

Это запрос, который выполняется/завис прямо сейчас, или 'в целом медленный'? Посмотрите на активные сессии.

SELECT sid, serial#, sql_id, last_call_et AS running_sec, event, blocking_session
FROM v$session WHERE status='ACTIVE' AND type='USER' ORDER BY last_call_et DESC;

• Выполняется / завис прямо сейчас
• В целом медленный; хочу посмотреть его план

Ждёт ли запрос чего-то?

Если он активен, но не продвигается, скорее всего, он ждёт. Заполнены ли blocking_session / wait_event?

SELECT sid, blocking_session, event, seconds_in_wait
FROM v$session WHERE sid=&target_sid;

• Заблокирован другой сессией
• Ждёт ресурс (IO / журнал / latch)
• Ожидания нет; активно расходует CPU

Какое ожидание преобладает?

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

SELECT event, total_waits, time_waited FROM v$session_event WHERE sid=&target_sid ORDER BY time_waited DESC FETCH FIRST 5 ROWS ONLY;

• Ожидание IO (db file read / PAGEIOLATCH)
• Ожидание журнала / commit (log file sync / WRITELOG)
• Блокировка / enqueue (enq: TX / LCK)

Получите план выполнения (EXPLAIN)

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

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

• Полное сканирование / нет индекса
• Вчера был быстрым; план внезапно изменился
• Обрабатывает слишком много строк / N+1
• План выглядит хорошо, но медленно

Актуальна ли статистика?

Большой разрыв между оценочным и фактическим числом строк (E-rows vs A-rows) — признак устаревшей статистики.

SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name='&TABLE_NAME';

• Статистика устарела
• Статистика актуальна, но всё равно медленно

Вся система: где узкое место?

Если медленно всё, а не один запрос, ищите узкое место по ресурсам: CPU, IO, память или массовые блокировки?

SELECT metric_name, value FROM v$sysmetric WHERE metric_name IN ('Host CPU Utilization (%)','Average Active Sessions') AND intsize_csec=(SELECT MAX(intsize_csec) FROM v$sysmetric);

• CPU близок к 100%
• Высокое ожидание IO
• Давление на память / низкий процент попаданий в кэш
• Много блокировок

Первопричина: блокировки

Запрос ждёт блокировку, удерживаемую другой сессией. Найдите сессию во главе цепочки (корневой блокировщик) и устраните её.

Первопричина: узкое место IO

Запрос много читает с диска. Обычно из-за полных сканирований (нет индекса) или нехватки памяти (буферный кэш).

Первопричина: узкое место commit / журнала

Если преобладает 'log file sync' / 'WRITELOG', фиксации ждут записи redo/WAL.

Первопричина: нет индекса

План выполняет полное сканирование. Подходящий индекс по столбцам фильтра/соединения даёт большое ускорение.

Первопричина: регрессия плана

Вчера запрос был быстрым, а сегодня нет; план изменился. Обычно из-за устаревшей статистики или изменения параметров.

Первопричина: дизайн запроса

План разумен, но запрос по своей сути делает слишком много работы (слишком много строк, N+1, лишние соединения).

Первопричина: устаревшая статистика

Оптимизатор ошибается в оценках, потому что статистика не актуальна.

Углублённый анализ

Если план и статистика в порядке, но всё равно медленно, копайте глубже.

Первопричина: узкое место CPU

Вся система упирается в CPU. Обычно CPU сжигают несколько запросов с плохими планами.

Первопричина: давление на память

Низкий процент попаданий в кэш и/или своппинг: памяти недостаточно или она распределена неправильно.