SPM (SQL Plan Management)

#===================================================================================================
# SPM (SQL Plan Management)
#===================================================================================================

SPM (SQL Plan Management) - автоматический процесс управляющий базовыми планами выполнения

Работа SPM управляется параметрами

-- 1. параметр включающий автоматический захват и добавление в SPM планов для повторяющихся запросов
show parameter optimizer_capture_sql_plan_baselines;
-- FALSE (по умолчанию)

-- 2. параметр включающий использование захваченных планов SPM
show parameter optimizer_use_sql_plan_baselines
-- TRUE (по умолчанию)

-- список базовых планов сохраненных SPM
select * from DBA_SQL_PLAN_BASELINES;

-- оптимизатор использует базовые планы для которых соблюдается условие
select * from DBA_SQL_PLAN_BASELINES where ENABLED='YES' and ACCEPTED='YES';

-- поле ORIGIN указывает как план попал в реестр SPM
--   AUTO-CAPTURE - автоматический захват
--   MANUAL-LOAD - загруженный вручную

-- то что базовый план включен и принят не обязывает оптимизатор использовать именно его
-- оптимизатор выбирает из имеющихся планов тот который кажется ему наиболее оптимальным
-- чтобы заставить оптимизатор использовать нужный план из SPM, его необходимо зафиксировать
DECLARE
  plans_altered PLS_INTEGER;
BEGIN
  plans_altered := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => 'ВАШ_SQL_HANDLE',
    attribute_name  => 'fixed',
    attribute_value => 'YES'
  );
END;
/

#===================================================================================================
# ручная загрузка планов в SPM
#===================================================================================================
----------------------------------------------
-- вариант 1. ручная загрузка плана в SPM из курсора в Shared Pool
----------------------------------------------
SET SERVEROUTPUT ON;
DECLARE
  plans_loaded  PLS_INTEGER;
BEGIN
  plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id       => 'ВАШ_SQL_ID',           -- Например, 'g123h456jk789'
    plan_hash_value => 1234567890,          -- Опционально: конкретный хеш плана
    sql_text     => NULL,                   -- Опционально: можно указать текст, если нет SQL_ID
    enabled      => 'YES',                  -- Сделать план активным сразу (YES/NO)
    accepted     => 'YES',                  -- Пометить как принятый (YES/NO)
    fixed        => 'NO'                    -- Сделать план фиксированным (YES/NO)
  );
  DBMS_OUTPUT.PUT_LINE('Загружено планов: ' || plans_loaded);
END;
/

----------------------------------------------
-- вариант 2. экспорт/импорт SPM и перенос в другую базу
----------------------------------------------
-- 1. создание Staging Table
EXEC DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'SPM_STAG_TABLE', table_owner => 'SYSTEM');

-- 2. упаковка планов в таблицу
DECLARE
  plans_packed PLS_INTEGER;
BEGIN
  plans_packed := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => 'SPM_STAG_TABLE',
    sql_id     => 'ВАШ_SQL_ID'
  );
END;
/

-- 3. экспортировать/импортировать таблицу

-- 4. распаковать планы из Staging Table

DECLARE
  plans_unpacked PLS_INTEGER;
BEGIN
  plans_unpacked := DBMS_SPM.UNPACK_STGTAB_BASELINE(
    table_name => 'SPM_STAG_TABLE'
  );
END;
/

#===================================================================================================
# выключение/удаление базового плана SQL Plan Baseline
#===================================================================================================

-- выключение базового плана
DECLARE
  ret PLS_INTEGER;
BEGIN
  ret := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => '<sql_handle>',
    plan_name       => '<plan_name>',
    attribute_name  => 'enabled',
    attribute_value => 'NO'
  );
END;
/

-- удаление базового плана
DECLARE
  plans_dropped PLS_INTEGER;
BEGIN
  plans_dropped := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
    sql_handle => '<sql_handle>',
    plan_name  => '<plan_name>'
  );
END;
/

#===================================================================================================
# определение проблемных запросов и их планов
#===================================================================================================
-- сортировка по buffer_gets
SELECT sql_id, PLAN_HASH_VALUE, SQL_PLAN_BASELINE, fetches, executions, disk_reads, buffer_gets FROM V$SQLAREA order by buffer_gets desc;

-- сортировка по disk_reads
SELECT sql_id, PLAN_HASH_VALUE, SQL_PLAN_BASELINE, fetches, executions, disk_reads, buffer_gets FROM V$SQLAREA order by buffer_gets desc;

-- идентификаторы плана PLAN_HASH_VALUE, SQL_PLAN_BASELINE
-- запросы с разными sql_id могут иметь одинаковый PLAN_HASH_VALUE потому что хешируется сама структура плана
-- без привязки к конкретным именам таблиц или полей

#===================================================================================================
# просмотр планов
#===================================================================================================
-- список планов для заданного sql_id
select plan_hash_value from v$sql_plan where sql_id='<sql_id>' group by plan_hash_value;

-- полный текст планов для заданного sql_id
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>', null, 'ALLSTATS LAST'));

#===================================================================================================
#  удаление из Shared Pool (временное решение)
#===================================================================================================
-- определение address и hash
SELECT address || ',' || hash_value AS name_string FROM V$SQLAREA WHERE sql_id = '<sql_id>';

-- удаление из shared pool
EXEC DBMS_SHARED_POOL.PURGE('<address>,<hash>', 'C');

#===================================================================================================
# Расчет плана без выполнения запроса
#===================================================================================================
-- example 1
EXPLAIN PLAN SET STATEMENT_ID = 'example_1' FOR
select * from T1 where idnum in (
  select idnum from T2 group by idnum
);

-- example 2
EXPLAIN PLAN SET STATEMENT_ID = 'example_1' FOR
select * from T1 a, (select idnum from T2 group by idnum) b where a.idnum = b.idnum;

-- Обратите внимание, что EXPLAIN PLAN FOR — это DML-оператор, поэтому после его выполнения вам, возможно,
-- потребуется сделать COMMIT, чтобы планы стали видны другим сессиям, но обычно в вашей текущей сессии они сразу доступны.

-- После выполнения команд EXPLAIN PLAN, выберите результаты из PLAN_TABLE с помощью системной функции DBMS_XPLAN.DISPLAY.
-- Это наиболее удобный способ форматированного вывода: 

-- Показать план для example 1
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'example_1', 'TYPICAL'));

-- Показать план для example 2
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'example_2', 'TYPICAL'));

-- EXPLAIN PLAN FOR дает вам предполагаемый план. В редких случаях (обычно из-за сильно устаревшей статистики или
-- использования привязок переменных — bind variables) фактический план выполнения (который вы видите, когда
-- реально запускаете запрос, например, через AUTOTRACE или DBMS_XPLAN.DISPLAY_CURSOR после выполнения)
-- может немного отличаться от предполагаемого.