1. В случаях, когда данных много и запросы тяжелые. Требуется оптимизация, управление джойнами, хинты и прочее.
2. К п1. Чтобы перенести обработку поближе к данными.
3. В случаях, когда бизнес логика размазана и ее часть приходится на БД (всякое в жизни бывает). Тогда SQL становятся большими и сложными - их проще писать на SQL. ORM в этом случае приносит лишнюю сложность.