План запроса деградировал (внезапно медленно)

Запрос, вчера выполнявшийся быстро, сегодня внезапно медленный. Обычно оптимизатор переключился на худший план. Найдём первопричину среди устаревшей статистики, чувствительности к параметрам, роста объёма данных или внезапной смены плана и стабилизируем план.

Действительно ли план изменился и когда?

Есть ли несколько планов (plan_hash/plan_generation) для одного запроса и когда началось замедление? Совпадает ли это с недавним развёртыванием, обновлением, изменением параметров или большой загрузкой данных?

SELECT sql_id, plan_hash_value, executions, ROUND(elapsed_time/GREATEST(executions,1)/1e6,3) AS sec_per_exec, first_load_time
FROM v$sql WHERE sql_id='&sqlid' ORDER BY first_load_time;

• Статистика может быть устаревшей
• Один и тот же запрос ведёт себя по-разному в зависимости от параметра (sniffing/peeking)
• Объём данных значительно вырос
• План внезапно изменился после обновления/сброса

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

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

SELECT table_name, num_rows, last_analyzed, stale_stats
FROM dba_tab_statistics WHERE table_name='T';

• Статистика устарела/отсутствует
• Статистика актуальна, но план по-прежнему чувствителен к параметрам

Есть ли чувствительность к параметрам?

Запрос выбирает и кэширует план по значению параметра при первом выполнении (MSSQL parameter sniffing / Oracle bind peeking). Для других значений при неравномерном распределении этот план может быть плохим.

SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware
FROM v$sql WHERE sql_id='&sqlid';

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

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

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

Первопричина: чувствительность к параметрам (sniffing / bind peeking)

Кэшированный план выбран под первое пришедшее значение параметра и плох для других значений при неравномерном распределении. Цель — обеспечить выбор плана, подходящего под значение.

Решение: закрепите правильный план (baseline / force plan)

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

Первопричина: объём данных вырос, план больше не подходит

По мере роста таблицы план, ранее бывший идеальным (например, nested loop), теперь плохо масштабируется; оптимизатору нужны статистика и пути доступа, правильно отражающие рост.

Первопричина: внезапная смена плана (обновление / сброс кэша)

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