Всем доброго.
Коллеги есть запрос, визуально всё нормально. На 12-ке тоже подвисает по указанному событию, но ненадолго - пока максимум видел 5 минут и далее выполняется. На 11.2.0.3 всё менее предсказуемо, может на 2 минуты подвиснуть, может на 5, может на час, может на 4 часа. При этом план появляется в v$sql_plan, запрос появляется в v$sql_monitor, в самом em как будет просто висит и ничего не делает.
Причину искать пробовал по разному, но стойкое ощущение что это просто бага. В запросе на join/left join более 15 таблиц. Уже дошло до абсурда, думал может дело в какой-то таблице, убирал их по одной. Итого: когда остаётся 7 таблиц отрабатывает всё мгновенно и далее по мере прибавления начинаются вот эти ожидания, при этом неважно какую добавлять т.е. будто от количества зависит. Не думаю, что хинтами тут можно что-то изменить, уже пробовал по разному.
Что происходит в момент когда всё висит? Плодятся запросы в v$sql с точно таким же sql_id и одинаковым временем запуска, время обновления при этом меняется. Так же множатся данные в v$sql_shared_cursor и меняется у них только OPTIMIZER_MISMATCH, ну и конечно child_adress+child_number. В Reason указано примерно следующее:



Ещё заметил, что возможно это связано с тем, что запрос в пакете. При запуске вне пакета такого дикого подвисания не видел ни разу, ну может минут 5 максимум. При запуске из анонимного блока подвисание тоже есть, но опять же о часах речи никогда не шло. При вставке в темповую таблицу всё отрабатывает нормально, ожидания есть но совсем копеечные.
Перехода на 12-ку пока нет, надо как-то отладить этот механизм на 11-ой версии. Может есть у кого идеи?

P.S> Заранее спасибо!

Коллеги, что-то у меня форматирование текста какое-то странное. Извиняюсь, видимо что-то не так сделал.

SeaGate, в плане действительно есть temp table transformation. В запросе участвует конструкция with с хинтом MATERIALIZE, если вместо MATERIALIZE указать хинт INLINE, то запрос почти никогда не отрабатывает, уходит в бесконечное ожидание. Но в v$sql запросы плодится перестают, появляется всего 2. По reason всё тоже самое <reason>Optimizer mismatch(2)</reason>. При том, что сам запрос после начала работы выполняется за 5-10сек. В каком формате предоставить план? format => Parallel или как-то иначе?
По ссылке пройти не смог.
--
Kamael, насколько я понимаю _max_permutations отвечает в какой-то мере за скорость парсинга, в моем же случае запрос разбирается корректно. С уменьшением этого параметра запрос разбирался конечно быстрее, но в остальном профита не было - то же бесконечно ожидание.
--
dbpatch, да сначала тоже подумал про Mutex-ы. Но потом начал читать разбираться и думаю, что в них дело. К слову ожидания "library cache: mutex X" у меня нет.

Сейчас парадокс в том, что с хинтом INLINE вместо MATERIALIZE вставка не выполняется даже в темповую табличку. Т.е. по сути проблему удаётся повторять многократно, от этого конечно легче разбираться.