Выявление и устранение тупика Oracle ITL

Question

У меня есть пакет БД Oracle, который обычно вызывает тупик, который я считаю ITL (список заинтересованных транзакций). Соответствующая часть файла трассировки находится ниже.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000cb52-00000000        22     131           S       23     143          SS
TM-0000ceec-00000000        23     143    SX             32     138    SX   SSX
TM-0000cb52-00000000        30     138    SX             22     131           S
session 131: DID 0001-0016-00000D1C session 143: DID 0001-0017-000055D5
session 143: DID 0001-0017-000055D5 session 138: DID 0001-001E-000067A0
session 138: DID 0001-001E-000067A0 session 131: DID 0001-0016-00000D1C
Rows waited on:
Session 143: no row
Session 138: no row
Session 131: no row

В этой таблице нет индексов битовой карты, поэтому причина не в этом. Насколько я могу судить, отсутствие "Строки ожидали" плюс "S" в столбце "Ожидание официанта", вероятно, указывает на то, что это тупик ITL. Кроме того, таблица записывается довольно часто (примерно 8 операций вставки или обновления одновременно, часто 240 раз в минуту), поэтому тупиковая ситуация с ITL представляется весьма вероятной.

Я увеличил параметр INITRANS таблицы и его индексов до 100 и увеличил PCT_FREE для таблицы с 10 до 20 (затем перестроил индексы), но тупики все еще возникают. Кажется, что тупик возникает чаще всего во время обновления, но это может быть просто совпадением, так как я проследил его всего пару раз.

Мои вопросы двоякие:
1) Это фактически тупик ITL?
2) Если это тупик ITL, что еще можно сделать, чтобы избежать этого?

---

Оказывается, это вовсе не проблема тупиковой ситуации ITL, а проблема с неиндексированными внешними ключами. Я обнаружил это благодаря ответу dpbradley, который убедил меня в том, что это не проблема ITL, и побудил меня выяснить, какими могут быть другие причины тупика без "строк".

Answer 1

Наилучшим показателем давления ITL являются представления о производительности:

select event, total_waits, time_waited, average_wait
 from v$system_event
 where event like 'enq: TX%'
 order by 2 desc;

показывает, что конкурс TX ожидает, и

select OBJECT_NAME, SUBOBJECT_NAME, TABLESPACE_NAME, 
       OBJECT_TYPE, STATISTIC_NAME, VALUE
  from v$segment_statistics 
  where statistic_name = 'ITL waits'
  and value > 0
  order by value desc;

показывает задействованные таблицы и индексы.

(Как все v$ представления, результаты с того момента, когда экземпляр был запущен.)

Если это показывает, что у вас действительно есть ожидания ITL, то параметры INITRANS и PCTFREE являются основными вращающимися ручками (но INITRANS = 100 звучит для меня довольно дорого, и они стоят пространства).

Если ожидание ITL не является проблемой, необходимо проверить код приложения.

Answer 2

Лучший вариант - увеличить его по мере необходимости (начать с 10 по умолчанию и увеличить на 10). Если вы видите сокращение ITL ожидания, вы готовы. Обычно эти связанные параметры корректируются методом проб и ошибок как в Oracle, так и в SQL Server. Корректировка этих параметров в режиме реального времени не будет такой большой проблемой, если только ресурс не слишком занят. Вы можете использовать следующий запрос, чтобы видеть после каждого приращения, ждет ли ITL или исчезнет или сильно уменьшится:

SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
  FROM v$segment_statistics t
  WHERE t.STATISTIC_NAME = 'ITL waits' AND t.VALUE > 0
  ORDER BY t.value desc;

Мы выполнили несколько настроек для сценариев взаимоблокировки Oracle из-за ожидания ITL с использованием этого метода. ПРИМЕЧАНИЕ. Убедитесь, что индекс перестроен, если initrans модифицирован для индексов. Также убедитесь, что статистика не устарела.

Для быстрой проверки можно использовать SQL Tuning Advisor, чтобы увидеть полное состояние запроса / индекса и статистику.