There are some situations where switching to D3-2-1 is handy. Some of which is already mentioned but ill list them anyway.
1) Travelling uphill when you need the torque. (especially when there is alot of corners the auto can't decide what gear to use)
2) Travelling downhill in 60 zones. D3 can hold the speed up so you won't have to ride the brake to avoid speeding.
Worst case scenario is travelling downhill for a long period of time. I've seen friends ride the brake the whole way down and by the end we've had to pull over and let their brakes cool down.
3) Entering/overtaking on the freeway. I find my Integra's auto to be laggy when switching gears.
Other than that switching the automatic transmission manually is pretty pointless.