Oracle mechanics

01.07.2012

Использование SPM в практических приложениях

Filed under: CBO,heuristics,Oracle,Oracle new features,Plan Management — Игорь Усольцев @ 11:09
Tags: , ,

мем от Ивана Постникова

Теоретически в Oracle 11g планы выполнения запросов со связанными переменными могут меняться под влиянием Adaptive Cursor Sharing (ACS) и не должны быть объектом технологии Cardinality Feedback (CF)

При этом из почти официального описания SPM and adaptive cursor sharing:

When you have a SQL plan baseline for a statement, the SPM aware optimizer will select the best accepted plan as the optimal plan

можно сделать вывод, что созданные в бд и фиксированные/одобренные для конкретного запроса Baseline, SQL Patch (составляющие SQL Plan Management) должны иметь логичный и неоспоримый приоритет при выборе оптимизатором плана выполнения

Далее — несколько практических наблюдений/тестов из области ОЁБС (OEBS aka Oracle Applications), выполненных с целью фиксации плана выполнения с использованием SPM. из которых можно заключить, что в реальных приложениях взаимодействие ACS/CF и SPM не так просто, как в теории

Итак, запрос со связанными переменными начинает медленно выполняться, при этом никаких изменений ни со стороны приложения, ни со стороны бд не производилось, просто недавно обновили Oracle на 11.2 (с вытекающими проявлениями новых фич)

Запрос к V$SQL показывает, курсор с CHILD_NUMBER=2, изменив план, стал выполняться медленнее – 556 вместо 0,15 секунд:

11.2.0.3.@ SQL> select s.sql_text,
  2         s.child_number as CHILD,
  3         s.plan_hash_value,
  4         s.optimizer_cost,
  5         round(s.elapsed_time / executions) as ELAPSED_BY_EXEC,
  6         s.IS_BIND_SENSITIVE, s.IS_BIND_AWARE, s.IS_SHAREABLE,
  7         s.EXECUTIONS
  8    from v$sql s
  9   where b.sql_id = '7h32p3yhunfaj'
 10  /

SQL_TEXT                                           CHILD PLAN_HASH_VALUE OPTIMIZER_COST  ELAPSED_BY_EXEC IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS
-------------------------------------------------- ----- --------------- --------------- --------------- ----------------- ------------- ------------ ----------
SELECT COUNT (*) FROM VIEW1 WHERE HEADER_ID =  :b1     0      3023217270     2171894              166895 Y                 N             N                    49
SELECT COUNT (*) FROM VIEW1 WHERE HEADER_ID =  :b1     2      2690279671    76473687           556619257 Y                 N             Y                    75

Причины можно увидеть в V$SQL_SHARED_CURSOR:

SQL> @V$SQL_SHARED_CURSOR 7h32p3yhunfaj

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- ------------------------------------------------------------
7h32p3yhunfaj      0 Y                  N                N                Optimizer mismatch(13)  |
7h32p3yhunfaj      2 N                  N                N                Statistics Row Source mismatch(0)  |

или в секции Note плана выполнения:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '7h32p3yhunfaj','2','all allstats advanced last'));
...
Note
-----
   - cardinality feedback used for this statement

– налицо все признаки применения cardinality feedback, по определению предназначеной для запросов без связанных переменных! В то же время, судя по V$SQL.IS_BIND_AWARE=N технология Bind-Aware Cursor Sharing (BACS) для этого запроса не применяется

Т.о. для запросов со связанными переменными Oracle может применять наряду с традиционной Adaptive Cursor Sharing (ACS) и технологию  Cardinality Feedback

Для фиксации плана выполнения был создан Baseline из курсора с быстрым планом выполнения и PLAN_HASH_VALUE=3023217270, отключив в Baseline использование cardinality feedback добавлением подсказки /*+ OPT_PARAM(‘_optimizer_use_feedback’ ‘false’) */

Через некоторое время после создания и успешного использования фиксированного Baseline запрос вновь мог начинать выполняться с «плохим» планом. При этом наблюдалось поведение, похожее на проявление другой передовой технологии Bind-Aware Cursor Sharing:

  • Bind mismatch(25) как причине генерации нового курсора
  • но в то же время IS_BIND_AWARE=N

, что более естественно для запроса со связанной переменной, однако Baseline при этом не всегда используется:

SQL> @sql 7h32p3yhunfaj

CHILD OPTIMIZER_COST PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE SQL_PLAN_BASELINE              EXECUTIONS
----- -------------- --------------- ----------------- ------------- ------------ ------------------------------ ----------
    0       54283026       921676850 Y                 N             Y                                                   27
    4        1464633      3023217270 Y                 N             N            SQL_PLAN_cx7t606tv57km47640326          7

SQL> @sql_shared_cursor 7h32p3yhunfaj

SQL_ID        CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ----- ------------------ ---------------- ---------------- ---------------------------------------------
7h32p3yhunfaj     0 N                  N                Y                NLS Settings(0)  |
7h32p3yhunfaj     4 N                  Y                N                Bind mismatch(25)  |  extended_cursor_sharing

А после добавления к Baseline подсказки /*+ NO_BIND_AWARE*/ с целью отключения BASC либо генерируются многочисленные курсоры по причине Cardinality Feedback:

SQL> @v$sql

CHILD OPTIMIZER_COST PLAN_HASH_VALUE OBJECT_STATUS       IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_PER_EXEC ROWS_PER_EXEC
----- -------------- --------------- ------------------- ----------------- ------------- ------------ ------------------------------ ---------- ---------------- -------------
    1        1464633      3023217270 INVALID_UNAUTH      Y                 N             N            SQL_PLAN_cx7t606tv57km47640326          2            0,146             1
    3        1464633      3023217270 INVALID_UNAUTH      Y                 N             N            SQL_PLAN_cx7t606tv57km47640326          1            0,182             1
    5        1464633      3023217270 VALID               Y                 N             Y            SQL_PLAN_cx7t606tv57km47640326          5            0,149             1

SQL> @v$sql_shared_cursor 7h32p3yhunfaj

SQL_ID        CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ----- ------------------ ---------------- ---------------- ------------------------------------------------------------
7h32p3yhunfaj     1 Y                  N                N                Optimizer mismatch(13)  |
7h32p3yhunfaj     3 Y                  N                N                Optimizer mismatch(13)  |
7h32p3yhunfaj     5 N                  N                Y                NLS Settings(0)  |

SELECT * FROM TABLE(dbms_xplan.display_cursor( '7h32p3yhunfaj','5','basic advanced last'));

Note
-----
   - SQL plan baseline SQL_PLAN_cx7t606tv57km47640326 used for this statement
   - cardinality feedback used for this statement

— и как бы совместно используются и Baseline, и CF

либо запрос опять же мог выполняться без использования созданного Baseline — по причине CF:

CHILD OPTIMIZER_COST PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE SQL_PLAN_BASELINE              EXECUTIONS
----- -------------- --------------- ----------------- ------------- ------------ ------------------------------ ----------
    0        1464633      3023217270 Y                 N             N            SQL_PLAN_cx7t606tv57km47640326          2
    1       54283026       921676850 Y                 N             Y                                                    1

SQL> @shared_cu 7h32p3yhunfaj

SQL_ID        CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ----- ------------------ ---------------- ---------------- ---------------------------------------------------
7h32p3yhunfaj     0 Y                  N                N                Optimizer mismatch(13)  |
7h32p3yhunfaj     1 Y                  N                N                Optimizer mismatch(13)  |

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '7h32p3yhunfaj','1','basic advanced last'));
-----
   - cardinality feedback used for this statement

Складывается впечатление, что Oracle сначала фиксирует Statistics Row Source mismatch | Optimizer mismatch(13) как причины повтовного неиспользования курсора, создаёт новый дочерний курсор под флагом CF, а затем обнаруживает наличие Baseline с фиксированным планом выполнения – в результате чего либо не используется Baseline ( в статусе ACCEPTED & FIXED), либо используется Baseline но с созданием дублирующих курсоров — в любои случае поведение нестабильно и нужный план завиксировать не удалось

При этом:

  • вдвойне интересно то, что к одному и тому же курсору может применяться и cardinality feedback, и Bind-Aware Cursor Sharing
  • Baseline, созданный из курсора, с добавленными хинтами /*+ opt_param(_optimizer_use_feedback false) no_bind_aware*/ не РАБОТАЕТ, хотя трейс из процедуры dbms_sqldiag.dump_trace рапортует от обратном (т.е. теоретически работать должен, т.к. процедура делает новый «чистый» разбор SQL — hard parse):
SQL> exec dbms_sqldiag.dump_trace('7h32p3yhunfaj',2,'Compiler','My_CBO_7h32p3yhunfaj');
...
SPM: statement found in SMB
...
SPM: cost-based plan found in the plan baseline, planId = 1197736742
SPM: cost-based plan successfully matched, planId = 1197736742
...
Content of other_xml column
===========================
  db_version     : 11.2.0.3
  parse_schema   : APPS
  plan_hash      : 3023217270 -- при чистом разборе выбран "правильный" план из Baseline

А вот при реальном выполнении запроса на определённом шаге оптимизатор решает что план из Baseline невозможно воспроизвести:

SPM: statement found in SMB
 ...
 SPM: planId in plan baseline = 1197736742, planId of reproduced plan = 4139785265
 ------- START SPM Plan Dump -------
 SPM: failed to reproduce the plan using the following info:
 ...
 SPM: generated non-matching plan:
 ...

и генерирует по новой Cost-Based план

Причина, видимо в том что компилировать план Oracle начинает со значениями параметра оптимизатора по умолчанию, в частности:

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
...
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
...
_optimizer_use_feedback             = true

, после чего принимается решение о применении или неприменении cardinality feedback, и только после этого смотрят в содержимое Baseline:

SPM: planId in plan baseline = 1197736742, planId of reproduced plan = 4139785265
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : APPS
  plan_baseline signature  : 14134582402899276068
  plan_baseline plan_id    : 1197736742
  plan_baseline hintset    :
...
    hint num  4 len 44 text: OPT_PARAM('_optimizer_use_feedback' 'false')

– соответственно, к этому моменту изменение параметра подсказкой /*+ OPT_PARAM(‘_optimizer_use_feedback’ ‘false’) */, добавленная в Baseline, уже слабо влияет на решение оптимизатора

Что подтверждается, например тем что отключение cardinality feedback на уровне сессии/системы по-прежнему прекрасно работает:

SQL> alter session set "_optimizer_use_feedback" = false;

– запрос выполняется, Baseline отлично работает, и доп.курсоры не генерятся, никакие доп.технологии (BACS, CF) не используются:

SQL> @v$sql 7h32p3yhunfaj

INST_ID CHILD OPTIMIZER_COST PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_PER_EXEC ROWS_PER_EXEC
------- ----- -------------- --------------- ----------------- ------------- ------------ ------------------------------ ---------- ---------------- -------------
      1     0        1464633      3023217270 Y                 N             Y            SQL_PLAN_c8a0ztuknsw9447640326         19            0,126             1

SQL> @v$sql_shared_cursor 7h32p3yhunfaj

INST_ID SQL_ID        CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------- ------------- ----- ------------------ ---------------- ---------------- ------------------------------------------------------------
      1 7h32p3yhunfaj     0 N                  N                N                Optimizer mismatch(13)  |

— тут Optimizer mismatch(13) — унаследованная причина)

В качестве попытки зафиксировать план пробовал также использовать технологию SQL Patch для «альтернативного» добавления подсказок /*+ opt_param(_optimizer_use_feedback false) no_bind_aware*/

begin
  for reco in (select sql_fulltext from v$sqlarea where sql_id = '7h32p3yhunfaj')
    loop
    dbms_sqldiag_internal.i_create_patch(
      sql_text => reco.sql_fulltext,
      hint_text => 'no_bind_aware opt_param(''_optimizer_use_feedback'' ''false'')',
      name => 'NO_CF_NO_BACS');
    end loop;
end;

— но поскольку SQL Patch также как и Baseline является частью SQL Plan Management (SPM), и применяется на том же уровне генерации плана, 100% фиксировать план по-прежнему не удаётся

Стабильным вариантом фиксации плана по моим наблюдениям является создание Baseline из условно хорошего плана 3023217270 с последующим добавлением в Baseline хинта /*+ opt_param(optimizer_features_enable 10.2.0.4)*/ – предположительно, ввиду сакральной связи конфликтующих технологий со значением OFE %)

При этом список подсказок, составляющих такой Baseline, очень интересен:

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 sys.sqlobj$data od, sys.sqlobj$ o
  5                    where o.obj_type = 2
  6                      and od.obj_type = 2
  7                      and o.name = 'SQL_PLAN_cx7t606tv57km47640326'
  8                      and o.signature = od.signature
  9                      and o.plan_id = od.plan_id
 10                      and comp_data is not null)) d
 11  /

OUTLINE_HINTS
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')                            -- понижение OFE
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.3')              -- включение фич оптимизатора 11.2.0.3
OPT_PARAM('_optimizer_null_aware_antijoin' 'true')               -- по отдельности, отдельными подсказками
OPT_PARAM('_optimizer_extend_jppd_view_types' 'true')
OPT_PARAM('_replace_virtual_columns' 'true')
OPT_PARAM('_first_k_rows_dynamic_proration' 'true')
OPT_PARAM('_bloom_pruning_enabled' 'true')
OPT_PARAM('_optimizer_multi_level_push_pred' 'true')
OPT_PARAM('_optimizer_group_by_placement' 'true')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'simple')     -- включая параметры Extended Cursor Sharing
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'true')           -- Adaptive Cursor Sharing
OPT_PARAM('_optimizer_improve_selectivity' 'true')
OPT_PARAM('_optimizer_enable_density_improvements' 'true')
OPT_PARAM('_optimizer_native_full_outer_join' 'force')
OPT_PARAM('_optimizer_enable_extended_stats' 'true')
OPT_PARAM('_nlj_batching_enabled' 1)
OPT_PARAM('_optimizer_extended_stats_usage_control' 192)
OPT_PARAM('_bloom_folding_enabled' 'true')
OPT_PARAM('_optimizer_coalesce_subqueries' 'true')
OPT_PARAM('_optimizer_fast_pred_transitivity' 'true')
OPT_PARAM('_optimizer_fast_access_pred_analysis' 'true')
OPT_PARAM('_optimizer_unnest_disjunctive_subq' 'true')
OPT_PARAM('_optimizer_unnest_corr_set_subq' 'true')
OPT_PARAM('_optimizer_distinct_agg_transform' 'true')
OPT_PARAM('_aggregation_optimization_settings' 0)
OPT_PARAM('_optimizer_connect_by_elim_dups' 'true')
OPT_PARAM('_optimizer_eliminate_filtering_join' 'true')
OPT_PARAM('_connect_by_use_union_all' 'true')
OPT_PARAM('_optimizer_join_factorization' 'true')
OPT_PARAM('_optimizer_use_cbqt_star_transformation' 'true')
OPT_PARAM('_optimizer_table_expansion' 'true')
OPT_PARAM('_and_pruning_enabled' 'true')
OPT_PARAM('_optimizer_distinct_placement' 'true')
OPT_PARAM('_optimizer_use_feedback' 'true')                      -- + Cardinality Feedback
OPT_PARAM('_optimizer_try_st_before_jppd' 'true')
OPT_PARAM('_optimizer_interleave_jppd' 'true')
OPT_PARAM('_px_partition_scan_enabled' 'true')
OPT_PARAM('_optimizer_false_filter_pred_pullup' 'true')
OPT_PARAM('_optimizer_enable_table_lookup_by_nl' 'true')
OPT_PARAM('_optimizer_full_outer_join_to_outer' 'true')
ALL_ROWS
...

В таком варианте план фиксируется надёжно, правда в 2-х вариантах:

 INST_ID CHILD_NUMBER OPTIMIZER_COST PLAN_HASH_VALUE OBJECT_STATUS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_PER_EXEC
 ------- ------------ -------------- --------------- ------------- ----------------- ------------- ------------ ------------------------------ ---------- ----------------
       1            1        1464633      3023217270 VALID         Y                 Y             Y            SQL_PLAN_cx7t606tv57km47640326         10            0,122
       1            2       56546016      3023217270 VALID         N                 N             Y            SQL_PLAN_cx7t606tv57km47640326        338            0,152
       1            3       56633212      3023217270 VALID         N                 N             Y            SQL_PLAN_cx7t606tv57km47640326         49            0,115
       1            5       56633212      3023217270 VALID         N                 N             Y            SQL_PLAN_cx7t606tv57km47640326         17            0,153
       2            1        1464633      3023217270 VALID         Y                 N             Y            SQL_PLAN_cx7t606tv57km47640326          6            0,128
       2            2       56546016      3023217270 VALID         N                 N             Y            SQL_PLAN_cx7t606tv57km47640326        433            2,173
       2            3       56633212      3023217270 VALID         N                 N             Y            SQL_PLAN_cx7t606tv57km47640326        108            0,178
       2            4        1466728      3023217270 VALID         Y                 N             Y            SQL_PLAN_cx7t606tv57km47640326         35            0,116

– один и тот же план 3023217270 со стоимостями 1,4М (рассчитанный по правилам оптимизатора 11.2) и 56М (по правилам 10.2) – но в рассматриваемом случае значение имеет только надёжная фиксация быстрого плана, что и получено в итоге

Причинах генерации курсоров — штатные — устаревшая статистика (Rolling Invalidate Window Exceeded) и NLS Settings — никаких посторонних примесей типа  Cardinality Feedback & Cursor Sharing не встречаются:)

SQL> @v$sql_shared_cursor 7h32p3yhunfaj

INST_ID SQL_ID        CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------- ------------- ----- ------------------ ---------------- ---------------- ------------------------------------------------------------
      1 7h32p3yhunfaj     1 N                  N                Y                Rolling Invalidate Window Exceeded(2)  |  already_processed
      1 7h32p3yhunfaj     2 N                  N                N                Rolling Invalidate Window Exceeded(2)  |  already_processed
      1 7h32p3yhunfaj     3 N                  N                N                NLS Settings(0)  |
      1 7h32p3yhunfaj     5 N                  N                N                NLS Settings(0)  |
      2 7h32p3yhunfaj     1 N                  N                N                Rolling Invalidate Window Exceeded(2)  |  already_processed
      2 7h32p3yhunfaj     2 N                  N                N                Rolling Invalidate Window Exceeded(2)  |  already_processed
      2 7h32p3yhunfaj     3 N                  N                N                NLS Settings(0)  |
      2 7h32p3yhunfaj     4 N                  N                N                NLS Settings(0)  |

UPD. Причиной подобного нестабильного поведения обычно является использование в запросе кроме связанных  переменных, значений публичных переменных PL/SQL пакетов, т.е. параметризованных обзоров

В таком случае логичным выглядит применение обеих технологий: и CF, и BACS, однако остаётся проблема  надёжной фиксации плана с помощью SPM Baseline

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

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

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

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