Oracle mechanics

02.03.2015

Стабильность SQL Plan Baseline при обновлениях

Filed under: Oracle,Plan Management,SQL Tuning — Игорь Усольцев @ 22:13
Tags:

Всё указывало на запрос 9s7ppf88qzx2w: и экспресс-анализ по пользователю/модулю доморощенным скриптом ASH_SQL_WAIT_TREE.SQL:

SQL> @ash_sql_wait_tree "client_id  = 'APPS_USER' and module = 'e:AR:frm:XXXXX'" 0
 
LVL BLOCKING_TREE  EVENT                     WAITS_COUNT EXECS_COUNT AVG_WA SQL_ID
--- -------------- ------------------------- ----------- ----------- ------ -------------
  1 (USER)         On CPU / runqueue                 691          10      0 9s7ppf88qzx2w
  1 (USER)         db file sequential read            83           7     53 9s7ppf88qzx2w
  1 (USER)         gc current block 2-way             29           3      3 9s7ppf88qzx2w
  1 (USER)         gc cr grant 2-way                  12           3      4 9s7ppf88qzx2w
  1 (USER)         db file sequential read             9           9    295 63sr2px4kd9da
...

, и данные SQL трейса:

SQL ID: 9s7ppf88qzx2w Plan Hash: 2290451523

...

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1     24.86      24.92         18         28          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch       64    440.72     543.60     115423   60798426          0         590
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       71    465.58     568.53     115441   60798454          0         590

, и в Shared Pool запрос выглядел «богато»:)

SQL> @shared_cu 9s7ppf88qzx2w
 
INST    EXECS LAST_LOAD_TIME USERS_OPENING LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON1                   SQL_PLAN_BASELINE  IS_OBSOLETE
---- -------- -------------- ------------- ---------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- ------------------------- ------------------ -----------
   1        1 26.02 12:21                1 26.02 12:21          28705736      1082418118         143977     4 N          N          Y          Y                  N                N                Optimizer mismatch(13)  |                    N
   1        1 26.02 11:01                1 26.02 11:01          34063060      3680477589         135782     1 N          N          N          Y                  N                N                Optimizer mismatch(13)  |                    N
   1        2 26.02 10:10                1 26.02 10:14         136545937      2290451523         124054     0 N          N          N          Y                  N                N                Optimizer mismatch(13)  |                    N
   1        1 25.02 12:16                0 25.02 12:16          34828982      1082418118         130845     3 N          N          N          Y                  N                N                NLS Settings(0)  |                           N
   1       16 25.02 12:08                0 25.02 12:14           3177757      1082418118         130819     2 N          N          N          Y                  N                N                Optimizer mismatch(13)  |                    N
   2        3 26.02 12:15                0 26.02 12:20          72497229      2290451523         124054     1 N          N          N          Y                  N                N                Optimizer mismatch(13)  |                    N
   2        1 26.02 12:18                1 26.02 12:18          28571585      3680477589         144866     2 N          N          Y          Y                  N                N                Optimizer mismatch(13)  |                    N
   2        6 26.02 10:14                0 26.02 11:23          94942584      2290451523         124054     0 N          N          N          Y                  N                N                Optimizer mismatch(13)  |                    N

— многообразие планов, сгенерированных, очевидно, под влиянием cardinality feedback

Удивительным оказалось то, что для запроса наличествует во всех смыслах разрешённый и, более того, воспроизводимый (REPRODUCED=YES) baseline:

SQL> @bl_check4sql_id 9s7ppf88qzx2w
 
SQL_HANDLE            PLAN_NAME                      ORIGIN      VERSION    LAST_MODIFIED       LAST_EXECUTED       ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ------------------------------ ----------- ---------- ------------------- ------------------- ------- -------- ----- ---------- ---------
SQL_66a57f45ad0a7744  SQL_PLAN_6d9bz8qqhnxu4929c981c MANUAL-LOAD 11.2.0.3.0 11.04.2014 11:27:32 24.04.2014 09:15:33 YES     YES      YES   YES        NO

, который однако же к запросу не применяется со следующим комментарием в 10053 трейсе:

SPM: statement found in SMB
...
SPM: plan reproducibility - session OFE = 11020004, hinted OFE = 11020003 -- *
...
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : APPS
  plan_baseline signature  : 7396457900258522948
  plan_baseline plan_id    : 1059230035
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
    hint num  3 len 22 text: DB_VERSION('11.2.0.3')
...
SPM: change REPRODUCED status to NO, planName = SQL_PLAN_6d9bz8qqhnxu4929c981c -- **
...

— по причине несовпадения версий создания baseline и текущего значения OFE (*), поскольку с момента создания baseline бд была успешно обновлена:

SQL> @param optimizer_features_enable
 
NAME                       VALUE     IS_DEF   IS_MOD     DSC
-------------------------- --------- -------- ---------- --------------------------------------
optimizer_features_enable  11.2.0.4  TRUE     FALSE      optimizer plan compatibility parameter

Более того, согласно тому же трейсу, REPRODUCED status должен был быть изменён (**), но практически этого не случилось :(

Из всего этого следует, что несмотря на оптимистичность одного из основных Benefits of SQL Plan Management:

SQL plan baselines significantly minimize potential regressions resulting from an upgrade

после обновления / upgrade проверять актуальность baseline-ов совсем нелишне, невзирая на значение флага REPRODUCED

5 комментариев »

  1. Игорь, получается, что из хинтов бейслайнов лучше убирать OPTIMIZER_FEATURES_ENABLE и DB_VERSION?
    Или достаточно убрать OPTIMIZER_FEATURES_ENABLE?
    Воспроизводится ли план, если их убрать?

    комментарий от Sayan Malakshinov — 11.03.2015 @ 09:27 | Ответить

    • Не пробовал убирать по отдельности, но, как мне кажется, это не должно значительно помочь/повлиять — т.к. информация о версии создания baseline в любом случае сохраняется в DBA_SQL_PLAN_BASELINES.VERSION

      комментарий от Игорь Усольцев — 11.03.2015 @ 10:21 | Ответить

      • Поправь меня если я не прав, но насколько я понял сам пост, проблема-то в «hinted OFE = 11020003», т.е. как раз по идее OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’) из хинта, а не DBA_SQL_PLAN_BASELINES.VERSION?

        комментарий от Sayan Malakshinov — 11.03.2015 @ 10:29 | Ответить

        • Действительно, проблема в «hinted OFE = 11020003», но формально, в соответствии с документацией и здравым смыслом, такой проблемы быть в принципе не должно — мы ведь может хинтовать запрос на новой бд старыми версиями OFE ?)
          Ну, и, конечно, печально, что механизмом SPM baseline-ов эта проблема может не детектироваться

          комментарий от Игорь Усольцев — 11.03.2015 @ 11:04 | Ответить

          • В общем, еще один минус механизму SPM :) Я-то им практически не пользуюсь из-за отсутствия поддержки force_matching как у профилей, а теперь еще один мощный минус.

            комментарий от Sayan Malakshinov — 11.03.2015 @ 11:10 | Ответить


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 такие блоггеры, как: