Тьфу блин, поубивала бы.

Выдернула этот подзапрос отдельно, выполняю, план хороший. С включенным MJC (в пределах сессии) плохой, а с выключенным хороший.

Смотрю главный запрос, а там в подсказках ORDERED, убираешь ORDERED и план становится более менее, выполнение запроса 21 секунда (через JOIN вообще секунд 5), я там специально в TMP_PRODUCT_LIST понапихала 121000 товаров и TMP_PARTY_LIST
1 100 000.

Теперь бы разработчика убедить не мешать оптимизатору оптимизировать.

У меня еще в паре мест они индекс рекомендуют оптимизатору, а при проверке советник находит другой индекс в 1000 (!) раз быстрее.

trace.log,

Он как раз по рекомендациям разработчика и тупит, я их в программе и не могу убрать.

Неправильно что-то все таки, или оптимизатор кривой, или...
Версия 11.2.0.1 Enterprise.

Финал:
MJC отключила




а в тех трех запросах, которые продолжили висеть и перемножать временные таблицы по NESTED_LOOPS убедила разработчика убрать ORDERED.
Отчеты стали минут по 5-10.