Имеется: запрос, чужой, вызывается закрытой системой, выглядит как несколько соединенных иерархических подзапросов с WITH. Сам материализованный подзапрос возвращает 600000 строк, быстро (0.5 сек). Общий запрос выедает Temp и валится после 15 минут работы, вычитывая логически в районе 1TB и загрузив 4 проца до еквозможности работать других. Это начало истории. Через экспорт/импорт клонирую пользователя, по обеим схемам собираю статистику. В "новой" схеме запрос без проблем сортируетса в памяти, в темп не пишет, выполняется за 2 секунды.

Оптимизационная среда (по v$sql_optimizer_env) одинаковая
Пробовал очищать shared_pool, точечно и гамузом. Почему-то старый запрос чудным образом возрождается.

Посмотрел планы - разные, на "быстром" cpu_cost в 4 раз больше, io_cost - в полтора. Разница в 4-х строчках из 144, фильтрация перед соединением, что логично, хоть и не по костам.

Планы из 142 (144) строк не привожу, но может кто-то подкинет идею, куда хоть копать?
База 11.2.0.4, Oracle Linux

Спасибо.
Инстанс тот же, база та же, все виртуализировано. Бейзлайна/профиля нет. Байнды одинаковые, выполняется все с одной машины с одного тестового скрипта pl/sql Developer, хотя тут типы не особо важны, собственно работа с базой нареканий не вызывает, доступ к таблицам идентичен, проблема со связыванием в памяти.
За статью спастбо, на днях практически такой же запрос вылечили профилем, но причина так и не ясна, а значит, сидим на бомбе.

Попробую, не мудрствуя лукаво, применить план с "хорошей" схемы, но советы продолжают приветствоваться.

andrey_anonymous,

v$sql_optimizer_env говорит, что ВСЕ одинаковое. Нет причин не верить :)

xtender,

Поясните мне, как тут 10053 поможет? Что делается, я и так знаю, но почему? Хоть намекните

Тогда другой вопрос:
сделал alter system flush shared_pool, выполнил запрос и вижу в v$sqlarea свой запрос со старым first_load_time. DBMS_SHARED_POOL.PURGE тоже не помогло. Причем до выполнения его в v$sqlarea не было! Нашел коммнтарий Кайта по поводу того, что он флашит, что может, но может кто подскажет, что еще нужно очистить, чтобы уничтожить его следы ? Ребут хоть поможет или оно где-то персистится?

andrey_anonymous,

Еще раз: выполняется скрипт, с одними и теми же параметрами, из одного клиентского приложения, права, профайл у пользователей одинаковые. Эффект воспроизводится даже при вызове "чужого" запроса через current_schema. Есть ощущение, что там план "залип", когда еще не было данных, но этого не показывает.

andrey_anonymous,

Да у меня доступа на файловую систему нет, я не админ, просто у пользователя прав больше, чем нужно, вот я и делаю, что могу, траблшутю по старой памяти :)

Valergrad,

Да я как раз об этом на Кайта сослался, но не мой случай, активных сессий не было. Он из sqlarea ушел, но не насовсем, а где-то переждал и вернулся :)

Vadim Lejnin,

Это неправильный подход, если это нужно пользователю, то неважно, кто решает вопрос, у разработчика в плане performance tuning зачастую больше опыта, по крайней мере так было, когда я был разработчиком.