Як завжди несподівано повільно починав виконуватися зазвичай швидкий запит, що не повертає рядків :(:
При цьому запит виконується повільно на різних інстанси по-різному, на першому:
При однаково прекрасному плані виконання:
Статистика виконання на 2-му інстанси прямо показує причину множинних читань кореневого блоку індексу (index branch block) і, як наслідок, повільне виконання запиту:
- і дійсно, в оглядах gv $ session і gv $ transaction легко знайти 4 багатогодинних транзакції, запущені на 2-му інстанси і активно змінюють блоки цікавить таблиці і відповідного індексу
Як Oracle відновлює консистентну версію блоку даних, багаторазово зміненого відкритими транзакціями (що відбивається статистикою data blocks consistent reads - undo records applied) відмінно описав Дж.Льюісу:
Цікаво, що статистики та очікування на 1-му інстанси для вищепоказане повільного виконання можуть виглядати або не зовсім адекватними (в сенсі не дозволяє визначити причину низької продуктивності запиту):
або вказувати в якості причини уповільнення очікування gc cr block busy:
формально відноситься до класу Contention-Related Wait Events. що також не зовсім зрозуміло вказує джерело проблеми
Або ж подія gc cr block busy крім проблем з конкуренцією відображає затримки cache fusion. пов'язані з відновленням CR версії блоку на віддаленому інстанси
До слова, в згаданих 4-х довгограючих транзакціях на 2-му інстанси виконання подібних запитів з тієї ж причини з часом значно сповільнюється судячи За Трейсі, обробленого tkprof:
- від 332/9570
35 мс в середньому в першій частині виконання довгою транзакції / бізнес-процесу
63 мс і більш надалі
Така ж поведінка при бажанні можна спостерігати на сиром Трейсі за ключовим словом FETCH - тому що саме так відбивається в Трейсі отримання CR версії індексного блоку:
- на початку виконання однієї з довгих конкуруючих транзакцій на FETCH для sql_id = 2kdudjnay024c витрачалося не більше 98 мс (максимально e = 97709 мкс):
- з часом, в результаті накопичення незакомміченних undo записів - верхня межа часу виконання (фактично тривалість FETCH) піднялася майже до 500 мс (e = 479399):
При неконкурентном виконанні єдина довгограюча транзакція виконується помітно швидше за рахунок меншого часу виконання (знову практично еквівалентного часу FETCH) цих дрібних запитів - не більше 14 мс:
- за відсутності конкуруючих транзакцій, багаторазово змінюють одні і ті ж блоки, undo records applied не потрібні