Настройка запроса по образцу: четыре способа корректировки плана запроса без изменения кода Деев Илья, «Иннова-Системс»

Обновление кода приложения Изменение данных и статистики Смена версии и изменение поведения оптимизатора Изменение параметров оптимизатора Возможные источники проблем

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

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

1. Находим проблемы с планом запроса 2. Изменяем план запроса хинтами 3. Проверяем результат через выполнение нового варианта запроса 4. Применяем набор винтов настроенного запроса к проблемному запросу Настройка запроса по образцу

Настроенная версия SQL Неприемлемый план Регулирующие хинты Настроенный план Все хинты настроенного запроса на выходе CBO SQL hint injection Проблемная версия SQL Неприемлемый план Все хинты настроенного запроса на вход CBO Настроенный план

Хинты как результат работы оптимизатора select * from table(dbms_xplan.display_cursor(,,'outline')); Хинты в тексте запроса – гарантия применения, но при этом отсутствие гибкости Хинты в механизмах стабилизации планов выполнения: Stored Outlines -> SQL Plan Baselines Немного о хантах

Скрипт 0.test_data.sql -- пользователь drop user test cascade; create user test identified by test; grant connect, resource to test; -- данные для тестового запроса create table test.drop_tbl as select rownum n, 'txt'||rownum txt from dual connect by level 'test', tabname => 'drop_tbl'); end; Тестовые данные

Oracle 11 Enterprise Edition Идея - работа с планом запроса-образца: dbms_spm.load_plan_from_cursor_cache Права, параметр, скрипты с примерами создания и удаления: 1.sql_plan_baseline.sql Контроль: V$SQL.SQL_PLAN_BASELINE Преимущество – стандартный функционал, простота использования Метод 1: SQL Plan Baseline

Oracle 11 (официально – в EE, SQL Repair Advisor в Enterprise Manager, реально - все редакции) Идея – использовать SQL patch не в рамках SQL Repair Advisor, а напрямую Скрипты – 2.sql_patch.sql Контроль – V$SQL.SQL_PATCH Особенность – хинты обрабатываются только в системном виде, используется внутренний пакет: sys.dbms_sqldiag_internal.i_create_patch Метод 2: SQL Patch

Oracle 10, 11, Diagnostic & Tuning Pack Идея – использовать список нужных винтов при импорте профиля: DBMS_SQLTUNE.IMPORT_SQL_PROFILE Скрипты: 3.sql_profile.sql Контроль – V$SQL.SQL_PROFILE Особенность: по сути - мягкий как Метод 3: SQL Profile

Oracle 9,10,11, все редакции Идея – заменить список винтов в private outline проблемного запроса хинтами настроенного запроса и создать на его основе public outline. Скрипты: 4.1.outlines.sql, 4.2.outlines.sql Контроль - ALL_OUTLINES Особенность – метод устарел, но … он самый мощный! Метод 4: Outlines

Отмена действия винтов в тексте запроса Хинт IGNORE_OPTIM_EMBEDDED_HINTS

Приоритеты при совместном использовании SQL plan baseline SQL Profile SQL patch Outlineselect * from table(dbms_xplan.display(null,null, 'basic+note')); -- Note outline "OL_4AAY3KXC7RDDG" used for this statement SQL patch "patch_4aay3kxc7rddg" used for this statement - SQL plan baseline "SQL_PLAN_3dm7hzprspdufe13b857f" used for this statement ++ - SQL profile "PROF_4aay3kxc7rddg" used for this statement - SQL plan baseline "SQL_PLAN_3dm7hzprspdufe13b857f" used for this statement +