При реальном выполнении запроса, во время hard parse, происходит peeks bind values.
Explain plan и SQL*Plus autotrace - do not peek bind values, когда они порождают планы.
А также 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%';
Получить план можно так:
По hash_value:
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;
Или по sql_id:
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;
basic - отображает минимальную информацию о плане выполнения.
- id операции
- имя операции
- опции
typical (default) - отображает самую актуальную информацию о плане выполнения.
Тоже что и basic плюс
- #rows
- #bytes
- cost
Также отображается информация
- parallel
- predicate
Исключается тоько информация
- projection
- alias
- remote sql
serial - отображает тоже что и typical
Но не отображает в плане
- parallel
даже если запрос выполняется параллельно.
all - максимальный уровень
Тоже что и typical плюс
- projection
- alias
- remote sql
advanced - отображает тоже что и all
Плюс информацию об используемых Outline (наборы подсказок)
из поля other_xml представления v$sql_plan_statistics_all
adaptive - отображает всё
плюс все потенциальные варианты адаптивного плана выполнения курсора
Примеры:
format => 'basic'
format => 'ALL +peek_binds -alias -predicate'
(т.е. можно добавлять или удалять выводимую информацию)
Например к уровню basic можно добавить вывод дополнительных столбцов столбцов:
format => 'basic +rows +bytes +cost +parallel +partition'
Хотя уровнь all всё это и так включает.
basic +rows +bytes +cost +parallel = typical без time
typical +projection +alias +remote = all
all +outline = advanced
Дополнительно можно добавить
+peeked_binds - выводит дополнительную секцию со значениями связанных переменных,
которые оптимизатор учитывает при подготовке плана выполнения.
+report - для адаптивного плана
+metrics - для 12с могут быть показаны спискм доступных/используемых SQL Plan Directives
Чтобы дополнительно добавить столбцы со статистикой ввода-вывода и использования памяти нужно добавить
+iostats
+memstats
или просто 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'));
starts - количество выполнений операции
e-rows - ожидаемое количество строк при каждом выполнении операции
e-time - ожидаемое время выполнения
a-rows - наблюдаемое суммарное количество строк за время всех выполнений операций
a-time - наблюдаемое время выполнения
При добавлении дополнительных столбцов со статистикой ввода-вывода и использования памяти (allstats)
появятся следующие поля:
buffers - consistent gets + current gets
reads - physikal reads
0Mem - сколько потребуется памяти для обработки курсора полностью в памяти (v$sql_workarea.estimated_optimal_size)
1Mem - сколько потребуется памяти для обработки курсора за один проход (v$sql_workarea.estimated_onepass_size)
used-mem - реальное значение памяти использованной в течении последнего выполнения курсора (v$sql_workarea.last_memory_used)
Для получения столбцов со статистикой ввода-вывода (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'));
|Id|Operation|Name|Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|
Иначе в плане мы не увидим некоторых столбцов:
|Id|Operation|Name|E-Rows|E-Bytes|Cost (%CPU)|E-Time|OMem|1Mem|Used-Mem|
Хинт 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;
смотрим план:
select * from table (dbms_xplan.display_cursor('0vx5dc8jftang',1,'ADVANCED ALLSTATS LAST +peeked_binds +report +metrics'));
Explain plan и SQL*Plus autotrace - do not peek bind values, когда они порождают планы.
А также 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%';
Получить план можно так:
По hash_value:
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;
Или по sql_id:
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 управляет уровнем детализации плана.
basic - отображает минимальную информацию о плане выполнения.
- id операции
- имя операции
- опции
typical (default) - отображает самую актуальную информацию о плане выполнения.
Тоже что и basic плюс
- #rows
- #bytes
- cost
Также отображается информация
- parallel
- predicate
Исключается тоько информация
- projection
- alias
- remote sql
serial - отображает тоже что и typical
Но не отображает в плане
- parallel
даже если запрос выполняется параллельно.
all - максимальный уровень
Тоже что и typical плюс
- projection
- alias
- remote sql
advanced - отображает тоже что и all
Плюс информацию об используемых Outline (наборы подсказок)
из поля other_xml представления v$sql_plan_statistics_all
adaptive - отображает всё
плюс все потенциальные варианты адаптивного плана выполнения курсора
Примеры:
format => 'basic'
format => 'ALL +peek_binds -alias -predicate'
(т.е. можно добавлять или удалять выводимую информацию)
Например к уровню basic можно добавить вывод дополнительных столбцов столбцов:
format => 'basic +rows +bytes +cost +parallel +partition'
Хотя уровнь all всё это и так включает.
basic +rows +bytes +cost +parallel = typical без time
typical +projection +alias +remote = all
all +outline = advanced
Дополнительно можно добавить
+peeked_binds - выводит дополнительную секцию со значениями связанных переменных,
которые оптимизатор учитывает при подготовке плана выполнения.
+report - для адаптивного плана
+metrics - для 12с могут быть показаны спискм доступных/используемых SQL Plan Directives
Чтобы дополнительно добавить столбцы со статистикой ввода-вывода и использования памяти нужно добавить
+iostats
+memstats
или просто 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'));
Значения столбцов статистики плана выполнения
starts - количество выполнений операции
e-rows - ожидаемое количество строк при каждом выполнении операции
e-time - ожидаемое время выполнения
a-rows - наблюдаемое суммарное количество строк за время всех выполнений операций
a-time - наблюдаемое время выполнения
При добавлении дополнительных столбцов со статистикой ввода-вывода и использования памяти (allstats)
появятся следующие поля:
buffers - consistent gets + current gets
reads - physikal reads
0Mem - сколько потребуется памяти для обработки курсора полностью в памяти (v$sql_workarea.estimated_optimal_size)
1Mem - сколько потребуется памяти для обработки курсора за один проход (v$sql_workarea.estimated_onepass_size)
used-mem - реальное значение памяти использованной в течении последнего выполнения курсора (v$sql_workarea.last_memory_used)
Для получения столбцов со статистикой ввода-вывода (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'));
|Id|Operation|Name|Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|
Иначе в плане мы не увидим некоторых столбцов:
|Id|Operation|Name|E-Rows|E-Bytes|Cost (%CPU)|E-Time|OMem|1Mem|Used-Mem|
Хинт 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;
смотрим план:
select * from table (dbms_xplan.display_cursor('0vx5dc8jftang',1,'ADVANCED ALLSTATS LAST +peeked_binds +report +metrics'));
