Oracle mechanics

30.10.2015

12c: простое управление планом с использованием SQL Plan Directives (SPD)

Filed under: Oracle,Oracle 12c — Игорь Усольцев @ 00:26
Tags: ,

После обновления на версию 12.1.0.2 обнаружился деградировавший запрос:

12.1.0.2.@ SQL> @shared_cu12_noxml 4nut8pr74vpff
 
EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL REASON#1
----- ------------- -------------------- ------------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ----------------------
  374             2 2015-10-28/01:30:14  28.10.2015 15:38:55    349992290 VALID              2500238505           2253     1 Y          N          Y          N                  N                       0         1         2        Optimizer mismatch(12)

— со ср.временем ~ 350 сек. при SLA/таймауте в 300

Из запроса заметно, что вероятной причиной деградации может быть использованная в процессе директива (SPD_Used), которую удалось запеленговать командой EXPLAIN PLAN:

SQL> select * from table(dbms_xplan.display('','','+metrics'));
...
Sql Plan Directive information:
-------------------------------

  Valid directive ids:
    1529934330873575083

, расшифровать запросом:

SQL> @spd_id 1529934330873575083
 
DIRECTIVE_ID           ENABLED INT_STATE     STATE      REASON                               TAB_CNT REDUNDANT TABLE_LIST               COLUMN_LIST EQ_PRED_ONLY  SIMPLE_COL_PRED_ONLY  IND_ACCESS_BY_JOIN_PRED  FILTER_ON_JOIN_OBJ  CREATED           LAST_MODIFIED     LAST_USED
---------------------- ------- ------------- ---------- ------------------------------------ ------- --------- ----------------------- ----------- ------------- --------------------- ------------------------ ------------------- ----------------- ----------------- -----------------
   1529934330873575083 YES     PERMANENT     USABLE     SINGLE TABLE CARDINALITY MISESTIMATE       1 NO        XO.T_COMPETITION_BUFFER DT          NO            YES                   NO                       NO                  26.10.15 03:52:30 27.10.15 01:09:57 27.10.15 01:09:57

и отключить/запретить стандартной процедурой:

SQL> exec DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE (1529934330873575083, 'ENABLED','NO')
 
PL/SQL procedure successfully completed
 
SQL> @spd_id 1529934330873575083
 
DIRECTIVE_ID           ENABLED INT_STATE     STATE      REASON
---------------------- ------- ------------- ---------- ------------------------------------
   1529934330873575083 NO      PERMANENT     USABLE     SINGLE TABLE CARDINALITY MISESTIMATE

После чего, правда, старый неудачный курсор может автоматически не перейти в невалидное состояние:

SQL> @shared_cu12_noxml 4nut8pr74vpff
 
EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL REASON#1
----- ------------- -------------------- ------------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ----------------------
  389             5 2015-10-28/01:30:14  28.10.2015 16:11:20    349269787 VALID              2500238505           2253     1 Y          N          Y          N                  N                       0         1         2        Optimizer mismatch(12)

и требуется ручное вмешательство/purge:

SQL> @purge_cu 4nut8pr74vpff
 
PL/SQL procedure successfully completed

, что переводит открытый курсов с желаемый статус INVALID_UNAUTH:

SQL> @shared_cu12_noxml 4nut8pr74vpff
 
EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS  PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL REASON#1
----- ------------- -------------------- ------------------- ------------ -------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ----------------------
  389             3 2015-10-28/01:30:14  28.10.2015 16:12:16    349754155 INVALID_UNAUTH      2500238505           2253     1 Y          N          Y          N                  N                       0         1         2        Optimizer mismatch(12)

и гарантирует скорое [пере]создание:

SQL> @shared_cu12_noxml 4nut8pr74vpff
 
EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL REASON#1
----- ------------- -------------------- ------------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- -------------------------------------
   83            20 2015-10-28/16:15:02  28.10.2015 16:18:00       859294 VALID              3753797035           8037     0 Y          N          Y          N                  N                                                    Rolling Invalidate Window Exceeded(3)

— нового курсора уже без использования ранее запрещённой директивы (SPD_Used) и, как следствие, автоматического dynamic sampling (DS_LEVEL), возросшей в 4 раза стоимостью и упавшим до ~ 1 секунды временем выполнения (ELA_PER_EXEC)

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

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

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