I mentioned to someone the other day that I think the fiery boulder spell in Diablo 2: Lord of Destruction is one of the best skills ever. It's great watching the boulder roll into enemies and knock them back.
The boulder lasts more than just a second, and I think that's a large part of what makes the skill so much fun. It's rare for players to be able to pause for a moment and appreciate an action they just performed.
The tranquilizer gun in Perfect Dark 64 is another example with lasting effect. I can recall laughing hysterically with friends as one of us loaded up the other on tranquilizer and watched his screen go blurry. It could get annoying. But, again, you had a second or two to appreciate your action.