plan hash value что это

Мой блог

четверг, 20 сентября 2018 г.

План запроса

А также Explain plan и SQL*Plus autotrace не учитывают преобразование типов данных.
Например, если столбец символьного типа сравнивается с числом, происходит неявное
преобразование типов и индекс столбца не может использоваться.

Получить реальный план выполнения запроса можно из представления v$sql_plan.

Определяем hash_value, address, child_number, sql_id, plan_hash_value

SELECT hash_value, address, child_number, sql_id, plan_hash_value
FROM v$sql
WHERE sql_text LIKE ‘%select sysdate from dual%’
AND sql_text NOT LIKE ‘%v_sql%’;

Получить план можно так:

SELECT
lpad(‘ ‘, 2 * level) || pt.operation || ‘ ‘ || pt.options || ‘ ‘ || pt.object_name «Plan Access Path»,
pt.cost,
pt.cardinality,
pt.bytes,
pt.cpu_cost,
pt.io_cost,
pt.temp_space,
pt.access_predicates,
pt.filter_predicates,
pt.qblock_name as «Query Block name»
FROM (select *
from v$sql_plan
where HASH_VALUE = 2343063137
and ADDRESS = ‘00007FF7C88C4128’
and CHILD_NUMBER = 0
) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;

SELECT
lpad(‘ ‘, 2 * level) || pt.operation || ‘ ‘ || pt.options || ‘ ‘ || pt.object_name «Plan Access Path»,
pt.cost,
pt.cardinality,
pt.bytes,
pt.cpu_cost,
pt.io_cost,
pt.temp_space,
pt.access_predicates,
pt.filter_predicates,
pt.qblock_name as «Query Block name»
FROM (select *
from v$sql_plan
where sql_id = ‘7h35uxf5uhmm1’
and child_number = 0
) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;

Получить план из AWR по sql_id и plan_hash_value:

SELECT lpad(‘ ‘, 2 * level) || pt.operation || ‘ ‘ || pt.options || ‘ ‘ ||
pt.object_name «Query Plan»,
pt.cost,
pt.cardinality,
pt.bytes,
pt.cpu_cost,
pt.io_cost,
pt.temp_space,
pt.access_predicates,
pt.filter_predicates,
pt.qblock_name
FROM (select *
from dba_hist_sql_plan
where sql_id = ‘7h35uxf5uhmm1’
and plan_hash_value = 1388734953
) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;

Для получения плана запроса удобно использовать пакет DBMS_XPLAN

Следующие функции этого пакета :

DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT ‘TYPICAL’);

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
format IN VARCHAR2 := ‘TYPICAL’,
sqlset_owner IN VARCHAR2 := NULL)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := ‘TYPICAL’)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

DBMS_XPLAN.DISPLAY_SQL_PATCH_PLAN(
name IN VARCHAR2,
format IN VARCHAR2 DEFAULT ‘TYPICAL’)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

DBMS_XPLAN.DISPLAY_SQL_PROFILE_PLAN(
name IN VARCHAR2,
format IN VARCHAR2 DEFAULT ‘TYPICAL’)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

Параметр format управляет уровнем детализации плана.

— id операции
— имя операции
— опции

Тоже что и basic плюс

Также отображается информация

Исключается тоько информация

— projection
— alias
— remote sql

Но не отображает в плане

даже если запрос выполняется параллельно.

Тоже что и typical плюс

— projection
— alias
— remote sql

Плюс информацию об используемых Outline (наборы подсказок)
из поля other_xml представления v$sql_plan_statistics_all

плюс все потенциальные варианты адаптивного плана выполнения курсора

Например к уровню basic можно добавить вывод дополнительных столбцов столбцов:

format => ‘basic +rows +bytes +cost +parallel +partition’

Хотя уровнь all всё это и так включает.

basic +rows +bytes +cost +parallel = typical без time
typical +projection +alias +remote = all
all +outline = advanced

Дополнительно можно добавить

Чтобы дополнительно добавить столбцы со статистикой ввода-вывода и использования памяти нужно добавить

или просто ALLSTATS

format => ‘advanced allstats’

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

format => ‘advanced allstats last’

select * from table (dbms_xplan.display_cursor(‘adcv4sbhvh2rv’,0,’ADVANCED ALLSTATS LAST +peeked_binds +report +metrics’));

Значения столбцов статистики плана выполнения

При добавлении дополнительных столбцов со статистикой ввода-вывода и использования памяти (allstats)
появятся следующие поля:

Для получения столбцов со статистикой ввода-вывода (allstats) необходимо установить значение параметра statistics_level = ALL.

alter system set statistics_level = all;

или
alter session set statistics_level = all;

наш запрос:
select count(*) from emp;

смотрим план:
select * from table (dbms_xplan.display_cursor(‘0vx5dc8jftang’,1,’ADVANCED ALLSTATS LAST +peeked_binds +report +metrics’));

Иначе в плане мы не увидим некоторых столбцов:

Хинт gather_plan_statistics используется для сбора расширенной статистики выполнения запроса в соответствии с планом выполнения.
Действует аналогично установке значения параметра statistics_level = all или _rowsource_execution_statistics = TRUE на уровне сессии или системы.
Данные по статистике плана выполнения при этом сохраняются в обзоре:
v$sql_plan_statistics_all

set linesize 150
set pagesize 2000
set serveroutput OFF

select /*+ gather_plan_statistics */ count(*) from t;

Источник

Oracle mechanics

SQL запросы и CBO

Актуальный план выполнения запроса

При анализе конкретного SQL запроса полезно выяснить, какой план выполнения в действительности используется Oracle, т.к. результат выполнения команды explain plan (в т.ч. при установке опции SQL*Plus AUTOTRACE ON EXPLAIN — см., например, EXPLAIN PLAN Restrictions), даёт только оценку возможного плана выполнения и может существенно отличаться от плана запроса, который будет в действительности использован при выполнении запроса

1. Из обзора V$SQL_PLAN, например, так:

План выполнения уникально определяется комбинациями значений (HASH_VALUE, ADDRESS, CHILD_NUMBER) либо (SQL_ID, CHILD_NUMBER) из V$SQL
QBLOCK_NAME – название блока запроса, используется в некоторых хинтах оптимизатором в outline section плана выполнения

2. Используя пакет DBMS_XPLAN (источником данных о плане является тот же обзор V$SQL_PLAN) сразу после выполнения запроса:

, либо по значениям (v$sql.sql_id, v$sql.child_number):

Функции пакета не показывают запросов, в тексте которых содержится выражение ‘%DBMS_XPLAN%’ в любом регистре — см. Не работает DBMS_XPLAN.DISPLAY_CURSOR

3. из трейс файлов (event 10053, 10132):

4. Из репозитория AWR:

— вплоть до 11.2 включительно значения access_predicates и filter_predicates в dba_hist_sql_plan, к сожалению, не сохраняются — Bug 7493519: ACCESS_PREDICATES AND FILTER_PREDICATES MISSING IN DBA_SQL_PLAN_HIST

5. Начиная с 11.2, где добавлено поле PLAN_PARENT_ID, из обзора V$SQL_PLAN_MONITOR, если запрос мониторится:

6. Начиная с 11.2 можно получить трейс оптимизатора с планом выполнения с помощью процедуры dbms_sqldiag.dump_trace

— однако, поскольку в этом случае для формирования трейса будет выполнен повторный разбор (hard parse) модифицированного комментарием запроса (с несовпадающим sql_id), сформированный трейс (и план) могут отличаться от актуальных

Plan hash value

определяется набором и последовательностью операций с объектами бд (определяемыми только по имени, без указания схемы) при выполнения запроса, например:

Условия (filter / access predicates), расчётные величины (стоимость, объём данных, время выполнения), информация о партициях, принадлежность объектов на значение Plan hash value не влияют — см. Randolf Geist. PLAN_HASH_VALUE — How equal (and stable?) are your execution plans — part 1

Значения связанных переменных и констант, используемые в запросе

Кроме SQL трейса (event 10046, level 4/12), можно найти в обзоре v$sql_bind_capture, например так:

Обзор v$sql_bind_capture содержит «… по одному из значений связанных переменных (bind values used for the bind variable), использованных во время одного из последних выполнений соответствующего (идентифицируемого по child_address, hash_value) SQL запроса. Значения связанных переменных не всегда попадают в этот обзор*. В обзор попадают значения связанных переменных только если связанные переменные имеют простой тип (LONG, LOB, и ADT типы данных исключаются) и связанные переменные используются во фразах WHERE или HAVING запроса.»

*) Не при всяком/каждом выполнении запроса значения связанных переменных попадают в этот обзор. Алгоритм отбора неясен, однако в некоторых известных случаях в обзор v$sql_bind_capture попадали значения связанных переменных, при которых определённый запрос выполнялся очень быстро, в то время как значения, при которых запрос был медленным не попадали. В то же время отчёты ADDM показывали, что большая часть выполнений этого запроса происходит медленно. Найти «медленные» значения помогла трассировка со стороны приложения

Начиная с версии Oracle 11g R2 значения связанных переменных для запросов, попадающих под Oracle Real-Time SQL Monitoring (параллельное выполнение, либо потребление >= 5 секунд CPU time или I/O time, либо использование подсказки /*+ MONITOR*/) можно найти в поле BINDS_XML обзора V$SQL_MONITOR:

Подсказки плана выполнения, используемые CBO при выполнении запроса (outline section)

Методы Oracle для модификации и фиксации существующих планов выполнения

Илья Деев. Четыре способа корректировки планов запросов по образцу без изменения кода

Видимость элементов SPM (SQL Profiles, SQL Plan Baselines, SQL Patches) в контейнерной архитектуре CDB/PDB

SQL Profiles

«В отличие от Stored Outlines, подсказки, используемые в SQL Profiles не пытаются диктовать механизм испольнения запроса (операции доступа к данным) напрямую. Вместо этого они устанавливают арифметические корректирующие коэффициенты для оптимизатора…»

т.е. SQL Profiles не пытается влиять на план выполнения запроса «напрямую», подсказывая способы доступа к объектам БД (использовать ли индекс — index range scan или FULL TABLE SCAN). Этот инструмент с помощью специальных хинтов (вычисленных на основании статистики реального выполнения запроса, используя технологию cardinality feedback) предоставляет CBO коэффициенты для расчётов и выбора «откорректированного жизнью» плана выполнения. Коэффициенты эти, конечно же, со временем могут стать неактуальными и может потребоваться повторное выполнение процедур подготовки SQL Profiles

Перечень уже применяемых в профилях хинтов можно увидеть в документально неупоминаемом обзоре SYS.DBMSHSXP_SQL_PROFILE_ATTR:

В 12c формат хранения рекомендованных хинтов после выполнения DBMS_SQLTUNE.EXECUTE_TUNING_TASK изменился:

, так же в 12c для получения списка подсказок созданного SQL Profile используется:

показывает хинты типа OPT_ESTIMATE(@»SEL$2″, TABLE, «C»@»SEL$2», SCALE_ROWS=106.8828149), что значит multiply row count by 106.88… для таблицы «C» из блока запроса ( QBLOCK_NAME) @»SEL$2″ — значения этих синонимов можно найти в файлах 10053 trace или в обзоре V$SQL_PLAN

«It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.»

Хранимые шаблоны / stored outlines

Доступны, начиная с версии Oracle 9i

Параметр управления use_stored_oulines | use_private_outlines не является инилизационным. При постоянном применении Oracle рекомендует устанавливать этот «параметр» системным триггером after startup on database — How to Enable USE_STORED_OUTLINES Permanently [ID 560331.1] (либо пользовательским after logon on database). Проверить текущее состояние переменной на уровне системы/сессии можно утилитой oradebug — How to check the category of a Stored Outline:

SQL PLAN BASELINES / SQL plan management (SPM)

Рекомендованный к применению метод управления планами выполнения, начиная с Oracle 11g

Идея: план выполнения из library cache, sql set или промежуточной таблицы (stage table) в составе набора подсказок и параметров оптимизатора сохраняется в системных таблицах (sys.sqlobj$data) для дальнейшего использования оптимизатором при построении плана очередного выполнения запроса

baseline = набор подсказок, применяемых на определённом, не первом этапе генерации плана выполнения.

Пример неавтоматического создания baseline для запроса, находящегося в shared pool SGA:

Созданный baseline, как и sql patsh (ещё один элемент технологии SPM — Sql Plan Management) будут применяться к запросам после нормализации SQL текста: исключения пробелов, переводов строки и нормализации регистра кроме регистра текстовых констант (literals), например, только что созданный baseline успешно применяется к запросу отличным sql_id:

Типичные операции с baseline:

Начиная с Oracle 11g фиксация планов выполнения запросов с помощью baseline может быть затруднена / проблематична из-за влияния технологий Cardinality Feedback / Bind Aware Cursor Sharing

Пример загрузки архивного плана из репозитория AWR, с последующим добавлением подсказок и установкой аттрибутов DBA_SQL_PLAN_BASELINES.AUTOPURGE и FIXED: Сложный запрос, использование Baseline и Bind-Aware Cursor Sharing

Владимир Пржиялковский. Как обязать СУБД применять к запросам конкретные приемлемые планы
HOW TO LOAD SQL PLANS INTO SPM FROM AWR [ID 789888.1] — восстановление из истории «хорошего» плана выполнения
Loading Hinted Execution Plans into SQL Plan Baseline. [ID 787692.1] — ручная модификация плана выполнения существующего baseline
Jonathan Lewis. Fake Baselines — пример создания sql baseline для запроса с планом выполнения другого запроса из Shared Pool за одно выполнение процедуры dbms_spm.load_plans_from_cursor_cache

New baselines are generated automatically:
• for statement that already have Baselines created (when new plans are parsed by the optimizer, as non accepted) –it is not capture!
• when creating a SQL Profile on a statement that has Baseline (as accepted)

SQL Patch

Метод добавления подсказок к запросу без изменения текста приложения — Using SQL Patch to add hints to a packaged application:

SQL Patch обладает некоторыми особенностями, например, отсутствием контроля содержимого и ограничением длины патча, создаваемого с помощью DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH, однако полноформатный «длинный» патч может быть создан процедурой SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE

Использование подсказок TABLE_STATS / INDEX_STATS / COLUMN_STATS

Szymon Skorupinski описывает способ/идею управления планом через модификацию статистики таблиц/индексов/столбцов на уровне запроса, при наличии «статистики хорошего выполнения» в DBA_TAB_STATISTICS / DBA_IND_STATISTICS / DBA_TAB_COL_STATISTICS либо в экспортированных таблицах:

Механизмы/технологии, используемые Oracle для автоматизированного улучшения планов выполнения

bind peeking

Использование значений связанных переменных при построении плана выполнения запроса, включая значения «настоящих» связанных переменных и «псевдо» связанных переменных, формируемых Oracle в процессе преобразовании запроса при использовании параметра CURSOR_SHARING=SIMILAR.
430208.1 Bind Peeking By Example
По умолчанию включён, начиная с Oracle 9.2, контролирующий параметр _OPTIM_PEEK_USER_BINDS. Называется причиной различий реальных планов выполнения запросов и планов, формируемых командами Autotrace и Explain Plan:
353670.1 Autotrace And Explain Plan Give Different Plans Than Row Source Trace

dynamic sampling

Оценка избирательности условий запроса (predicate selectivity) и статистики таблиц и индексов непосредственно во времявыполнения запроса. См. описание параметра optimizer_dynamic_sampling

«Начиная с Oracle 11gR, для параллельно выполняемых запросов оптимизатор автоматически принимает решение о применении и уровне dynamic sampling в зависимости от размеров таблиц и сложности условий в запросе. В случае если [на уровне системы/сессии/запроса] значение OPTIMIZER_DYNAMIC_SAMPLING установлено в значение, отличное от значения по умолчанию [2], dynamic sampling будет выполнять согласно установленному значению параметра. Факт использования dynamic sampling отражается в секции Note плана выполнения запроса»

При этом уровень dynamic sampling изменяется на уровне всего запроса и, например, может применяться к таблицам, обрабатываемым непараллельно, и не применяться к таблицам с параллельным доступом.

Bind-Aware Cursor Sharing

Начиная с Oracle 11.1 для запросов с использованием связанных переменных поддерживается генерация и нахождение в Shared Pool нескольких актуальных курсоров с различными планами выполнения. Разные курсоры применяются для наборов связанных переменных с различной статистикой выполнения запроса

Комплексная технология, состоит из 2-х последовательно применяемых функционалов Adaptive Cursor Sharing (ACS) и Extended Cursor Sharing (ECS). Adaptive Cursor Sharing для определения количества обрабатываемых на этапах выполнения запроса данных использует технологию Cardinality Feedback

Cardinality Feedback

Доступная с Oracle 11gR2 технология, использующая статистику исполнения запроса (rowsource statistics) для однократной модификации плана выполнения запроса, не использующего связанных переменных

Statistics Feedback

технология, продолжающая и развивающая Cardinality Feedback (CF) в версии 12c. Кроме дежурных исправлений / улучшений, данные Statistics Feedback в отличие от CF доступны в обзоре V$SQL_REDIRECTION в виде хинтов OPT_ESTIMATE, и сохраняются в Shared Pool в течение «времени жизни» курсора:

«Ручные» методы настройки запросов и влияния на план выполнения

Cardinality feedback

« Гипотеза (conjecture): CBO прекрасно находит наилучший план (access plan) для запроса в том случае, если имеет возможность точно оценить количество строк (CARDINALITIES) источников данных (ROW SOURCES), участвующих в плане выполнения»

В отличие от других методик, которые зачастую стараются заставить оптимизатор выбрать определённые операции доступа к данным в плане выполнения (access plan), этот метод настройки (tuning by cardinality feedback) ищет несоответствия между оценкой и реальными количеством строк, получаемых при выполнении плана (execution plan) и старается выяснить причины, приводящие CBO к ошибкам в оценках и выбору (предположительно) неоптимального плана выполнения. Как только причины найдены, следующей задачей является нахождение пути исправления причин ошибок в оценках, чтобы и в итоге предоставить возможность оптимизатору проделать свою работу заново, доверяя ему найти лучший план на основе исправленных, более точных оценок.
Таким образом эта методология не противоречит методике, используемой при подготовке SQL profiles с использованием пакета DBMS_SQLTUNE. SQL profiles дают CBO корректирующие коэффициенты для исправления [ошибочных] оценок количества строк источников данных (row source cardinality), в то время как метод TCF (tuning by cardinality feedback) стремится в первую очередь обеспечить оптимизатор более точной информацией для того, чтобы выполняемые оценки (row source cardinality estimates) приводили к более точным результатам.

Cardinality feedback — заметка J.Lewis, там же презентация Michelle Deng, в которой в качестве метода обеспечения оптимизатора более точной информацией об объектах бд используется механизм dynamic sampling

Коэффициенты, используемые CBO для расчётов в отсутствии статистики по объектам бд (т.е. по умолчанию)

В отсутствие необходимой для вычислений статисттики объектов бд (таблиц, столбцов, индексов) CBO использует набор постоянных величин и коэффициентов (hard-coded in Oracle) … Приведены наиболее свежие известные значения констант …

Источник

Четыре способа корректировки планов запросов по образцу без изменения кода.

Илья Деев,
«Innova Systems»,
член RuOUG

Источник: Russian Oracle User Group with the library and forum options,
http://www.ruoug.org/events/20121206/index.html
[От редакции FORS Magazine: дополнением к данной статье является одноименная презентация И.Деева, представленная им на семинаре Российской группы пользователей Oracle 06.12.2012.]

[От редакции FORS Magazine: в процессе обсуждения этой статьи уже после её опубликования обнаружилась небольшая неточность: в скрипте 1.1 выдача привилегий была корректна только для Oracle Database 11g и не применима в версии 10g. В настоящем тексте эта неточность исправлена. К сожалению, исправить также оперативно эту неточность в pgf-форматах этого выпуска журнала не представляется возможным, поэтому в следующем 6-м номере журнала будет опубликовано соответствующее письмо в редакцию нашего автора Ильи Деева.]

Введение

В процессе эксплуатации баз данных иногда возникает необходимость скорректировать планы запросов, не прибегая к изменению исходного кода. Такие средства влияния на план запроса как корректировка статистики, изменение параметров сессии, построение SQL профиля не всегда доступны, не всегда гарантированно ведут к нужному результату, а также могут иметь нежелательные побочные эффекты. Корректировка статистики объектов базы данных и установка параметров в сессии могут повлиять не только на проблемные запросы, но и на другие. Построение SQL профилей возможно только в Oracle Enterprise Edition при использовании платных Diagnostic &Tuning Pack. Однако, даже обладая этими инструментами, к сожалению, не всегда удается получить удовлетворительный результат. В данной заметке описываются способы решения проблемы с помощью метода настройки плана запроса по образцу.

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

Небольшое отступление по поводу хинтов. С одной стороны, набор хинтов – это результат работы оптимизатора. Начиная с Oracle 10.2 список хинтов запроса доступен через вывод плана запроса с помощью пакета DBMS_XPLAN с заданием формата вывода ‘outline’:

С другой стороны, хинты могут использоваться как входная управляющая информация непосредственно в тексте запроса либо, например, в функционале Stored Outlines. Использование хинтов в пользовательском коде может быть обосновано в том случае, когда прежде всего важна стабильность планов выполнения. Возможные недостатки при таком подходе – риск применения неполного списка хинтов, что может привести к изменению планов в другой среде, а также искусственное ограничение свободы действий оптимизатора, потенциально ведущее к невозможности использования других, быть может, более оптимальных планов. В целом, хинты в тексте запроса – это своеобразная смирительная рубашка для оптимизатора, которая иногда бывает необходима. Иногда возникает обратная задача, когда требуется отменить действие ненужных хинтов в тексте некоторых запросов.

Получить полный набор хинтов, реализующий необходимый план, довольно просто – для этого достаточно иметь правильно работающую версию запроса либо правильную версию плана запроса в AWR (см. DBA_HIST_SQL_PLAN). Затем необходимо каким-то образом применить эти хинты к исходному проблемному запросу. Далее будет рассмотрено четыре способа, с помощью которых можно это осуществить.

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

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

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

Способ №1. Использование функционала SQL Plan Management. Создание SQL plan baseline.

Способ, который задействует функционал SQL Plan Management, может быть применен в Oracle 11 Enterprise Edition. План настроенного запроса-образца с помощью процедуры dbms_spm.load_plan_from_cursor_cache загружается в качестве плана для настраиваемого запроса, текст которого явно указывается в соответствующем параметре процедуры. При этом создается SQL plan baseline.

В примере, представленном ниже, подразумевается, что параметр optimizer_capture_sql_plan_baselines имеет значение false (в этом случае SQL baseline для каждого запроса автоматически не создается). Необходимы следующие привилегии:

Скрипт 1.1 Привилегии для выполнения скрипта с использованием SQL Baselines:

Скрипт 1.2. Пример настройки с использованием SQL plan baseline:

В разделе Note отчета dbms_xplan.display указывается используемый SQL plan baseline.

Название SQL plan baseline для запроса отражается также в поле V$SQL.SQL_PLAN_BASELINE.

В Oracle 11 SE скрипты выполнятся, однако никакого эффекта на план запроса не окажут.

Cледует иметь в виду, что для такого типа запросов, как multitable insert, невозможно создать SQL plan baseline, см., например: http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/#comment-49382

Способ №2. Создание SQL патчей.

Еще один способ изменения планов запросов без правки исходного кода в Oracle 11g – использование функционала SQL Repair Advisor, а именно – создание SQL патчей, которые предназначены для изменения планов запросов в случае непредвиденных проблем. Принцип использования в нашем случае такой же как и ранее – получение набора хинтов настроенного запроса и создание на их основе SQL патча для проблемного запроса. Для создания SQL патча необходимы права на вызов пакета sys.dbms_sqldiag_internal.

Скрипт 2.1. Привилегии для использования SQL патчей

Скрипт 2.2 показывает, как можно автоматизировать создание SQL патча:

Для просмотра результата выполним следующие команды:

Следует заметить, что используемые в SQL патче хинты должны быть именно в системном виде. При попытке использовать в SQL патче хинт в виде /*+ full(drop_tbl) */ патч будет создан, но план запроса не изменится.

SQL Repair Advisor является бесплатной опцией Oracle EE. Однако, функционалом создания SQL патчей удается воспользоваться и в других редакциях Oracle 11g, включая XE.

Способ №3. Настройка с использованием SQL profiles.

Скрипт 3.1. Привилегии для использования профилей.

Скрипт 3.2. Пример использования настройки по образцу с помощью SQL profiles

В разделе Note отчета указано имя используемого SQL profile. В примере используется категория профилей DEFAULT. При необходимости, можно изменить ее наименование. Категория профилей по умолчанию задается параметром sqltune_category:

Название SQL профиля для запроса отражается также в поле V$SQL.SQL_PROFILE.

Иногда бывает также необходимо отменить действие хинтов, входящих в текст запроса.

Скрипт 3.3 Отмена действия хинтов

Способ №4. Редактирование private outline и создание на его основе public outline.

Наконец, четвертый способ настройки основан на функционале Stored Outlines и заключается в подмене хинтов private outline проблемного запроса хинтами настроенного запроса и последующего создания public outline. Особенно актуален такой вариант для версии Standard Edition, где Plan Stability является доступной опцией. Этот способ настройки работает во всех редакциях версий 9,10,11.

Итак, допустим, нужно оптимизировать выполнение некоего запроса с помощью outline. План действий следующий:

Следует заметить, что замена содержимого OL$HINTS (временная таблица, отличающаяся от одноименной таблицы схемы OUTLN) в данном примере осуществляется без редактирования заголовка шаблона в OL$ ( в частности, не меняется число хинтов).

Скрипт 4.1. Предоставление привилегий для работы с outlines.

Скрипт 4.2 пошагово реализует изложенный выше план.

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

Таблицы, к которым ведут синонимы OL$, OL$HINTS, OL$NODES в Oracle 10 и Oracle 11 являются временными таблицами, и поскольку их содержимое очищается, необходимо выполнить весь скрипт в одной сессии.

Для удаления настроенного outline нужно выполнить команду:

Скрипт 4.3 объединяет шаги предыдущего скрипта в один PL/SQL скрипт:

При проверке работы настроенного запроса нужно не забывать устанавливать категорию для outline, если она не установлена на уровне инстанса.

Большим достоинством этого метода является то, что он подходит для разных версий и редакций Oracle – 9, 10, 11, EE, SE, XE. Недостатком этого способа, помимо большей сложности скрипта, является то, что в 11g функционал Stored Outlines официально объявлен устаревшим. В документации outlines настоятельно рекомендуется заменить на SQL plan baselines.

Выводы

Как было показано, в разных редакциях разных версий Oracle всегда найдется способ исправить план неправильно выполняющегося запроса без изменения кода, для этого имеются достаточно широкие возможности. В перспективе хотелось бы видеть, чтобы функционал SQL Plan Baselines работал не только в Enterprise Edition. Объявление функционала Stored Outlines устаревшим логически требует его замены на функционал SQL Plan Management во всех редакциях Oracle. Это было бы самым удобным вариантом для пользователей.

Таблица 1. Рассмотренные способы настройки и версии Oraсle.

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

Источник