Oh, I've got a few.
Dead Rising: The excavator. It's not really the most effective one by far, but it's the most ridiculous to see in action, in the same vein as the lawnmower. It's basically a heavy, oversized drill, and how it works is the fun: Frank impales a zombie with it, and then you just guide him into a crowd and clear a path, with the impaled zombie spinning 'round and 'round. Blood and body parts fly off as you hit more and more zombies, until it's time to toss off the zombie and impale a new one. Messy fun.
Assassin's Creed: The hidden blade. Apart from just randomly sticking guards as I move about, I love using it in combat. It's difficult sometimes to get the counter timing right and there's no blocking any blows with it. But after sticking three or four guards out of seven in counter moves, the remaining ones all run away at once, utterly shit-scared. Then I chase them down.
Halo 3: The spartan laser. It's always satisfying getting a kill with one of its five shots, be it a fully loaded warthog, those bastard banshees, or just some guy who has no clue he's about to see red in a big, bad way. I'll also have to mention the plasma and spike grenades, just because I love goading anyone in a vehicle to charge me, and then I parry and stick them when they do. Most satisfying when it's a banshee.
Gears of War: The torque shot. Lining up a shot properly and pulling it off can be a pain in the ass, but hearing that "thuck!" sound followed by an explosion can be so satisfying.
Duke Nukem 3D: The pipe bombs and the devastator. The former is fun to toss about, especially when you have the drop on someone/something else, the latter is just utter overkill and sometimes will get Duke to comment or laugh when it makes a huge mess.
Metroid Prime: The plasma beam. Charge up a shot, and watch that space pirate instantly turn to ash!
River City Ransom: The gang members. It's more for hilarity than anything else, picking one up, battering the others with him, and then chucking him into them. If there was ever a game that should be remade, it's this one. Only I'd rather keep it looking roughly the same, and just expand the world as massively as possible. It is still ridiculously fun to this day.
Serious Sam: Both the Colt and the SBC Cannon. I love both, because the Colt is a weak weapon, but fires with infinite range and perfect accuracy, and its ammo is limitless. It's so effective when you're firing two at once, when you know how to keep on the move. The SBC Cannon? It's a big goddamn cannon like you'd find on pirate ships of old, but bigger, and fires a gigantic uranium-filled cannon ball that just plows a straight path when charged up, and finishes with an explosion when it comes to rest. It's like bowling in gun form!
Goldeneye: Proximity mines. Such elaborate traps, and sometimes, I ended up killing myself because I'd go overkill with them and chain explosions that ultimately traced back to myself.
And if anything, Ratchet & Clank could have its own thread for just this topic.
