There's actually a reload animation for the Chicago Typewriter in Resident Evil 4, which is a complete waste of time, unless you like snazzy poses (and had the PS2 version).
The Ice Arrows in Ocarina of Time took a ton of effort to get (more than any other item except maybe the Biggoron Sword or that stupid bloody cow), and were ultimately completely worthless.
A lot of the guns you can get in Goldeneye are pretty worthless, especially some of the ones you get through cheats. Honestly, what was the point of the Taser or Silver PP7?