Подробнее о фиксации планов выполнения: PostgreSQL , PostgresPro , Oracle.

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

Подробнее о методологии фиксации планов выполнения в PostgreSQL и Oracle

1. Сводное описание параметра autoprepare_threshold (PostgreSQL / Postgres Pro)

Параметр autoprepare_threshold — это механизм автоматической подготовки операторов (auto-prepare), доступный в Postgres Pro Enterprise (начиная с версии 14) и перенесённый в основную ветвь PostgreSQL 17.

1.1. Принцип работы

• Суть: Сервер автоматически находит часто повторяющиеся параметризованные запросы и кеширует их планы выполнения, без явных команд PREPARE/EXECUTE со стороны приложения.
• Условие срабатывания: Запрос должен быть выполнен не менее autoprepare_threshold раз в рамках одного обслуживающего процесса (backend).
• Значение по умолчанию: 0 — режим автоподготовки отключён.
• Сопутствующие параметры: autoprepare_limit (макс. число подготовленных операторов на процесс, умолч. 100), autoprepare_memory_limit, autoprepare_for_protocol.
• Эффект: Повторное выполнение запроса пропускает стадии парсинга и планирования, экономя CPU и снижая latency.
• Отличие от generic-планов: Автоподготовка кеширует generic-план (общий план без учёта конкретных значений параметров); это аналог внутреннего механизма PREPARE, но без участия клиента.

1.2. Пример влияния на профиль нагрузки (на основе предоставленных данных topic-1.txt и topic-2.txt)

В предоставленном исследовании сравнивались два периода работы продуктивной системы PostgreSQL 17:

• Период 1 (снимки 335-336): online_analyze.enable=off, autoprepare_threshold=2, generic_plan_fuzz_factor=0.9
• Период 2 (снимки 383-384): online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1

⚠️ Важное терминологическое уточнение: Значение autoprepare_threshold=0 отключает режим автоподготовки. В комбинации с generic_plan_fuzz_factor=1 планировщик всегда выбирает custom-план (специализированный, с конкретными значениями параметров) вместо generic-плана. Это ключевое различие с поведением при autoprepare_threshold=2, когда после 2 выполнений мог кешироваться generic-план.

Изменение ключевых метрик (база DB-4, >99% нагрузки)

• Total time: 9939.42 с → 7823.90 с (–21.3%) — Подтверждено данными
• I/O time: 3642.46 с → 2520.62 с (–30.8%) — Подтверждено данными
• Executed count: 6 360 955 → 7 249 064 (+14.0%) — Подтверждено данными
• Среднее время запроса: 1.56 мс → 1.08 мс (–30.8%) — Подтверждено данными
• Shared blocks read: 79 952 474 → 57 980 273 (–27.5%) — Подтверждено данными
• Temp/Local blocks written: 5 305 366 → 3 844 964 (–27.5%) — Подтверждено данными
• Hit ratio: 92.2% → 93.9% (+1.7 п.п.) — Подтверждено данными
• Cache resets: 4 → 0 (прекратились) — Подтверждено данными

Анализ планов выполнения (Top SQL)

Для ключевого запроса 12e2db113ff929b0 (выборка из _InfoRg12488 с сортировкой) зафиксированы следующие изменения I/O:

• Seq Scan: 25.75 с (48 вызовов) → 0 с (исчез) –100%
• Bitmap Heap Scan: 24.26 с (649 вызовов) → 12.82 с –47%
• Index Scan: 32.0 с (30 245 вызовов) → 9.91 с –69%

Суммарное I/O запроса снизилось в 3.6 раза (~82 с → ~23 с). План с последовательным сканированием полностью исчез, что является прямым следствием отказа от фиксированного generic-плана в пользу custom-плана с индексным доступом.

Механизм влияния

1. При autoprepare_threshold=2 и generic_plan_fuzz_factor=0.9: После 2 выполнений мог кешироваться обобщённый (generic) план, который из-за усреднённых оценок параметров выбирал Seq Scan. Порог fuzz_factor=0.9 допускал выбор generic-плана, даже если его стоимость была до 10% выше custom-плана.
2. При autoprepare_threshold=0 и generic_plan_fuzz_factor=1: Автоподготовка отключена; fuzz_factor=1 означает, что generic-план выбирается, только если его стоимость строго равна или ниже custom-плана. Планировщик вынужден каждый раз генерировать custom-план с учётом конкретных значений параметров, что приводит к стабильному выбору Index Scan.
3. Роль online_analyze: Признана вторичной для данного кейса, поскольку проблемные запросы не используют временные таблицы, а статистика не-временных таблиц обновляется штатным autovacuum/autoanalyze.

Уровень достоверности общего вывода

Вероятно (Уровень-2): Ключевым фактором улучшения метрик Load Distribution является отказ от преждевременной фиксации неоптимальных обобщённых планов и переход на специализированные планы с индексным доступом. Для перевода в статус "Подтверждено" необходим контролируемый A/B-тест с изолированным изменением параметров и анализ pg_stat_statements по planid.

2. Механизмы фиксации планов выполнения в Oracle

В Oracle Database существует несколько эволюционно сменявших друг друга механизмов фиксации и стабилизации планов выполнения. Ниже приведён их систематизированный обзор.

2.1. Историческая эволюция

• Oracle 8i — 10g: Stored Outlines (хранимые контуры) — ⚠️ устаревший (deprecated), но поддерживается для обратной совместимости.
• Oracle 10g — настоящее время: SQL Profiles (профили SQL) — актуален; используется в связке с SQL Tuning Advisor.
• Oracle 11g — настоящее время: SQL Plan Management (SPM) / SQL Plan Baselines — основной рекомендуемый механизм.
• Oracle 12c+: SQL Plan Directives — дополнительный механизм; автоматическая корректировка оценок кардинальности.

2.2. Stored Outlines (хранимые контуры)

Суть: Набор подсказок (hints), принудительно задающих план выполнения для конкретного SQL-запроса.

• Принцип: Сохраняется набор атрибутов оптимизатора (фактически hints), которые воспроизводят конкретный план выполнения.
• Создание: CREATE [OR REPLACE] OUTLINE outline_name FOR CATEGORY category_name ON sql_statement
• Активация: ALTER SESSION SET use_stored_outlines = true или системно через ALTER SYSTEM
• Идентификация запроса: Точное текстовое совпадение запроса (case-sensitive, пробелы критичны)
• Недостатки:
• Жёсткая привязка к тексту запроса (даже лишний пробел ломает совпадение)
• Полностью исключает возможность нахождения лучшего плана
• Требует ручного управления
• Не интегрирован с эволюцией планов
• Миграция: Возможна в SPM через DBMS_SPM.MIGRATE_STORED_OUTLINE

2.3. SQL Plan Management (SPM) и SQL Plan Baselines

Суть: Проактивный механизм, при котором оптимизатор автоматически управляет планами выполнения, гарантируя использование только известных или проверенных планов. Основной механизм с Oracle 11g.

2.3.1. Архитектура SPM

SQL Management Base (SMB) — логическое хранилище в SYSAUX, состоящее из:

• SQL Plan History (история планов):
• При первом выполнении запроса план попадает в историю
• Каждый новый план также сохраняется
• SQL Plan Baseline (базовая линия планов):
• Accepted plans (принятые планы) — могут использоваться оптимизатором
• Fixed plans (фиксированные) — имеют приоритет над нефиксированными
• Non-accepted plans (непринятые) — не используются до верификации
• SQL Statement Log (журнал SQL-операторов)

2.3.2. Жизненный цикл плана в SPM

1. Захват (Capture): План выполнения автоматически захватывается при компиляции запроса. Условие: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE.
2. Формирование baseline: При первом выполнении план автоматически становится accepted и формирует исходную baseline.
3. Обнаружение нового плана: При изменениях среды (статистика, индексы, версия) оптимизатор может сгенерировать новый план. Он добавляется в plan history как non-accepted.
4. Эволюция (Evolution): Запускается DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE или автоматическая задача SPM Evolve Advisor. Оптимизатор проверяет, улучшает ли новый план производительность.
5. Принятие или отклонение: Если новый план лучше — он становится accepted. Если хуже — остаётся non-accepted и не используется оптимизатором.
6. Фиксация (Fix): Администратор может пометить план как fixed, что даёт ему абсолютный приоритет при выборе.

2.3.3. Способы создания SQL Plan Baseline

• Автоматический захват: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE — все планы сохраняются.
• Из Cursor Cache: DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '...') — загрузка из разделяемого пула.
• Из SQL Tuning Set (STS): DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => '...') — массовая загрузка из настроечного набора.
• Ручная загрузка: Из AWR или другого источника с помощью DBMS_SPM.
• Миграция Stored Outlines: DBMS_SPM.MIGRATE_STORED_OUTLINE — перенос хранимых контуров в baseline.

2.3.4. Ключевые преимущества SPM над Stored Outlines

• Предотвращение регрессий: Новый план не применяется автоматически — он должен пройти верификацию (эволюцию).
• Возможность улучшения: Оптимизатор может находить и внедрять лучшие планы (в отличие от "замороженных" outlines).
• Независимость от текста: Использует SQL_ID (хэш), а не точное текстовое совпадение.
• Автоматизация: SPM Evolve Advisor автоматически эволюционирует baseline по расписанию.
• Гибкость: Поддержка fixed-планов, ручного принятия/отклонения, переноса между базами.

2.3.5. Новшества в Oracle 19c/23ai

• ADD_VERIFIED_SQL_PLAN_BASELINE (19.22+, 23ai): автоматически находит предыдущие планы выполнения, определяет лучший и создаёт baseline с одним вызовом.

2.4. SQL Profiles (профили SQL)

Суть: Дополнительная информация для оптимизатора, корректирующая оценки кардинальности (cardinality) без жёсткой фиксации плана.

• Создание: Автоматически через SQL Tuning Advisor (DBMS_SQLTUNE) или вручную через DBMS_SQLTUNE.IMPORT_SQL_PROFILE
• Отличие от baseline: Профиль корректирует статистику, а не фиксирует план. Оптимизатор сам выбирает метод доступа, но с уточнёнными оценками.
• Преимущество: Более гибкий, чем baseline — адаптируется к изменениям данных.

3. Сравнение механизмов фиксации планов в PostgreSQL и Oracle

3.1. Детальное сравнение

Основной механизм

• PostgreSQL (17): Встроенный PREPARE/EXECUTE; параметр plan_cache_mode (с версии 12)
• PostgreSQL + Postgres Pro (17): autoprepare (автоподготовка); расширение pg_plan_advice (с версии 19)
• Oracle (19c/23ai): SQL Plan Management (SPM) — SQL Plan Baselines (основной); Stored Outlines (устаревший)

Способ активации

• PostgreSQL (17): Явный: клиент выполняет PREPARE, затем EXECUTE
• PostgreSQL + Postgres Pro (17): Автоматический: сервер автономно находит и кеширует планы часто выполняемых запросов
• Oracle (19c/23ai): Автоматический (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE) или ручной через DBMS_SPM

Уровень кеширования

• PostgreSQL (17): Локальный (в рамках backend-процесса). План не разделяется между сессиями
• PostgreSQL + Postgres Pro (17): Локальный (в рамках backend-процесса), аналогично PREPARE
• Oracle (19c/23ai): Глобальный (в разделяемом пуле SGA). План доступен всем сессиям

Срок жизни плана

• PostgreSQL (17): До закрытия prepared-оператора, завершения сессии или сброса кеша планов
• PostgreSQL + Postgres Pro (17): До завершения сессии или сброса кеша; ограничивается autoprepare_limit
• Oracle (19c/23ai): Сохраняется в SMB (табличное пространство SYSAUX) на постоянной основе

Generic vs Custom планы

• PostgreSQL (17): Автоматический выбор после 5 выполнений (по умолчанию): если generic-план дешевле, переключается на него
• PostgreSQL + Postgres Pro (17): Аналогично, дополнительно управляется generic_plan_fuzz_factor и autoprepare_threshold
• Oracle (19c/23ai): Oracle не использует концепцию generic/custom в том же смысле. Планы кешируются с учётом bind variable peeking (подсмотр значений переменных связывания)

Стабилизация плана

• PostgreSQL (17): Отсутствует встроенный механизм. plan_cache_mode=force_generic_plan может зафиксировать generic-план, но это грубый подход
• PostgreSQL + Postgres Pro (17): pg_plan_advice (с версии 19) позволяет сохранить "совет планировщика" и воспроизвести план при последующем выполнении
• Oracle (19c/23ai): SPM: история планов + baseline + эволюция. Администратор контролирует, какие планы accepted/fixed

Предотвращение регрессий

• PostgreSQL (17): ❌ Нет встроенного механизма. Generic-план может "заморозиться" и стать неоптимальным при изменении данных (как в рассмотренном кейсе с autoprepare_threshold=2)
• PostgreSQL + Postgres Pro (17): Частично: pg_plan_advice позволяет зафиксировать проверенный план
• Oracle (19c/23ai): ✅ Полноценный: новый план становится non-accepted и не применяется до верификации через эволюцию

Эволюция планов

• PostgreSQL (17): ❌ Отсутствует
• PostgreSQL + Postgres Pro (17): ❌ Отсутствует
• Oracle (19c/23ai): ✅ Автоматическая (SPM Evolve Advisor) или ручная через DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

Возможность переноса планов

• PostgreSQL (17): ❌ Не предусмотрена (планы локальны)
• PostgreSQL + Postgres Pro (17): ❌ Не предусмотрена
• Oracle (19c/23ai): ✅ Экспорт/импорт baseline между базами через DBMS_SPM или Data Pump

Интеграция с подсказками

• PostgreSQL (17): Прямые подсказки планировщику отсутствуют. Можно косвенно влиять через enable_* параметры и join_collapse_limit
• PostgreSQL + Postgres Pro (17): Расширение pg_hint_plan (стороннее); pg_plan_advice (встроенное, версия 19)
• Oracle (19c/23ai): Подсказки через комментарии (/*+ hint */) встроены на уровне ядра

Зрелость механизма

• PostgreSQL (17): Базовый. Основное развитие — в готовящемся pg_plan_advice
• PostgreSQL + Postgres Pro (17): autoprepare — зрелый; pg_plan_advice — новый, в стадии активного развития
• Oracle (19c/23ai): Высокозрелый. SPM развивается с 2008 г. (Oracle 11g) и глубоко интегрирован

3.2. Принципиальные архитектурные различия

3.2.1. Глобальный vs Локальный кеш планов

• Oracle: Планы выполнения хранятся в разделяемом пуле (SGA) и доступны всем сессиям. Новый план, обнаруженный одной сессией, сразу попадает в SMB и может использоваться (или блокироваться) для всех остальных. Это обеспечивает централизованное управление, но требует механизмов предотвращения регрессий (SPM).
• PostgreSQL: Каждый backend-процесс хранит собственный кеш планов. Prepared-оператор (и его generic-план) существует только в рамках одной сессии. Одна сессия может страдать от неоптимального плана, в то время как другая — использовать оптимальный, даже для идентичного запроса. Это архитектурное ограничение PostgreSQL, проистекающее из его process-per-connection модели.

3.2.2. Подход к управлению планами

• Oracle:
• Философия: "Доверяй, но проверяй" — оптимизатор может пробовать новые планы, но применяет их только после верификации.
• Роль администратора: Активная: управление baseline, эволюция, аудит планов.
• Автоматизация: SPM Evolve Advisor, автоматический захват baseline.
• PostgreSQL:
• Философия: "Планируй каждый раз заново, если нет явной команды кешировать".
• Роль администратора: Ограниченная: настройка plan_cache_mode, autoprepare_threshold, статистики.
• Автоматизация: Автоподготовка (autoprepare) — единственный автоматический механизм; pg_plan_advice требует ручного вмешательства.

3.2.3. Влияние на рассмотренный кейс

В предоставленном эксперименте (topic-1.txt, topic-2.txt) ключевая проблема заключалась в том, что локальный кеш планов PostgreSQL зафиксировал неоптимальный Seq Scan через generic-план при autoprepare_threshold=2. В Oracle такая ситуация была бы обработана иначе:

• SPM с автоматическим захватом: При обнаружении нового плана (Index Scan) он был бы добавлен в plan history и после эволюции мог бы заменить неоптимальный план в baseline.
• Если бы неоптимальный план уже был в baseline: Новый (Index Scan) добавился бы как non-accepted. Администратор мог бы вручную принять его, а старый фиксированный план — удалить или пометить как non-accepted.
• При использовании Stored Outlines: План был бы жёстко зафиксирован, и никакого улучшения не произошло бы (аналогично проблемной конфигурации PostgreSQL).

4. Итоговый вывод

Сравнительная оценка по ключевым аспектам

• Простота настройки:
• PostgreSQL: ✅ Высокая — достаточно изменить несколько параметров
• Oracle: 🔴 Умеренная — требует понимания SPM, DBMS_SPM, эволюции
• Предсказуемость:
• PostgreSQL: 🟡 Зависит от конфигурации. Неверный autoprepare_threshold/generic_plan_fuzz_factor может зафиксировать неоптимальный план (подтверждено экспериментом)
• Oracle: 🟢 Высокая — baseline гарантирует использование только проверенных планов
• Гибкость:
• PostgreSQL: 🟡 Ограничена — нет механизма эволюции планов; pg_plan_advice (PostgreSQL 19) частично решает проблему
• Oracle: 🟢 Высокая — эволюция, фиксация, ручной контроль
• Масштабируемость:
• PostgreSQL: 🟢 Локальный кеш исключает contention на глобальном кеше
• Oracle: 🟡 Глобальный кеш может быть узким местом при высокой конкуренции

Практическая рекомендация (на основе предоставленных данных)

Для высоконагруженных систем PostgreSQL 17 с преобладанием параметризованных запросов:

• Установка autoprepare_threshold = 0 (отключение автоподготовки) в сочетании с generic_plan_fuzz_factor = 1 (строгое предпочтение custom-планов) обоснованно привела к снижению Total time на 21.3% и I/O на 30.8% за счёт отказа от преждевременной фиксации неоптимальных обобщённых планов.
• Включение online_analyze признано вторичным фактором для данного профиля нагрузки (проблемные запросы не используют временные таблицы).
• Уровень достоверности: Вероятно (Уровень-2). Для окончательного подтверждения необходим изолированный A/B-тест с анализом pg_stat_statements по planid и логами auto_explain.
• После выпуска PostgreSQL 19 с pg_plan_advice станет доступен более тонкий механизм фиксации планов, приближающий PostgreSQL к возможностям Oracle SPM.

Более подробное описание