План запроса

При реальном выполнении запроса, во время 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;

Параметр 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'));