Just something I found out today...

select nvl(arg1, arg2) ... from ...

Both arg1 and arg2 will be evaluated for every row regardless of wether arg1 is null or not. This caused me problems cos arg2 was a function which selected the nextval from a sequence. So when selecting 15000 records just two records caused arg1 to be null yet arg2 was evaluated 15000 times instead of two times. This caused a performance issue in that it was unnecessarily selecting from the sequence 14998 times.

The solution was to replace the nvl by case i.e.

select case when arg1 is null then arg2 else arg1

NOTE coalesce is not the same as the above either.