Oracle mechanics

24.07.2013

Построение индекса в режиме ONLINE и ожидание enq: MD — contention

Filed under: commonplace,Блокировки,Oracle,wait events — Игорь Усольцев @ 09:27
Tags: ,

В 11.2.0.3 поспешно несвоевременно попытался построить индекс на таблице с materialized view log:

SID#283 SQL> @sid

INST_ID SID  SERIAL#  SPID
------- ---- ------- -----
1       283      295 32156

SID#283 SQL> create index T_CLIENT_CLASS_ID_UIDX on T_CLIENT(CLASS_ID, ID) tablespace ITS online parallel;

Однако, заметив затянувшееся ожидание enq: MD — contention:

SQL> select * from v$lock_type where type = 'MD';

TYPE  NAME                       ID1_TAG          ID2_TAG  IS_USER DESCRIPTION
----- -------------------------- ---------------- -------- ------- -----------------------------------------------------
MD    Materialized View Log DDL  master object #  0        NO      Lock held during materialized view log DDL statements

, попытался терминировать сессию средствами Oracle:

OTHER_SID SQL> Alter system kill session '283,295';

Alter system kill session '283,295'

ORA-00031: session marked for kill

Через некоторое время на этой ( конечно же, тестовой ;) системе были замечены ряд сессий в длительном ожидании library cache lock:

SQL> @lock_tree

BLOCKING_TREE                    EVENT                 SECONDS_IN_WAIT SQL_TEXT                                 P1TEXT          P1RAW            P2TEXT                   P2
-------------------------------- --------------------- --------------- ---------------------------------------- --------------- ---------------- ---------------- ----------
INST#1 SID#283 plsqldev.exe      enq: MD - contention                1 create index T_CLIENT_CLASS_ID_UIDX on   name|mode       000000004D440006 master object #     1684907
  INST#1 SID#3   oracle@ (J009)  library cache lock              45099 begin DBMS_STATS.CLEANUP_STATS_JOB_PROC  handle address  00000006065FF5E0 lock address     2609875953
  INST#1 SID#383 oracle@ (J001)  library cache lock              74162 SELECT ALIAS_TXT FROM sys.snap$  WHERE   handle address  00000006065FF5E0 lock address     2600616558
  INST#1 SID#302 oracle@ (J002)  library cache lock             149760 select count(*) from all_snapshots wher  handle address  00000006065FF5E0 lock address     2616150288
  INST#1 SID#403 oracle@ (J006)  library cache lock             152627 DECLARE job BINARY_INTEGER := :job; nex  handle address  00000006065FF5E0 lock address     2609893778
  INST#1 SID#86  oracle@ (J008)  library cache lock             152627 DECLARE job BINARY_INTEGER := :job; nex  handle address  00000006065FF5E0 lock address     2602099956
  INST#1 SID#144 oracle@ (J011)  library cache lock             152627 DECLARE job BINARY_INTEGER := :job; nex  handle address  00000006065FF5E0 lock address     2609891027
  INST#1 SID#464 oracle@ (J003)  library cache lock             152929 DECLARE job BINARY_INTEGER := :job; nex  handle address  00000006065FF5E0 lock address     2621527688

, блокируемых недобитой сессией 283, находящейся в статусе KILLED и бесконечном состоянии self-deadlock:

SQL> select * from v$session where sid = 283;

SID SERIAL# SERVER    COMMAND TADDR            STATUS STATE   SECONDS_IN_WAIT BLOCKING_SESSION EVENT                P2TEXT                P2 WAIT_CLASS
--- ------- --------- ------- ---------------- ------ ------- --------------- ---------------- -------------------- --------------- -------- ----------
283     295 DEDICATED       9 00000006036179B8 KILLED WAITING               3              283 enq: MD - contention master object #  1684907 Other

Структуры Library Cache показывают, что блокировки относятся к объекту кэша T_CLIENT типа TABLE, Exclusive Lock & Pin на который прочно удерживаются 283-й сессией:

SQL> @kgllockpin

HANDLER          NAMESP           KGLOBTYD KGLNAOBJ  KGLLKTYPE  MODE_HELD  MODE_REQ   SECS_IN_WAIT EVENT                 SID    SERIAL  PROGRAM         SQL_TEXT
---------------- ---------------- -------- --------- ---------- ---------- ---------- ------------ --------------------- ------ ------- --------------- ---------------------------------------
00000006065FF5E0 TABLE/PROCEDURE  TABLE    T_CLIENT  Pin        Exclusive  None       5            enq: MD - contention  283    295     plsqldev.exe    create index T_CLIENT_CLASS_ID_UIDX
00000006065FF5E0 TABLE/PROCEDURE  TABLE    T_CLIENT  Lock       Exclusive  None       5            enq: MD - contention  283    295     plsqldev.exe    create index T_CLIENT_CLASS_ID_UIDX
00000006065FF5E0 TABLE/PROCEDURE  TABLE    T_CLIENT  Lock       None       Share      143312       library cache lock    464    115     oracle@ (J003)  DECLARE job BINARY_INTEGER := :job; nex
00000006065FF5E0 TABLE/PROCEDURE  TABLE    T_CLIENT  Lock       None       Share      143011       library cache lock    403    375     oracle@ (J006)  DECLARE job BINARY_INTEGER := :job; nex
00000006065FF5E0 TABLE/PROCEDURE  TABLE    T_CLIENT  Lock       None       Share      143011       library cache lock    86     81      oracle@ (J008)  DECLARE job BINARY_INTEGER := :job; nex
00000006065FF5E0 TABLE/PROCEDURE  TABLE    T_CLIENT  Lock       None       Share      143010       library cache lock    144    133     oracle@ (J011)  DECLARE job BINARY_INTEGER := :job; nex
00000006065FF5E0 TABLE/PROCEDURE  TABLE    T_CLIENT  Lock       None       Share      140143       library cache lock    302    1575    oracle@ (J002)
00000006065FF5E0 TABLE/PROCEDURE  TABLE    T_CLIENT  Lock       None       Share      64546        library cache lock    383    15557   oracle@ (J001)
00000006065FF5E0 TABLE/PROCEDURE  TABLE    T_CLIENT  Lock       None       Share      35483        library cache lock    3      745     oracle@ (J009)  begin DBMS_STATS.CLEANUP_STATS_JOB_PROC

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

select s.sql_id, s.sql_text
  from v$open_cursor o, v$sqlarea s
 where o.sid = 283
   and o.sql_id = s.sql_id
 order by s.last_active_time

Параметр V$SESSION.P2 = 1684907 ожидания enq: MD — contention указывает на временную таблицу SYS_JOURNAL_1684906, создаваемую для хранения изменений данных таблицы T_CLIENT, вносимых DML операциями во время по/перестроения индекса в режиме ONLINE:

SQL> select object_name from dba_objects where object_id = 1684907;

OBJECT_NAME
-------------------
SYS_JOURNAL_1684906

SQL> select a.object_type, a.object_name, b.*
  2  from   dba_objects a,
  3         ( select substr(object_name,13) as object_id,
  4                  object_id              as temp_obj_id,
  5                  object_name            as temp_table_name,
  6                  object_type            as temp_obj_type
  7           from dba_objects
  8           where object_name like 'SYS_JOURNAL_%') b
  9  where a.object_id = b.object_id
 10  /

OBJECT_TYPE  OBJECT_NAME             OBJECT_ID  TEMP_OBJ_ID TEMP_TABLE_NAME      TEMP_OBJ_TYPE
------------ ----------------------- ---------- ----------- -------------------- -------------
INDEX        T_CLIENT_CLASS_ID_UIDX  1684906        1684907 SYS_JOURNAL_1684906  TABLE

В соответствии с руководством How to use DBMS_REPAIR.ONLINE_INDEX_CLEAN ? (Doc ID 1378173.1) безуспешно пробую избавиться от остатков недостроенного индекса:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    isClean BOOLEAN;
  3  BEGIN
  4    isClean := FALSE;
  5    WHILE isClean = FALSE LOOP
  6      isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID,
  7                                                DBMS_REPAIR.LOCK_WAIT);
  8      DBMS_LOCK.SLEEP(10);
  9    END LOOP;
 10  EXCEPTION
 11    WHEN OTHERS THEN
 12      RAISE;
 13  END;
 14  /

— выполнение затягивается, процедура безуспешно пытаясь получить разделяемый (Share Mode) доступ к недостроенному индексу, надёжно удерживаемый (среди прочих) той же сессией 283:

SQL> @kgllockpin

HANDLER          NAMESP          KGLOBTYD KGLNAOBJ               KGLLKTYPE  MODE_HELD  MODE_REQ   SECS_IN_WAIT EVENT                 SID    SERIAL  PROGRAM         SQL_TEXT
---------------- --------------- -------- ---------------------- ---------- ---------- ---------- ------------ --------------------- ------ ------- --------------- ---------------------------------------
00000006065FF5E0 TABLE/PROCEDURE TABLE    T_CLIENT               Pin        Exclusive  None       10           enq: MD - contention  283    295     plsqldev.exe    create index T_CLIENT_CLASS_ID_UIDX
00000006065FF5E0 TABLE/PROCEDURE TABLE    T_CLIENT               Lock       Exclusive  None       10           enq: MD - contention  283    295     plsqldev.exe    create index T_CLIENT_CLASS_ID_UIDX
00000006065FF5E0 TABLE/PROCEDURE TABLE    T_CLIENT               Lock       None       Share      153577       library cache lock    464    115     oracle@ (J003)  DECLARE job BINARY_INTEGER := :job; nex
00000006065FF5E0 TABLE/PROCEDURE TABLE    T_CLIENT               Lock       None       Share      153276       library cache lock    86     81      oracle@ (J008)  DECLARE job BINARY_INTEGER := :job; nex
00000006065FF5E0 TABLE/PROCEDURE TABLE    T_CLIENT               Lock       None       Share      153276       library cache lock    403    375     oracle@ (J006)  DECLARE job BINARY_INTEGER := :job; nex
00000006065FF5E0 TABLE/PROCEDURE TABLE    T_CLIENT               Lock       None       Share      153275       library cache lock    144    133     oracle@ (J011)  DECLARE job BINARY_INTEGER := :job; nex
00000006065FF5E0 TABLE/PROCEDURE TABLE    T_CLIENT               Lock       None       Share      150409       library cache lock    302    1575    oracle@ (J002)
00000006065FF5E0 TABLE/PROCEDURE TABLE    T_CLIENT               Lock       None       Share      74811        library cache lock    383    15557   oracle@ (J001)
00000006065FF5E0 TABLE/PROCEDURE TABLE    T_CLIENT               Lock       None       Share      45748        library cache lock    3      745     oracle@ (J009)  begin DBMS_STATS.CLEANUP_STATS_JOB_PROC
000000061A937968 INDEX           INDEX    T_CLIENT_CLASS_ID_UIDX Pin        Exclusive  None       10           enq: MD - contention  283    295     plsqldev.exe    create index T_CLIENT_CLASS_ID_UIDX
000000061A937968 INDEX           INDEX    T_CLIENT_CLASS_ID_UIDX Lock       Exclusive  None       10           enq: MD - contention  283    295     plsqldev.exe    create index T_CLIENT_CLASS_ID_UIDX
000000061A937968 INDEX           INDEX    T_CLIENT_CLASS_ID_UIDX Lock       None       Share      731          library cache lock    247    20381   plsqldev.exe     DECLARE   isClean BOOLEAN; BEGIN   isC

Давно напрашивавшийся вариант решения:

$ kill -9 32156

— даёт возможность процедуре DBMS_REPAIR.ONLINE_INDEX_CLEAN завершить свою «грязную работу»:

PL/SQL procedure successfully completed

, порождая замечание об очередной, второй попытке автоматически SMON очистить последствия ONLINE построения индекса в alert.log журнале:

Fri Jul 19 20:01:33 2013
online index (re)build cleanup: objn=1684906 maxretry=2000 forever=0 -- первая запись соответствует запуску Alter system kill session
...
Sun Jul 21 14:30:17 2013
online index (re)build cleanup: objn=1684906 maxretry=2000 forever=0 -- kill -9 & DBMS_REPAIR.ONLINE_INDEX_CLEAN

Более драматический случай с использованием startup restrict и ручным удалением временной таблицы — Online Index Rebuild fails with ORA-08106: cannot create journal table

2 комментария »

  1. Мы обычно строим индекс, с использованием INVISIBLE. Проблем пока не было.

    CREATE INDEX "schema"."index" ON "schema"."table" ("column1", "column2", "column3") ONLINE
      PCTFREE 10 INITRANS 2 MAXTRANS 255
      TABLESPACE "some_ts"  GLOBAL PARTITION BY HASH ("column1")
    (
    PARTITION "part001"  TABLESPACE "TS001" ,
    PARTITION "part002"  TABLESPACE "TS002" ,
    PARTITION "part003"  TABLESPACE "TS003" ,
    PARTITION "part004"  TABLESPACE "TS004" ,
    PARTITION "part005"  TABLESPACE "TS005" ,
    some part, 
    some part,
    some part,
    some part,
    some part)
    COMPRESS  PARALLEL 30  INVISIBLE;
    

    комментарий от mczimm — 24.07.2013 @ 10:55 | Ответить

    • Привет, Максим. INVISIBLE (как атрибут индекса, устанавливаемый после построения) вряд ли как-то влияет на проявление описанных проблем, если на таблице создан и активно используется materialized view log. Тем более в случае построения партиционированного индекса — как более комплексного процесса
      В приведённом мной примере JOB-ы (кроме сбора статистики), ожидавшие library cache lock, пытались обновить матвьюшки, зависимые от T_CLIENT. Выполнение таких же JOB-ов в момент запуска CREATE INDEX, скорее всего, и привело к описанным проблемам

      комментарий от Igor Usoltsev — 26.07.2013 @ 00:03 | Ответить


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