direct-path INSERT и ожидания enq: TM — contention

Текстовый отчёт AWR (Oracle 10.2.0.4)

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time  Wait Class
------------------------------ ------------ ----------- ------ ------ -----------
CPU time                                         27,751          47.3
enq: TM - contention                 31,252      15,179    486   25.9 Application ...

показывает, что на ожидание, связанное с неприятной блокировкой уровня таблиц (TM или DML locks), тратится более 25% общего времени выполнения запросов на уроне инстанса (DB time)

Согласно документации, обычно такое событие связано с отсутствием индексов на столбцах с действующими ограничениями типа внешнего ключа (foreign key constraints). Понятно, что это не единственная возможная причина блокировки

Для нахождения типичные запросов, ожидающих блокировки можно попробовать использовать Active Session History (ASH), используя фиксированную «оперативную» таблицу v$active_session_history для активных или выполнявшихся в течение последнего часа сессий, либо исторические таблицы, хранящиеся в бд dba_hist_active_sess_history / wrh$_active_session_history

SQL> select distinct
event,
sql_id,
mod(p1,16) as "mode",
p2|| ' - ' || o.name as "obj_ID - obj_name"
from gv$active_session_history ash,
obj$ o
where
event like 'enq: TM%'
and o.obj# (+)= ash.p2
/

EVENT                   SQL_ID           mode obj_ID - obj_name
enq: TM - contention    byf548usvyh1u    6    3809393 T1
enq: TM - contention    abt7wqnvcp22n    6    3742766 T2
enq: TM - contention    bj63d1a1w566q    3    3809393 T1
enq: TM - contention    djd25656whxcs    3    3742766 T2

Как видно, ожидали (а, получив, удерживали, блокируя другие сессии) блокировки таблиц в исключительном режиме (TM lock mode 6 — Exclusive) запросы типа direct-path INSERT:

INSERT /*+ APPEND */ INTO T1 SELECT ...
INSERT /*+ APPEND */ INTO T2 SELECT ...

, а ждали получения блокировки таблиц в менее блокирующем режиме SubExclusive (Table Subset Exclusive, TM lock mode 3, Row-X) запросы обычных вставок в таблицы:

INSERT INTO T1 (...) VALUES (...)
INSERT INTO T2 (...) VALUES (...)

Поведение ожидаемое, описанное в документации и в блогах и вполне логичное: при всех своих преимуществах не предназначен direct-path INSERT для OLTP систем с конкурентной вставкой, поскольку, увеличивая High Water Mark, блокирует таблицу от других DML операций

Используя запросы из презентации Active Session History by Kyle Hailey ( одного из разработчиков OEM 10g Performance monitor), можно примерно оценить какую часть времени проводят в ожидании блокировок найденные конкурентные вставки

SQL> select ash.SQL_ID,
 sum(decode(ash.session_state, 'ON CPU', 1, 0)) "CPU",
 sum(decode(ash.session_state, 'WAITING', 1, 0)) - sum(decode(ash.session_state, 'WAITING', decode(en.wait_class, 'User I/O', 1, 0), 0)) "WAIT",
 sum(decode(ash.session_state, 'WAITING', decode(en.wait_class, 'User I/O', 1, 0), 0)) "IO",
 sum(decode(ash.session_state, 'ON CPU', 1, 1)) "TOTAL"
from v$active_session_history ash, v$event_name en
 where SQL_ID is not NULL
 and en.event# = ash.event#
group by sql_id
order by sum(decode(session_state, 'ON CPU', 1, 1)) desc
/

# SQL_ID        CPU   WAIT    IO  TOTAL
1 bj63d1a1w566q   0  22642    11  22653 -- INSERT INTO T2 (...) VALUES (...)
...
7 byf548usvyh1u   0    530   179    709 -- INSERT /*+ APPEND */ INTO T1 SELECT ...

Первый по количеству зафиксированных в ASH записей «обычный» INSERT INTO T2 (…) VALUES (…) ждёт практически 100% времени выполнения, direct path insert (7-е место в списке) тратит на ожидания ~ 530/709 ~ 75% времени :(

Для первого (по числу попаданий в ASH, sql_id bj63d1a1w566q) запроса можно оценить расклад по ожиданиям

SQL> select event, count(*) from gv$active_session_history
where sql_id = 'bj63d1a1w566q'
and session_state = 'WAITING'
and wait_time = 0
group by event
order by 2 desc
/

EVENT                     COUNT(*)
enq: TM - contention    46715
enq: TX - contention          3422
gc current block busy          558
enq: HW - contention           264
enq: TX - index contention     216
gc buffer busy release          76

На первом месте, с большим отрывом — ожидание enq: TM — contention, которое и привлекло внимание к проблеме в отчёте AWR

Отключение режима direct path insert, форсированного в данном случае подсказками APPEND снимает проблему блокировок, экономит место в таблицах, однако может уменьшить скорость вставки больших объёмов данных ;)