When automatic overdrive transmissions came into popular use in the early 80's, the biggest complaint amongst consumers was their constant shifting from 3rd gear to overdrive and back to 3rd, when driving at around the 45 mph range. The auto manufacturers fixed this inherent problem by providing a way to disable the overdrive via the shift lever, on early mechanically shifted transmissions, or a switch, on modern electronically shifted transmissions.
Scotty mentioned some other driving conditions where it may be beneficial to turn off the overdrive. Another situation, when I turn off the overdrive, is while descending steep grades, so I can take advantage of the engine braking.
There are several situations where turning it off can come in handy.