Another way it could happen without reflecting too badly on the original developer would be finding a way to trick a SQL optimizer out of doing something stupid.

Oracle seems to be a lot more prone to that than SQL Server. One big area is that the optimizer forgets everything it should know if there's a user-defined function call in a Where clause. Oracle will try to run that before the rest of the conditions, and it will not notice if none of the parameters to the function are coming from the query. I got a ~200x speedup in PL/SQL code written by a friend of mine by putting the results of a function call into a variable before running the query.