У меня был опыт участия в написании системы OLTP, в которой был достаточно высокий темп апдейтов, причем часто апдейтились одни и те же записи (из разных транзакций). Для того, чтобы избежать задержек, нужно было обеспечить быстрое завершение транзакций (явный commit или rollback). Если клиент обращается к БД не через хранимые процедуры, то управление транзакциями выполняется на стороне клиента, а значит, если клиент поведет себя некорректно (не станет быстро завершать транзакцию), или возникнут проблемы со связью в середине транзакции, то записи, которые апдейтят многие транзакции, окажутся заблокированы этой подвисшей транзакцией. Если же клиент взаимодействует через хранимые процедуры, то управление транзакциями осуществляется из таких процедур (явно начинается и явно завершается в одной и той же процедуре). Это минимизирует время транзакции. Для нас это было одной из основных причин применения хранимых процедур. Хотя, конечно, существенно было и то, что минимизировался трафик между клиентом и БД. На мой взгляд, эти две причины имеют достаточно большое значение именно для OLTP.
В другом проекте (на MS SQL) были огромные объемы исходных данных. Для сокращения размера БД было разработано очень специфическое представление данных в таблицах, так что эффективно можно было выполнять только некоторые предопределенные запросы с обязательными параметрами. Если в этих запросах не задать обязательные параметры и/или добавить дополнительные условия, то оптимизатор мог запросто выбрать план выполнения, при котором сервер "уходил в себя" (результата дождаться было просто невозможно). Однако заказчик требовал обеспечить ему доступ к этим данным (например предоставить некие views) из его приложений. Если бы мы предоставили ему views, то при использовании их он мог бы не задать обязательные параметры, задать дополнительные условия, перевязать эти view с какими-то другими таблицами/views/подзапросами. При выполнении таких запросов запросто возникли бы проблемы с производительностью (причем, всего сервера БД). Мы передали заказчику набор хранимых процедур с обязательными параметрами (не задать их он уже не смог бы), возвращающие резалтсеты, которые он может вычитывать точно так же, как при выполнении обычного запроса.