There is a lot of confusion over this feature. It is not a manual override to the shifting, but rather a simple selection of the highest gear the transmission will go to. In "L" you choose the highest gear, and the transmission will shift as normal, but never go higher than your chosen gear. It's main use was for loads, hauling and bad weather, not to be racy or sporty. In the past, most automatics had a "2" or "L" gear where you set the transmission in this one single gear. Nice in some situations, but not really useful for others. GM upped it just a little by allowing you to choose the gear for the "L" mode.
I use it mostly for the passing aspect. Just shifting from "D" to "L" automatically lowers the transmission one gear. Help provide a more instant boost when needed. I've also towed a boat once, and kept it in "5" so the transmission wouldn't keep hunting gears at higher speeds.