Oracle mechanics

14.01.2016

12c: старая проблема High version count и параметр PARALLEL_INSTANCE_GROUP

Filed under: Диагностика системы (instance),Oracle,shared pool — Игорь Усольцев @ 08:13
Tags:

С коллегой Сергеем Щукиным смотрели на проблему избыточной генерации трейсов типа Huge Trace Files Created Containing «—— Cursor Obsoletion Dump sql_id=%s ——» (Doc ID 1955319.1) — где в соотвествии с рекомендациями, установка параметра _kks_obsolete_dump_threshold = 0 помогла отключить/избавиться от трейсов

Но проблему High version count это не решило:

12.1.0.2@ SQL> select inst_id, sql_id, count(*)
  2    from gv$sql
  3   group by inst_id, sql_id
  4   having count(*) > 1000
  5   order by count(*) desc
  6  /
 
INST_ID SQL_ID          COUNT(*)
------- ------------- ----------
      1 7u1z273sfty8z      93057
      1 5cbp4s5jq04xg       7169
      2 gyvrs5a6pm7h7       1225

Топовые запросы имели схожую статистику курсоров:

SQL> @shared_cu12s_noxml 7u1z273sfty8z
 
INST_ID PLAN_HASH_VALUE BIND_SENSE BIND_AWARE PX_MISMATCH REASON#1                              CURSOR_COUNT  PHV_COUNT FPHV_COUNT        EXECS USERS_OPENING
------- --------------- ---------- ---------- ----------- ------------------------------------- ------------ ---------- ---------- ------------ -------------
      1      2768206387 N          N          Y           Optimizer mismatch(4)                        94608          1          1       385403            32
      1      2768206387 N          N          N           Optimizer mismatch(4)                          132          1          1          512             0
      1      2768206387 N          N          Y                                                            1          1          1            1             0
 
SQL> @shared_cu12s_noxml 5cbp4s5jq04xg
 
INST_ID PLAN_HASH_VALUE BIND_SENSE BIND_AWARE PX_MISMATCH REASON#1                              CURSOR_COUNT  PHV_COUNT FPHV_COUNT        EXECS USERS_OPENING
------- --------------- ---------- ---------- ----------- ------------------------------------- ------------ ---------- ---------- ------------ -------------
      1       282845214 N          N          Y           Optimizer mismatch(4)                         7144          1          1        41942             0
      1       282845214 N          N          N           Optimizer mismatch(4)                           23          1          1           79             0
      1       282845214 N          N          Y                                                            2          1          1      2502241           138
 
SQL> @shared_cu12s_noxml gyvrs5a6pm7h7
 
INST_ID PLAN_HASH_VALUE BIND_SENSE BIND_AWARE PX_MISMATCH REASON#1                              CURSOR_COUNT  PHV_COUNT FPHV_COUNT        EXECS USERS_OPENING
------- --------------- ---------- ---------- ----------- ------------------------------------- ------------ ---------- ---------- ------------ -------------
      2      3835802939 Y          N          Y           Optimizer mismatch(4)                         1394          1          1         2110            24
      2      3835802939 Y          N          Y                                                            1          1          1            1             0
      2      3835802939 N          N          Y           Rolling Invalidate Window Exceeded(2)            1          1          1         2730             0
      2      3835802939 Y          N          N           Optimizer mismatch(4)                            1          1          1           13             0

PX_MISMATCH=Y и Optimizer mismatch(4) в причинах повторного неиспользования курсоров, что наводит на документ Common Bugs Associated with PX_MISMATCH (Doc ID 1629107.1), однако вся описанная там красота формально уже пофикшена в 12.1(

cursortrace level 577 даёт:

 kksCheckCursor: next child is #849
Child was pruned
 kksCheckCursor: next child is #850
 kksCheckCursor: pinning child #850 in shared mode 0x80760b108 0x1b6d68e1d0
 Compilation environment difference Failed sharing : 2000000000000
    parallel_execution_enabled          = true                 false -- запрет параллельного выполнения, как следствие
    active_instance_count               = 1                    0     -- по причине неточного значения PARALLEL_INSTANCE_GROUP
    parallel_query_default_dop          = 0                    48
    is_recur_flags                      = 0                    161
    total_processor_group_count         = 1                    0
 SQL pgadep:1 pgapls:0 system
 kksUnlockChild: releasing child
 Failed sharing : 2000000000000         -- *

и можно пробовать расшифровать значение Failed sharing : 2000000000000 (*) скриптом из ORA-00600 [17059]: How to determine the reason for «sharing failure(s)» as reported by a trace file (Doc ID 1457907.1):

SQL> DECLARE
  2    v_decimal       NUMBER default 0;
  3    v_power         NUMBER DEFAULT 0;
  4    v_bit           NUMBER DEFAULT 0;
  5    v_column_offset NUMBER :=4;
  6    v_reason        VARCHAR2(30);
  7    v_hex           VARCHAR2(16) := '2000000000000';
  8  BEGIN
  9    dbms_output.put_line(chr(13)||chr(10));
 10    select to_number('2000000000000','xxxxxxxxxxxxxxxx') into v_decimal from dual;
 11    while power(2,v_power) <= v_decimal loop
 12      v_bit := v_power +1;
 13      if bitand(v_decimal,power(2,v_power)) != 0 then
 14        select column_name into v_reason
 15          from dba_tab_cols
 16         where table_name ='V_$SQL_SHARED_CURSOR'
 17           and column_id = v_column_offset + v_bit;
 18        DBMS_OUTPUT.PUT_LINE('bit '||v_bit||':  '||v_reason);
 19      end if;
 20      v_power := v_power +1;
 21    END LOOP;
 22  END;
 23  /


bit 50:  PX_MISMATCH

PL/SQL procedure successfully completed.

— что и так было достаточно очевидно(

Можно по ключевым словам выйти на Cursors Are Not Being Shared Due to ‘PX_MISMATCH’ in Standby Environment (Doc ID 1467417.1) -> Bug 10297948 : FREQUENT PX MISMATCHES OCCURRING FOR PARTICULAR SQL, опять же приводящие к Bug 7352775 — Many child cursors when PARALLEL_INSTANCE_GROUP set wrong (Doc ID 7352775.8) — и несмотря на то, что оба три этих бага должны были быть «успешно пофикшены в 12.1», всё-таки проверить параметр PARALLEL_INSTANCE_GROUP и действительно найти неверно установленную пару (при отсутствии соотв.вида service):

SQL> show parameter %instance_group
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string      
parallel_instance_group              string      ORCL12I1

После приведения значений параметров:

SQL> show parameter %instance_group
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string
parallel_instance_group              string

и flush shared_pool получить для запроса 7u1z273sfty8z, ранее имевшего 93,000+ курсоров, следующую благостную картину:

SQL> @shared_cu12s_noxml 7u1z273sfty8z
 
INST_ID PLAN_HASH_VALUE BIND_SENSE BIND_AWARE FEEDBACK_STATS OPT_STATS REOPT ADAPT BIND_EQ_FAIL PQ_SLAVE_MISMATCH PX_MISMATCH REASON#1              FIX_CONTROL#1 CURSOR_COUNT  PHV_COUNT FPHV_COUNT      EXECS USERS_OPENING
------- --------------- ---------- ---------- -------------- --------- ----- ----- ------------ ----------------- ----------- --------------------- ------------- ------------ ---------- ---------- ---------- -------------
      1      2768206387 N          N          N              N         N           N            N                 N           Optimizer mismatch(4)                          1          1          1    3188546            26

— один курсор для 3,188,546 выполнений — оч.хорошо, а в целом по больнице:

SQL> select inst_id, sql_id, count(*)
  2    from gv$sql
  3   group by inst_id, sql_id
  4   having count(*) > 1000
  5   order by count(*) desc
  6  /
 
   INST_ID SQL_ID          COUNT(*)
---------- ------------- ----------
         2 8mdz49zkajhw3       1135
         1 8mdz49zkajhw3       1038

1000+ курсоров — это уже не 90,000+, тем не менее стоит проверить статистику курсоров:

SQL> @shared_cu12s_noxml 8mdz49zkajhw3
 
INST_ID PLAN_HASH_VALUE BIND_SENSE BIND_AWARE FEEDBACK_STATS OPT_STATS REOPT ADAPT REASON#1                         FIX_CONTROL#1          CURSOR_COUNT  PHV_COUNT FPHV_COUNT      EXECS USERS_OPENING
------- --------------- ---------- ---------- -------------- --------- ----- ----- -------------------------------- ---------------------- ------------ ---------- ---------- ---------- -------------
      1      2609763588 Y          N          Y              N         Y           PQ Slave mismatch(5)             314941404            0          919          1          1       2932             0
      2      2609763588 Y          N          Y              N         Y           PQ Slave mismatch(5)             314941404            0          752          1          1       1695             0
      1      2609763588 Y          N          N              N         N           PQ Slave mismatch(5)             314941404            0           83          1          1          0             0
      2      2609763588 Y          N          N              N         N           PQ Slave mismatch(5)             314941404            0           83          1          1          0             0
      2      2609763588 Y          N          N              N         N           Bind mismatch(36)                314941404            0           25          1          1         50             0
      1      2609763588 Y          N          N              N         N           Bind mismatch(36)                314941404            0           19          1          1         68             0
      2      2609763588 Y          N          Y              N         Y           Auto Reoptimization Mismatch(1)  314941404            0           15          1          1         47             0
      2      2609763588 Y          N          Y              N         Y           Optimizer mismatch(12)           314941404            0            3          1          1         10             0
      1      2609763588 Y          N          Y              N         Y           Optimizer mismatch(12)           314941404            0            2          1          1          6             0
      2      2609763588 Y          N          N              N         N           Optimizer mismatch(12)           314941404            0            1          1          1          0             0
      2      2609763588 Y          N          Y              N         Y                                                                              1          1          1          1             0
      1      2609763588 Y          N          N              N         N           Optimizer mismatch(12)           314941404            0            1          1          1          0             0
      1      2609763588 Y          N          N              N         N           PQ Slave mismatch(5)             314941404            0            1          1          1          1             0
      1      2609763588 Y          N          Y              N         Y                                                                              1          1          1          1             0

— картина судя по непустому полю FIX_CONTROL#1 наводит на встречавшийся ранее (но ещё не пофикшенный на этой системе) баг из Патч 20476175 для версий до 12.1.0.2.5 — и, вправду, запрос соответствует условиям возникновения описанного бага:

SQL> select sql_text from v$sqlarea where sql_id = '8mdz49zkajhw3';
 
SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*+ OPT_PARAM('_fix_control' '16391176:1') */ GROUP_TYPE, BUCKET_START, B

, и, что любопытно, является системным:)

SQL> select distinct session_type, module, action
  2    from gv$active_session_history
  3   where sql_id = '8mdz49zkajhw3'
  4  /
 
SESSION_TYPE MODULE       ACTION
------------ ------------ ----------------------
FOREGROUND   MMON_SLAVE   Automatic Report Flush
BACKGROUND   MMON_SLAVE   Automatic Report Flush

Кроме прочего, упорядочивание параметра PARALLEL_INSTANCE_GROUP позволило вернуть значение по умолчанию параметру:

SQL> alter system set "_kks_obsolete_dump_threshold" = 1;

уже без риска получать много/больших трейсов типа Cursor Obsoletion Dump, т.е. в отсутствие проблемы избыточной генерации курсоров/High version count — трейсов нет, что логично)

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

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

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