Oracle mechanics

SQL запросы и CBO

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

При анализе конкретного 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 плана выполнения

В документации — определение плана выполнения запроса из shared pool (V$SQL) для Oracle 9i

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'));

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

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

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

  -------------------------------- dump_trace ---------------------------------
  -- NAME:
  --     dump_trace - Dump Optimizer Trace
  --
  -- DESCRIPTION:
  --     This procedure dumps the optimizer or compiler trace for a give SQL
  --     statement identified by a SQL ID and an optional child number.
  --
  -- PARAMETERS:
  --     p_sql_id          (IN)  -  identifier of the statement in the cursor cache
  --     p_child_number    (IN)  -  child number
  --     p_component       (IN)  -  component name
  --                                Valid values are Optimizer and Compiler
  --                                The default is Optimizer
  --     p_file_id         (IN)  -  [trace] file identifier

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

С появлением адаптивных планов выполнения в версии 12c формат V$SQL_PLAN.OTHER_XML расширился и актуально-адаптированный план может быть найден так:

SQL> select * from (select sql_id, child_number from V$SQL where is_resolved_adaptive_plan = 'Y') where rownum < 2;
 
SQL_ID        CHILD_NUMBER
------------- ------------
34m67p601800n            0
 
SQL> WITH display_map AS
  2    (SELECT X.*
  3    FROM v$sql_plan,
  4      XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml ) COLUMNS
  5        op  NUMBER PATH '@op',    -- operation
  6        dis NUMBER PATH '@dis',   -- display
  7        par NUMBER PATH '@par',   -- parent
  8        prt NUMBER PATH '@prt',   -- ?
  9        dep NUMBER PATH '@dep',   -- depth
 10        skp NUMBER PATH '@skp' )  -- skip
 11    AS X
 12    WHERE sql_id     = '&&sql_id'
 13    AND child_number = &&sql_child
 14    AND other_xml   IS NOT NULL
 15    )
 16  SELECT
 17    -- new ID, depth, parent etc from display_map
 18    NVL(m.dis, 0) AS new_id,
 19    m.par         AS new_parent,
 20    m.dep         AS new_depth,
 21    -- plan formatting, as usual
 22    lpad(' ',m.dep*1,' ') || sp.operation || ' ' || sp.OPTIONS AS operation,
 23    sp.object#,
 24    sp.object_name,
 25    sp.object_alias,
 26    sp.object_type
 27  FROM v$sql_plan sp
 28  LEFT OUTER JOIN display_map m
 29  ON (sp.id = m.op)
 30  WHERE sp.sql_Id        = '&&sql_id'
 31  AND sp.child_number    = &&sql_child
 32  AND NVL(m.skp,0)      <> 1
 33  ORDER BY NVL(dis,0)
 34  /

&sql_id: 34m67p601800n
&sql_child: 0
 
NEW_ID NEW_PARENT  NEW_DEPTH OPERATION                   OBJECT# OBJECT_NAME  OBJECT_ALIAS         OBJECT_TYPE
------ ---------- ---------- ------------------------ ---------- ------------ -------------------- ---------------
     0                       SELECT STATEMENT
     1          0          1  SORT AGGREGATE
     2          1          2   NESTED LOOPS
     3          2          3    INDEX RANGE SCAN         2259404 I_OBJ1       OBJ_TAB#4@innerQuery INDEX (UNIQUE)
     4          2          3    TABLE ACCESS CLUSTER     2259418 TAB$         TAB#3@innerQuery     CLUSTER
     5          4          4     INDEX UNIQUE SCAN       2259410 I_OBJ#       TAB#3@innerQuery     INDEX (CLUSTER)
Plan hash value

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

11.2.0.3.@SCOTT SQL> select /*+ full(EMP)*/ * from emp where deptno = 20 or deptno = 21;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932                                --***

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   266 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     7 |   266 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=20 OR "DEPTNO"=21)           -- первоначальное условие

SQL> select /*+ full(EMP)*/ * from emp where deptno = 20;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932                                -- ***

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   190 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     5 |   190 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=20)                          -- условие изменилось

SQL> -- тот же запрос к таблице пользователя SYS
SQL> select /*+ full(EMP)*/ * from SYS.emp where deptno = 20;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932                                -- ***

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   435 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     5 |   435 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=20)

Условия (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, например так:

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;

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

12.1.0.2.@ SQL> select distinct trim(dbms_lob.substr(rat.attr5, 512)) as hints
  2    from sys.wri$_adv_tasks tsk, sys.wri$_adv_rationale rat
  3   where tsk.name = '&sql_id'
  4     and tsk.advisor_id = 4
  5     and rat.task_id = tsk.id
  6     and rat.type = 'RECOMMENDATION'
  7  /

HINTS
--------------------------------------------------------------------------------------------------

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

SELECT extractvalue(value(d), '/hint/text()') as outline_hints
 from sys.DBMSHSXP_SQL_PROFILE_ATTR
CROSS JOIN XMLTABLE('/outline_data/hint' PASSING xmltype(comp_data)) d
where profile_name = '&sql_profile_name'

, либо:

12.1.0.2.@ SQL> SELECT extractvalue(value(d), '/hint/text()') as outline_hints
  2    from sys.sqlobj$data od
  3    join sys.sqlobj$ o using (signature, category, obj_type, plan_id)
  4   CROSS JOIN XMLTABLE('/outline_data/hint' PASSING xmltype(comp_data)) d
  5   where obj_type = 1
  6     and o.name = '&sql_profile_name'
  7  /
Enter value for sql_profile_name: SYS_SQLPROF_01517b91a8c9000c
old   6:    and o.name = '&sql_profile_name'
new   6:    and o.name = 'SYS_SQLPROF_01517b91a8c9000c'

OUTLINE_HINTS
-------------------------------------------------------------------------------------------
...

показывает хинты типа 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 Outline Quick Reference (Doc ID 67536.1)
Творошенко Сергей. Использование хранимых шаблонов (stored outlines) при настройке приложений с недоступным исходным кодом
Navdeep Saini. How to force hints using Outlines

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

SQL> oradebug dumpvar sga sgauso
SQL> oradebug dumpvar uga ugauso
SQL PLAN BASELINES / SQL plan management (SPM)

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

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

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

Требуемые привилегии:

SQL> grant ADMINISTER SQL MANAGEMENT OBJECT to :username;

Grant succeeded.

Пример неавтоматического создания baseline для запроса, находящегося в shared pool 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 (ещё один элемент технологии SPM — Sql Plan Management) будут применяться к запросам после нормализации SQL текста: исключения пробелов, переводов строки и нормализации регистра кроме регистра текстовых констант (literals), например, только что созданный baseline успешно применяется к запросу отличным sql_id:

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

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

declare
  n pls_integer;
  baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
--
-- Создание Baseline из плана / курсора, находящегося в Shared Pool
--
  n := dbms_spm.load_plans_from_cursor_cache(sql_id => 'c21dh0yfus13y', plan_hash_value => 733896097);
--
-- Создание нового (дополнительного) Baseline для запроса / существующего Baseline sql_handle='SQL_1b55d727681aae5f'
-- и курсора sql_id='998cduxz1rdqy', plan_hash_value=530064972, например для фиксации другого плана с помощью подсказок
--
  n := dbms_spm.load_plans_from_cursor_cache(sql_id => '998cduxz1rdqy', plan_hash_value => 530064972, sql_handle => 'SQL_1b55d727681aae5f');
--
-- создание Baseline из исторического плана / курсора, находящегося в AWR
--
  DBMS_SQLTUNE.CREATE_SQLSET('SQLSET_c21dh0yfus13y');
  open baseline_ref_cursor for
  select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
         begin_snap => 43820,                                                                              -- начальный снапшот для поиска
         end_snap =>   43832,                                                                              -- финальный снапшот
         basic_filter => 'sql_id='||CHR(39)||'c21dh0yfus13y'||CHR(39)||' and plan_hash_value = 733896097', -- условие выбора
         attribute_list => 'ALL')) p;
  DBMS_SQLTUNE.LOAD_SQLSET('SQLSET_c21dh0yfus13y', baseline_ref_cursor);
  close baseline_ref_cursor;
  n := dbms_spm.load_plans_from_sqlset( sqlset_name => 'SQLSET_c21dh0yfus13y', sqlset_owner => USER, fixed => 'YES', enabled => 'YES');
  DBMS_SQLTUNE.drop_sqlset('SQLSET_c21dh0yfus13y');
--
-- перемещение Baseline с использованием промежуточной таблицы
--
  DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'SPM_STAGE1', tablespace_name => 'USERS');
  n := DBMS_SPM.PACK_STGTAB_BASELINE(table_name => 'SPM_STAGE1', enabled => 'yes', creator => 'APPS', sql_handle => 'SQL_04252731506dfa3b',plan_name => 'SQL_PLAN_089976586vyjv74c630de');
  -- перенос таблицы в целевую бд (expdp/impdp, dblink)
  n := DBMS_SPM.UNPACK_STGTAB_BASELINE(table_name => 'SPM_STAGE1', fixed => 'yes', sql_handle => 'SQL_04252731506dfa3b',plan_name => 'SQL_PLAN_089976586vyjv74c630de');
--
-- модификация / управление Baseline
--
  n := DBMS_SPM.alter_sql_plan_baseline('SQL_04252731506dfa3b','SQL_PLAN_089976586vyjv74c630de','fixed','yes');
  n := DBMS_SPM.alter_sql_plan_baseline('SQL_04252731506dfa3b','SQL_PLAN_089976586vyjv74c630de','autopurge','no');
  n := DBMS_SPM.alter_sql_plan_baseline('SQL_04252731506dfa3b','SQL_PLAN_089976586vyjv74c630de','description','Fixed plan for task#333222');
--
-- удаление Baseline
--
  n := dbms_spm.drop_sql_plan_baseline('SQL_04252731506dfa3b', 'SQL_PLAN_267kw3nnjs02604fdefe5');
end;

SQL> -- список подсказок, составляющих Baseline, из промежуточной / stage таблицы
SQL> select substr(extractvalue(value(d), '/hint'), 1, 200) as outline_hints
  2    from xmltable('/outline_data/hint' passing
  3                  (select xmltype(comp_data) as xmlval
  4                     from SPM_STAGE1
  5                    where sql_handle = 'SQL_04252731506dfa3b'
  6                      and obj_name = 'SQL_PLAN_089976586vyjv74c630de')) d
  7  /

OUTLINE_HINTS
-------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5")
...

SQL> -- получение плана, сохранённого в Baseline
SQL> SELECT * FROM TABLE(dbms_xplan.display_sql_plan_baseline('SQL_04252731506dfa3b','SQL_PLAN_089976586vyjv74c630de','all +predicate -outline -alias -projection'));

Список подсказок, составляющих SPM Baseline

11.2.0.3.@ SQL> @bl_hints SQL_PLAN_acg49cdw0088v4085ecd2

OUTLINE_HINTS
-------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
...

Начиная с 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> -- добавление хинта по 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   FORCE_MATCHING
------------- -------------- ---------- --------- ------------------- ------------------- ----------- -------- --------------
f6cas59kcsb27 NO_CF_NO_BACS  DEFAULT    SELECT... 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, 512) 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> -- - в описании дочернего курсора
SQL> select child_number, plan_hash_value, SQL_PATCH from V$SQL where sql_id = 'bkx799gtk6r0h';

CHILD_NUMBER PLAN_HASH_VALUE SQL_PATCH
------------ --------------- ---------------------
           0      1384833288 NO INDEX 4 OUTER JOIN
           1      2051684057 NO INDEX 5 OUTER JOIN

SQL> -- - в описании плана выполнения, в столбце V$SQL_PLAN.OTHER_XML:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('f6cas59kcsb27'));
...
Note
-----
 - SQL patch "NO INDEX 4 OUTER JOIN" used for this statement

SQL> -- - в трейсе оптимизатора:
Content of other_xml column
===========================
 db_version : 11.2.0.3
 parse_schema : APPS
 plan_hash : 2051684057
 plan_hash_2 : 1417133226
 sql_patch : NO INDEX 4 OUTER JOIN     -- название патча
...
  Outline Data:                        -- в виде хинтов в секции Outline

SQL> -- удаление SQL Patch
SQL> exec dbms_sqldiag.drop_sql_patch('NO INDEX 4 OUTER JOIN');

PL/SQL procedure successfully completed.

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

Механизмы/технологии, используемые 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) для однократной модификации плана выполнения запроса, не использующего связанных переменных

Statistics Feedback

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

12.1.0.1.@ SQL> select /***/ * from v$sql_redirection; -- тест на системных обзорах

711 rows selected.                                                                      -- объективная реальность

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1688674190

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |   153 |     0   (0)| 00:00:01 |
|*  1 |  HASH JOIN        |                   |     1 |   153 |     0   (0)| 00:00:01 | -- и ошибочный расчёт Rows=1
|*  2 |   FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |   111 |     0   (0)| 00:00:01 |
|   3 |   FIXED TABLE FULL| X$KKSSRD          |  1076 | 45192 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL> -- Статистика выполнения запроса отражается в виде
SQL> select sql_id, child_number, hint_id, hint_text
  2    from v$sql_reoptimization_hints
  3   where sql_id in
  4         (select sql_id
  5            from v$sql
  6           where sql_text like 'select /***/ * from v$sql_redirection%')
  7  /

SQL_ID        CHILD_NUMBER    HINT_ID HINT_TEXT
------------- ------------ ---------- ------------------------------------------------------------------------------
9z4x0qx7dz63y            0          1 OPT_ESTIMATE (@"SEL$88122447" JOIN ("R"@"SEL$4" "C"@"SEL$4") ROWS=711.000000 )

SQL> -- эта подсказка при следующем выполнении будет добавлена непосредственно в текст запроса во время разбора (трейс 10053) в виде
SQL> -- SQL:******* UNPARSED QUERY IS *******
SQL> -- SELECT /*+ OPT_ESTIMATE (@"SEL$88122447" JOIN ("R"@"SEL$4" "C"@"SEL$4") ROWS=711.000000 ) */ ...
SQL> -- что кроме непосредственного влияния хинта стимулирует генерацию нового дочернего курсора (hard parse)

SQL> select /***/ * from v$sql_redirection;                                  -- следующее выполнение

711 rows selected.

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  9z4x0qx7dz63y, child number 1                                        -- с новым дочерним курсором
-------------------------------------
select /***/ * from v$sql_redirection

Plan hash value: 1688674190                                                  -- на этот раз план не изменился

----------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |       |       |     1 (100)|
|*  1 |  HASH JOIN        |                   |   711 |   106K|     0   (0)| -- однако уточнена JOIN CARDINALITY Rows=711
|*  2 |   FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |   111 |     0   (0)|
|   3 |   FIXED TABLE FULL| X$KKSSRD          |  1076 | 45192 |     0   (0)|
----------------------------------------------------------------------------

Note
-----
   - statistics feedback used for this statement                              -- флажок

SQL> @shared_cu 9z4x0qx7dz63y

PLAN_HASH_VALUE OPTIMIZER_COST  CHILD SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS REASON1
--------------- -------------- ------ ---------- ------------------ --------------- ----------------------------------
     1688674190              1      0 N          Y                  N               Auto Reoptimization Mismatch(1)  | -- появившаяся в 12c причина
     1688674190              1      1 Y          N                  N

SQL> --
SQL> -- Statistics Feedback легко комбинируется с другой технологией 12c - Adaptive Plan
SQL> select sql_id, child_number from v$sql_plan where other_xml like '%adaptive%cardinality%' and rownum < 2
2  /
SQL_ID        CHILD_NUMBER
------------- ------------
2yn1z1cjm51wd            2 -- найденный в Library Cache курсор с признаками совместного применения технологий

SQL> select * from table(dbms_xplan.display_cursor('2yn1z1cjm51wd', 2))
  2  /

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2yn1z1cjm51wd, child number 2
-------------------------------------
select t.*, o.*, m.comments  from sys.dba_tables t,
...
Note
-----
   - statistics feedback used for this statement -- подтверждение
   - this is an adaptive plan                    -- --//--

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

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 defaults:

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

TrackBack URI

Блог на WordPress.com.

%d такие блоггеры, как: