Ну вы, робяты тут и наговорили...


Хранимая процедура скомпилирована для "средних" параметров - некой эвристики на которую ориентируется оптимизатор запросов

И производительность будет одинаковая, за исключением того, что запрос в ХП компилируется только один раз, а самостоятельный запрос компилируется каждый раз при выполнении

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

Если ораклоиды выяснят что вы пользуетесь запросами без bind-variables то скажут что писал идиот, и в какой-то мере будут правы.


Внесу ка я свои две копейки...

1. в Oracle обычно говорят не про компиляцию запроса, а про разбор (parsing)
2. когда на сервер передается очередной запрос (впервые), Oracle производит его разбор, построение плана выполнения, связывание переменных (binding) и выполнение. В процессе разбора производится определение существования объектов (таблиц и т.д.), что в случае ХП происходит на этапе ее компиляции (здесь есть определенная экономия времени со стороны ХП, но небольшая).
3. разобранный запрос остается в кэше, поэтому если выдается точно такой же запрос, то повторно происходит только т.н. мягкий разбор (soft parsing), при котором, например, проверяются привилегии текущего пользователя на упомянутые в запросе объекты. Все остальное, в том числе и план выполнения, используются повторно. Здесь преимущество ХП м.б. только в том случае, если в приложении в нескольких местах используется одинаковый запрос, но написанный по разному (лишний пробел, регистр символов и т.д.). В этом случае повторного использования не будет, поскольку Oracle посчитает эти запросы разными и для каждого будет выполнен hard parse.
4. По поводу использования bind var тоже вопрос не однозначный. С одной стороны, их использование сокращает кол. hard parse, с другой, иногда наличие константы вместо bind var позволяет построить более оптимальный план выполнения (не зря в Oracle 9 введено т.н. "подсматривание значений" - bind peeking). Поэтому, в общем случае, для OLTP - систем, где запросы относительно короткоиграющие, рекомендуется использовать bind var, а для DSS - систем, где время выполнения значительно превышает время разбора и важен оптимальный план, рекомендуется использовать константы вместо bind var

Локшин Марк 18-я веснаесли на некоторые объекты из запроса забираются права, то запрос сразу удаляется из кеша
А это точно так? По-моему выглядит несколько нелогично. Что из того, что у кого-то отобрали права на часть объектов запроса? Я понимаю, если изменилась схема данных, удалили индекс, но права?

Это точно НЕ так.
В кэше есть специальные защелки, которые отслеживают зависимости объектов. Если какой-то объект меняется (например, добавляется колонка в таблицу), то все зависимые объекты помечаются как инвалидные. Если в SQL (или PL/SQL), находящемся в кэше, есть обращение к подобным объектам, то он тоже помечается как инвалидный и при следующей выдаче такого SQL будет повторно разобран (hard parse).
По поводу прав ситуация другая, поскольку неизвестно, какой пользователь выдаст очередной такой SQL. Права проверяются на этапе soft parse, о чем я уже написал.
Другое дело, что размеры кэша ограничены и если для очередного запроса не хватит места, то из кэша будет стерт наиболее давно не использовавшийся запрос (алгоритм LRU)