Interestingly, though, recently I was faced with optimizing some queries in ACES that ran *very* slowly. They were complex with a bunch of subselects. Breaking pieces out into PL/SQL sped up the queries ay a couple of orders of magnitude (seriously).
Tables were analyzed and all that. The query optimizer was just picking a very, very bad execution plan for the Grand Monolithic Version of the query.
I'm sure I could've gotten equivalent execution times with the single query approach with the right set of optimizer hints but splitting out pieces into PL/SQL functions was easier and they were useful elsewhere, too so what the heck...
