Актуальный план выполнения запроса
При анализе конкретного SQL запроса нужно выяснить, какой план выполнения в действительности используется Oracle, т.к. результат выполнения команды explain plan (в т.ч. при установке опции SQL*Plus AUTOTRACE ON EXPLAIN – см., например, EXPLAIN PLAN Restrictions), даёт только оценку возможного плана выполнения и может существенно отличаться от плана запроса, который будет в действительности использован при выполнении запроса.
1. Из обзора V$SQL_PLAN, например, так:
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 = &hash and ADDRESS = '&addr' and CHILD_NUMBER = &child_number
sql_id = '&sql_id' and child_number = &child_number) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;
План выполнения уникально определяется комбинациями значений (HASH_VALUE, ADDRESS, CHILD_NUMBER) либо (SQL_ID, CHILD_NUMBER) из V$SQL
QBLOCK_NAME – название блока запроса, используется в некоторых хинтах оптимизатором в outline section плана выполнения
2. Используя пакет DBMS_XPLAN (источником данных о плане является тот же обзор V$SQL_PLAN) сразу после выполнения запроса:
SQL> select sysdate from dual; ... SQL> select * from table(dbms_xplan.display_cursor(null,null,'BASIC LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- Plan hash value: 1388734953 --------------------------------- | Id | Operation | Name | --------------------------------- | 0 | SELECT STATEMENT | | | 1 | FAST DUAL | | ---------------------------------
, либо по значениям (v$sql.sql_id, v$sql.child_number):
SELECT * FROM TABLE(dbms_xplan.display_cursor('&sql_id',
'&child_number',
'ADVANCED ALLSTATS LAST'));
3. из трейс файлов (event 10053, 10132):
alter session set events '10053 trace name context forever'; SELECT/*+ gather_plan_statistics*/ ...; alter session set events '10053 trace name context off';
4. Из репозитория AWR:
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 = '&sql_id'
and plan_hash_value = &plan_hash_value) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0
- вплоть до 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, если запрос мониторится:
SELECT lpad(' ', 2 * level) || pt.plan_operation || ' ' || pt.plan_options || ' ' ||
pt.plan_object_name "Query Plan",
pt.plan_cost,
pt.plan_cardinality,
pt.plan_bytes,
pt.plan_cpu_cost,
pt.plan_io_cost,
pt.plan_temp_space,
pt.starts,
pt.output_rows,
pt.workarea_mem,
pt.workarea_tempseg,
pt.physical_read_requests,
pt.physical_write_requests
FROM (select *
from v$sql_plan_monitor pt
where sql_id = '&sql_id'
-- and sql_plan_hash_value = &sql_plan_hash_value
-- and sid = &sid
-- and sql_exec_id = &sql_exec_id
and status = 'EXECUTING') pt
CONNECT BY PRIOR pt.plan_line_id = pt.plan_parent_id
START WITH pt.plan_line_id = 0
Значения связанных переменных и констант, используемые в запросе
Кроме SQL трейса (event 10046, level 4/12), можно найти в обзоре v$sql_bind_capture, например так:
select
c.child_address, c.address, c.name, c.DATATYPE_STRING,
DECODE(c.WAS_CAPTURED, 'YES', c.value_string,
'['||c.value_string||']:NOT_CAPTURED') bind_var
from v$sql a,
v$sql_bind_capture c
where c.child_address=a.child_address
and c.hash_value = a.HASH_VALUE
and a.SQL_ID = '&my_sql_id';
Обзор 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:
select * from xmltable
('/binds/bind' passing
(select xmltype(binds_xml) as xmlval
from v$sql_monitor
where binds_xml is not null
and key = &key
and sql_id = '&sql_id')
columns
"Name" varchar2(30) path '@name',
"Position" varchar2(10) path '@pos',
"Type" varchar2(30) path '@dtystr',
"MaxLength" varchar2(30) path '@maxlen',
"Value" varchar2(1000) PATH '/bind')
/
Подсказки плана выполнения, используемые CBO при выполнении запроса (outline section)
select
substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval
from v$sql_plan
where sql_id = '&sql_id'
and child_number = &child_number
and plan_hash_value = &plan_hash_value
and other_xml is not null)
) d
/
Методы Oracle для модификации и фиксации существующих планов выполнения
SQL Profiles
«В отличие от Stored Outlines, подсказки, используемые в SQL Profiles не пытаются диктовать механизм испольнения запроса (операции доступа к данным) напрямую. Вместо этого они устанавливают арифметические корректирующие коэффициенты для оптимизатора…»
т.е. SQL Profiles не пытается влиять на план выполнения запроса “напрямую”, подсказывая способы доступа к объектам БД (использовать ли индекс – index range scan или FULL TABLE SCAN). Этот инструмент с помощью специальных хинтов (вычисленных на основании статистики реального выполнения запроса, используя технологию cardinality feedback) предоставляет CBO коэффициенты для расчётов и выбора “откорректированного жизнью” плана выполнения. Коэффициенты эти, конечно же, со временем могут стать неактуальными и может потребоваться повторное выполнение процедур подготовки SQL Profiles
Пример применения:
DECLARE
my_sql_id VARCHAR2(30) := &sql_id;
my_task_name VARCHAR2(30);
BEGIN
begin
DBMS_SQLTUNE.DROP_TUNING_TASK(my_sql_id);
exception when others then NULL;
end;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => my_sql_id,
--sql_text => 'select ...', --if SQL not in shared pool--
scope => 'COMPREHENSIVE',
time_limit => 600,
task_name => my_sql_id,
description => 'SQL analysis for SQL_ID=' || my_sql_id);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => my_task_name);
END;
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( &sql_id ) FROM DUAL;
begin
dbms_sqltune.accept_sql_profile(task_name => &sql_id,
replace => TRUE,
force_match => true);
end;
Перечень уже применяемых в профилях хинтов можно увидеть в документально неупоминаемом обзоре SYS.DBMSHSXP_SQL_PROFILE_ATTR:
select attr_number, attr_value as cbo_hint from DBMSHSXP_SQL_PROFILE_ATTR where profile_name = &SQL_PROFILE_NAME;
Перечень рекомендованных хинтов после выполнения DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => &sql_tuning_task_name ); – из обсуждения в блоге J .Lewis’а:
select
rat.attr1
from
sys.wri$_adv_tasks tsk,
sys.wri$_adv_rationale rat
where
tsk.name = &sql_tuning_task_name
and rat.task_id = tsk.id;
показывает хинты типа 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
271196.1 Automatic SQL Tuning – SQL Profiles :
“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
Примеры:
Творошенко Сергей. Использование хранимых шаблонов (stored outlines) при настройке приложений с недоступным исходным кодом
Navdeep Saini. How to force hints using Outlines
SQL PLAN BASELINES / SQL plan management (SPM)
Рекомендованный к применению метод управления планами выполнения, начиная с Oracle 11g
Идея: план выполнения генерируется на основании набора подсказок оптимизатору, хранящемуся в sys.sqlobj$data – см.скрипт Подсказки, составляющие SQL Plan Baseline, фактически, baseline = набор подсказок, применяемых на определённом, не первом этапе генерации плана выполнения. В Oracle 11g фиксация планов выполнения запросов с помощью baseline может быть затруднена / проблематична из-за влияния технологий Cardinality Feedback / Bind Aware Cursor Sharing
Неавтоматическое создание baseline для запроса, находящегося в SGA:
11.2.0.3.@SQL> SELECT USERENV('CLIENT_INFO') FROM SYS.DUAL;
USERENV('CLIENT_INFO')
----------------------
1 row selected.
11.2.0.3.@SQL> select child_number, sql_id, plan_hash_value from v$sql where sql_text like 'SELECT USERENV(''CLIENT_INFO'') FROM SYS.DUAL%';
CHILD_NUMBER SQL_ID PLAN_HASH_VALUE
------------ ------------- ---------------
0 9qs3v6bgb0xcu 1388734953
1 row selected.
11.2.0.3.@SQL> declare res number;
2 begin
3 res := dbms_spm.load_plans_from_cursor_cache(sql_id => '9qs3v6bgb0xcu', plan_hash_value => '1388734953' );
4 end;
5 /
PL/SQL procedure successfully completed.
11.2.0.3.@SQL> select * from dba_sql_plan_baselines where sql_text like '%CLIENT_INFO%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION CREATED ENA ACC FIX REP AUT OPTIMIZER_COST MODULE EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES END_OF_FETCH_COUNT
--------------------- --------------------------------------------- ------------------------------ ----------- ---------- ---------- --- --- --- --- --- -------------- ------------ ---------- ------------ -------- ----------- ---------- ------------- -------------- ------- ------------------
SQL_f666908ec8ea1293 SELECT USERENV('CLIENT_INFO') FROM SYS.DUAL SQL_PLAN_gctnhjv4fn4nm125daea2 MANUAL-LOAD 11.2.0.3.0 13-MAR-12 YES YES NO YES YES 2 sqlplus.exe 1 1039 1000 0 0 0 1 1 1
1 row selected.
Созданный baseline, как и sql patsh будут применяться к запросам после нормализации SQL текста: исключения пробелов, переводов строки и нормализации регистра кроме регистра текстовых констант (literals), например, только что созданный baseline успешно применяется к запросу:
11.2.0.3.@SQL> select userenv ( 'CLIENT_INFO' ) 2 from 3 sys.dual; PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID cgzygkgjhdz7a, child number 0 ------------------------------------- ... Plan hash value: 1388734953 ... Note ----- - SQL plan baseline SQL_PLAN_gctnhjv4fn4nm125daea2 used for this statement
Пример загрузки архивного плана из репозитория 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
SQL Patch
Метод добавления подсказок к запросу без изменения текста приложения – Using SQL Patch to add hints to a packaged application:
SQL> -- добавление хинта по sql_id | создание SQL Patch
SQL> begin
2 for reco in (select sql_fulltext from v$sqlarea where sql_id = 'f6cas59kcsb27')
3 loop
4 dbms_sqldiag_internal.i_create_patch(
5 sql_text => reco.sql_fulltext,
6 hint_text => 'no_bind_aware opt_param(''_optimizer_use_feedback'' ''false'')',
7 name => 'NO_CF_NO_BACS');
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed
SQL> -- Список
SQL> select sys.dbms_sqltune_util0.sqltext_to_sqlid(sql_text || chr(0)) sql_id,
2 sp.name,
3 sp.category,
4 sp.sql_text,
5 to_char(sp.created,'dd.mm.yyyy hh24:mi:ss') as created,
6 to_char(sp.last_modified,'dd.mm.yyyy hh24:mi:ss') as last_modified,
7 sp.description,
8 sp.status,
9 sp.force_matching
10 from dba_sql_patches sp
11 /
SQL_ID NAME CATEGORY SQL_TEXT CREATED LAST_MODIFIED DESCRIPTION STATUS FOR
------------- -------------- ---------- -------------------------------------------------------------------------------- ------------------- ------------------- ----------- -------- ---
f6cas59kcsb27 NO_CF_NO_BACS DEFAULT SELECT /* test123 */ COUNT (*) FROM XXAR_PRINT_LINES_V WHERE HEADER_ID = :b1 05.03.2012 15:49:28 05.03.2012 15:49:28 ENABLED NO
SQL> -- просмотр содержимого SQL Patch
SQL> select substr(extractvalue(value(d), '/hint'), 1, 200) as sql_patch_hints
2 from xmltable('/outline_data/hint' passing
3 (select xmltype(comp_data) as xmlval
4 from sys.sqlobj$data od, sys.sqlobj$ o
5 where od.obj_type = 3
6 and (o.name = 'NO_CF_NO_BACS' and o.obj_type = 3)
7 and o.signature = od.signature
8 and comp_data is not null)) d
9 /
SQL_PATCH_HINTS
----------------------------------------------------------
no_bind_aware opt_param('_optimizer_use_feedback' 'false')
SQL> -- удаление SQL Patch
SQL> exec dbms_sqldiag.drop_sql_patch('NO_CF_NO_BACS');
Механизмы/технологии, используемые 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 Optimizer Team:
| Level (уровень) | В каких случаях будет использован Dynamic Sampling | Sample size (в блоках) |
|---|---|---|
| 0 | Отключает dynamic sampling | N/A |
| 1 | Как минимум одна непартиционированная таблица запроса не имеет статистики | 32 |
| 2 (default) | Одна или более таблиц запроса не имеют статистики | 64 |
| 3 | Все запросы, удовлетворяющие условиям уровня 2 + любые запросы, использующие выражения в условиях запроса (where clause predicates), например: Where substr(CUSTLASTNAME,1,3) or Where a + b =5 |
64 |
| 4 | Все запросы, удовлетворяющие условиям уровня 3 + любые запросы, содержащие сложные условия (complex predicates) с операторами OR или AND между множественными условиями для одной и той же таблицы |
64 |
| 5 | Все запросы, удовлетворяющие условиям уровня 4 | 128 |
| 6 | Все запросы, удовлетворяющие условиям уровня 4 | 256 |
| 7 | Все запросы, удовлетворяющие условиям уровня 4 | 512 |
| 8 | Все запросы, удовлетворяющие условиям уровня 4 | 1024 |
| 9 | Все запросы, удовлетворяющие условиям уровня 4 | 4086 |
| 10 | Любые запросы | Все блоки |
“Начиная с 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
Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback
Cardinality Feedback
Доступная с Oracle 11gR2 технология, использующая статистику исполнения запроса (rowsource statistics) для однократной модификации плана выполнения запроса, не использующего связанных переменных
“Ручные” методы настройки запросов и влияния на план выполнения
Cardinality feedback
Tuning by Cardinality Feedback – статья Wolfgang Breitling’а:
“Гипотеза (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) … Приведены наиболее свежие известные значения констант …
