tuning_by_example

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

2. Возможные источники проблем

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

3. Борьба с неприятными сюрпризами

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

4. Что хотелось бы получить

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

5. Настройка запроса по образцу

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

6. “SQL hint injection”

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

7. Немного о хинтах

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

8. Тестовые данные

Скрипт 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 <=10000;
create index test.i_drop_tbl_id on test.drop_tbl(n);
-- статистика
begin
dbms_stats.gather_table_stats(ownname => 'test', tabname
=> 'drop_tbl');
end;

9. Метод 1: SQL Plan Baseline

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

10. Метод 2: SQL Patch

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

11. Метод 3: SQL Profile

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

12. Метод 4: Outlines

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

13. Отмена действия хинтов в тексте запроса

Хинт IGNORE_OPTIM_EMBEDDED_HINTS

14. Приоритеты при совместном использовании

SQL
SQL
plan
Profile
baseline
SQL
patch
+
+
+
+
+
+
+
+
+
Outline select * 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
- SQL plan baseline
"SQL_PLAN_3dm7hzprspdufe13b857f" used for this
statement