Sure, in the Hit Man games, you can blow someone across the room with dual Silverballers and magnum ammo. If you want a challenge that is. Though it's spectacular to watch that civilian, target or mook fly across the room, most weapons are loud, take a long time to reload and will probably get you killed as you are surrounded by said mooks, all armed to the teeth.
In Forza Motorsport 3, players can drive SUVs and pickup trucks like the Range Rover Sport, and Dodge Ram SRT-10. They're amazing for battering cars around (but that will get you kicked out of the game), but they're so heavy that they plow through corners, and so huge that it can actually obstruct your view when in third person.
Pyramid Head's Great Knife in Silent Hill 2. It would be an awesome one-hit-kill weapon — if you could wield it effectively. As it is, equipping it slows you down to a crawl. It takes forever to bring it to the ready position. You can't move while it's in the ready position. It's so slow to move in striking that an enemy with any mobility at all will simply step out of the way. And the recovery time is so long that you're a sitting duck if when you miss. On the plus side, it's the only weapon that has any effect on Pyramid Head, its sheer bulk knocking him off balance... but even then, you're generally just better off keeping out of the way.
Actually the Great knife has another use, James is able to scare off enemies with this weapon. To achieve this, James must turn off his flashlight and radio, then equip the knife. Monsters, particularly Lying Figures, will start to run away from James. This only works in dark places.
Likewise, the secret chainsaw weapon in Silent Hill 2 is certainly awesome (it's a goddamn chainsaw!), but James has to start the saw each time he readies it, holds it off to the side so enemies can't walk into it, and swings it so slowly that there's almost no chance of ever hitting an enemy before they can hit him.
The regular Submachine Gun is Silent Hill 3 is highly damaging, but it burns through ammo very quickly and you can't freely aim it until after you start firing, forcing you to waste bullets while you point it at enemies that are higher or lower than you.
Silent Hill 4 takes this to another level. You can gather a whole arsenal of melee weapons, ranging from golf paraphernalia to demonic pickaxes... but the golf apparel breaks easily and the pickaxe is hard to use, so it's back to using Ye Olde Rusty Axe.
Also, the unlockable weapon Satellite Cannon, because it fired so slowly that any mobile enemy would already be out of the way by the time it actually fired.
The Graviton Gun in Syndicate Wars meant death to anyone on the wrong end of it, recharged quickly, and also emitted tendrils of energy that disintegrated eight other people when you fired it. So, unless you cared about collateral casualties, it was great. Except that it cost three times as much as the Satellite Rain and by the time you developed it, you were probably on the last levels, where you couldn't resupply your agents (or you cheated like a bastard).
And the Satellite Rain counts too. It's a Kill Sat which strikes the ground with beams of death that will knock down buildings and kill anyone in the target area. But with the delay between firing and impact, it's hard to get the enemy to stay in the target area, and it doesn't really do anything a mine or a nuclear grenade can't do more easily, other than looking twenty times more awesome.
In the GBA versions of Harvest Moon (Friends of Mineral Town and More Friends...), you can assemble the three Gems: when equipped, the Kappa Gem automatically restores Strength, Goddess Gem restores Stamina, and the Truth Gem displays both stats on screen. Handy, but at the time when you can actually get them (at least five in-game years in), most players will know the limits at which they can safely work their character, negating the need for the Truth Gem. Scarfing down some Elli Leaves or drinking a Bodigizer XL and a Turbojolt XL restore your health much faster than the Kappa and Goddess Gems would. All this still ignores the amount of effort needed to find all twenty-seven pieces of all three gems.
Some of the Buster attacks from Devil May Cry 4 are dangerous enough in crowds that using standard attacks is safer and more effective. Then there's Nero's 'Showdown', which is almost impossible to pull off without getting screwed yourself. Showdown is a powerful multi-hit attack that takes an exorbitant amount of time to wind up, and if you miss the first strike then it takes an equally crippling amount of time to wind down. However, it does have a saving grace that all of Nero's sword attacks tend to have extremely wide swing radii and Showdown is a Devil Trigger attack (so your BFS wielding spirit is also in effect), so if it connects then all enemies within a few meters almost certainly will die..
Showdown is useful for killing De Cloaked Fausts and Mega Scarecrows fast, just so long as you stun lock them with Summoned Swords during the wind up animation.
The Zodiac from Ratchet & Clank: Going Commando definitely qualifies for this. Costs a ridiculous 2.5 million bolts, with ammo costing a further 10 thousand bolts a shot. The weapon itself takes 2 seconds to charge, before vaporizing all (non boss) enemies on screen. However the weapon can only hold four shots. Much more effective is to let rip with your 100 round, rapid fire R.Y.N.O. 2, for just a mere one million bolt purchase, and a hundred bolts a shot. Considering the amount of enemies ever on screen, the R.Y.N.O. 2 can take out what the Zodiac can in almost the same amount of time.
However, since the ammo for Zodiac could also be found in regular crates (a.k.a., for free), the weapon is suddenly not THAT impractical...
The Tesla Claw degraded to this in the transfer to the sequel. The ammo capacity is boosted even beyond the first game's healthy supply, but it can't be leveled up or modded, and the shift in gameplay style toward a heavier shooter emphasis between games means that you'll be facing a lot more opponents that are just too far away for the Tesla Claw to touch them. Thus, the wicked awesome crab claw that shoots lightning that saw a lot of use in the original game — to the point that the gold version renders nearly every other weapon superfluous — will probably spend most of its time sitting unused in your weapons list.
About 90% of the available Plasma Blade combos and almost all of the Boosters in the 3-D Castlevania-styled action game Nanobreaker serve no real purpose other than to put on a (somewhat nifty) laser light show for your opponents. For that matter, if it doesn't hit all around you or deliver a quick instant kill, it was essentially useless against the game's swarming Orgamechs.
Super Robot Wars lives on this trope. Many attacks sure look cool and are stronger than others, but the overall cost is usually greater. Case in point: Cybuster's Cosmo Nova only has a single shot, and requires a very high Willpower rating. It's the machine's best move, but you'll usually end up using the weaker Akashic Buster more often, simply because you can squeeze off many more attacks with it and sooner as well.
There are entire robots like this: massive, powerful combining juggernauts that require you to devote multiple deploy slots (to send out all the parts) and have nothing but over-the-top power attacks that suck energy like crazy. Like everything else in Super Robot Wars, you generally want to use the more robust components to sweep up the enemies and then start hammering the boss with the high-end attacks.
The SRX gets hit with this stick in Original Generation when combining first works; it only lasts three turns, dodges like a brick, can barely harm the R-Gun Rivale with its attacks, and a single hit by the Rivale's ridiculously overpowered weapons will push it to the brink of obliteration.
During an Earth route scenario in the SRX's first game, Hayato then informs the SRX team that the time has come to change out their engines, saying that only when combined as one can the SRX show its true power. The new engine uses Tronium, which requires a vast gaping plot hole to explain how it could be developed so quickly. The bad news is that at around three minutes of time at full throttle, the engine is likely to meltdown and explode - and the pilots surely won't make it through alive. For wanky game mechanical reasons, this means that the SRX can only stay combined for three turns, and once the pilots separate again they won't be able to recombine that battle.
This trope is actually invoked in universe with the Alt Eisen. It was the prototype for a proposed Gespeant Mk.III line of mass-produced mechs, and while it packs a lot of punch it's also incredibly difficult to operate, to the point that Kyosuke is the only person who can use it effectively.
Alto's Special Command "Dance of Wings" in Super Robot Wars UX. Alto learns it on Scenario 50, so there aren't many enemies left that it works on to be useful.
The Supernova weapon in Jak 3 kills all the enemies on screen. It also uses up all your violet ammo — the most powerful ammo in the game. This also applies to the violet ammo itself, which can also be used to reverse gravity for all your enemies except for you. Cool? Yes. Practical? Not so much.
The final unlockable car in Tokyo Xtreme Racer Zero is an extremely powerful Nissan Fairlady Z...but just like its inspiration, the Devil Z from the Wangan Midnight series, it's a challenge to drive.
The Field Shutter in Zanac, which is a shield that protects your ship from frontal attacks. Sounds good until you discover that it pisses off the AI and makes it throw more enemies at you.
It is, however, very useful for the Large Cavern. Hovering at the top of the screen and spamming Globus is a viable strategy for most of the rooms. Also, several of the secret rooms in Final Approach require it.
You can also use the glyph you get for completing the Training Hall to get to Dracula's room without Volaticus, but that's more of a Bragging Rights Reward.
"Classicvania" games have the pocketwatch, which stops time...for five hearts...and then some enemies are immune to it. Haunted Castle's version of it is an exception; it costs only two hearts to stop time for four seconds.
Alucard in Castlevania III: Dracula's Curse. He can only wield a pocketwatch as a subweapon, he attacks with small projectile orbs that does abysmal damage and when upgraded, it shoots 3 projectiles, but each projectile deals the same abysmal damage so unless you're close enough, he won't dish out much damage. His specialty is to turn into bat and float, unable to attack, which would've been good if this was a Metroidvania, not a Classicvania. Lastly? He has a huge hitbox, making him easier to hit or drop into the Bottomless Pit.
His bat form is useful for getting past some tough areas: for example, there's one section where you have to cross a crumbling floor with three Bone Dragons blocking your way. It's incredibly hard to kill them without getting killed yourself in the process. Alucard can just fly over the dragons and avoid the section entirely.
In Castlevania: Circle of the Moon, there's a secret item crush triggered when you have at least 100 Hearts and no subweapon equipped. Since you can only swap but not completely drop subweapons after you've picked one up, you'd only be able to use this by intentionally avoiding picking subweapons up until you've attained a 100+ Heart maximum and the DSS cards necessary to use the item crush command (unless you use the "use any DSS combo" glitch). The requirements mean that indeed, you'd want to try this out maybe once at the most.
In Moonwalker on the Sega Genesis, you can perform a special move that makes everyone on screen dance themselves to death. Unfortunately, it costs half your life.
And you can get a powerup that transforms Michael into Humongous Mecha Michael, but it is completely useless for any level other then the second to last one, as you can't rescue children with it equipped, so all you can do is blast away at infinite Respawning Enemies for a minute or so.
Any Arms Fortress in Armored Core: For Answer, but two stick out:
"Cabracan" is a heavily armored tank that carries hundreds of drones. But the skirt is hinged, exposing its treads when it runs into a mine.
The Kiku weapon, a melee-range pile bunker that can one-shot almost anything... If you can hit with it, and that's a big if. Fast enemies will zip right past you, and most others can be taken out with ease using mundane weaponry.
Utilizing it as a countermeasure against lightweight blade-based enemies yields far more favorable results.
Close Combat II has quite a few such units. Flamethrowers are devastating, but their crew are killed by rifle fire before they can engage as often as not. Flamethrower Tanks are more durable, but are usually overkill for fighting infantry, inadequate for fighting tanks, and cost a whole lot more than simpler vehicles. The entire Tiger tank line is considered overpriced. The British also have a Churchill tank that's capable of killing anything in one shot, but it takes a full minute to reload.
Almost half the special weapons in Mega Man Legends are of this variety. The laser sword, for example, is slow, does only moderate damage, and has no range... in a 3D game where every enemy has projectiles and deals contact damage. The only really useful special weapons are obtained within the first few hours of gameplay, and by the end of the game even they become useless because your buster gun is more powerful than any of them are and is actually practical for killing things. Add in the facts that you have to collect several items to even build them and then spend several thousand zenny just to power them up to their full potential, and honestly, there's little to no point to even bothering with them.
The Shining Laser and Active Buster deserve special mention, though upon final upgrade they become Game Breakers and beyond. The Shining Laser has extremely low capacity (it more or less uses one ammo per frame), but can kill the final boss, notorious for his defense, in about four seconds with its final attack upgrade. The Active Buster fires rapid-fire homing missiles and costs more than every other Special Weapon in the game barring the Shining Laser combined. You can turn with the Shining Laser but not adjust the y-axis and you are completely immobile with the Active Buster but with a maximum homing rating it will seek enemies behind you. The breaking point? Both weapons gain unlimited ammo upon the final energy upgrade and when fully upgraded are the strongest and third-strongest weapons, with Active Buster being at the limits of every stat besides attack, in the game when everything is said and done.
Oil Man's Oil Slider. For some reason the designers left him with no other form of attack, despite augmenting many of the other Robot Masters, forcing him to slide around like an idiot if he wants to do any damage at all. There's also the Spark Shock in MM3; the fact that you can't switch weapons while enemies are paralyzed from it makes it a far less useful weapon than the much-maligned Top Spin. They did fix it in the Game Boy game, however, making it a much better power overall.
Likewise the Rush armors from 6 and 7. 6 had the Jet armor that replaces the Rush Coil with a short burst jetpack and the Power armor upped your attack power, and could destroy certain blocks and some minor enemies in one hit. However, both forms cost you the slide and special weapons, the Jet also took away the charge shot, and the Power shortened your range, making it nearly useless in most boss fights. 7 gave an armor that could fly and give a rocket punch. It had the same weaknesses as the Jet armor, but the huge sprites reduced its flight to a crappy doublejump, which automatically launched you at an angle. Also, there was an annoying cutscene that played every time you equipped the armor in 6 - especially annoying in the Wily stages when you have to equip it, then switch to another weapon, then equip it again...
Mega Man 10 had two Awesome But Impractical weapons!
The Commando Bomb could inflict heavy damage on enemies, and could even pierce some armored enemies, but bizarrely enough if you fired it directly at them, it only did minimal damage. You had to aim it at the floor/wall/ceiling adjacent to the enemy for it to explode and cause the damaging shockwaves! You could also change its direction twice in mid-flight, which sounds cool, except that usually you really only need to do so once, and if you accidentally (or intentionally, to dodge an enemy's shots) move Mega Man sideways after setting the bomb on a vertical path, you will change the bomb's trajectory again, missing your target! Of course, if you gain enough skill to use it effectively...
The Thunder Wool was even more powerful than the Commando Bomb against stationary enemies, since it could hit them with two powerful zaps, but good luck actually hitting anything! The weapon launched a cloud that traveled up and to the side a bit, then zapped anything below it with deadly lightning. But if the cloud hit an enemy before it reached the top of its arc... congratulations, you just wasted a bunch of weapons energy, because it only does minimal damage and dissipates before it can zap anything!
A lot of X's helmet upgrades tend to fall under this. Aside from the secret armor upgrades, the SNES ones, and their Gameboy Expies, aren't much use once you memorize where they'd be useful. Play style or character preference does the same to most of the PS1 ones as many reduce weapon energy usage (which Zero nor the Ultimate Armor really need), the Shadow Armor speeds up sword attacks, and the Gaea Armor has no stated function at all. Aversions are X4 (game play is set up so weapons are still useful), X7 (attracts power ups from further away), and X8 (a weaponized form of X1's part and a quick charge, though the Nova Strike makes weapons useless again).
Mega Man X 3 has the basic version of the buster upgrade, but only the basic version. At full power, you can fire two charge shots that, with the right timing, could overlap into a five-shot barrage that could hit nearly anything on screen. The problem is that you have to get to full power, which was two levels higher than the basic charge shot, otherwise you're shooting a full charge and a half-charged shot that won't do anything. You can get one of two enhancement chips, though you'd want the Game Breaker version, that gives a reserve of charge shots to get around this, but the energy consumption is huge, and you can only recharge it by taking damage, but the amount of damage needed to recharge a single unit is absurd. If you get Zero's beam saber though, charging to full power creates a new shot combo that will include a wave of energy flung from the saber after the cross shot that can take off over half a boss's energy meter. The saber won't add to your charge time, and is included in the shot reserve upgrade. Add on top of that the fact that the screen wide shot is slow, making it possible for opponents to dodge it.
The basic version of the X3 armor is this trope. The completed X2 armor had three useful attacks and you could only use the X3 scanning feature when you first enter a stage. Granted, after adding both of the Game Breakers to it, it was the best armor of the SNES games, but again, you need both of them.
Mega Man X1 and X2 have the Hadouken and Shoryuken enhancements, respectively. Both can kill most enemies in a single shot; however, using them requires X to be at full health and standing perfectly still, rendering them completely useless for most bosses. Finding them can be a pain too.
Though some of the Program Advances in the Mega Man Battle Network series are quite useful/balanced, and worth including in an appropriate deck, others are nearly impossible to use, requiring that three or four chips each of which can only show up once in a 30 chip deck all come up on the same turn. Even for the useful ones, attempting to include more than one or two will stretch your deck too thin.
A popular combo for a while in the third game was "Disco Inferno". Without going too far into details, it causes the entire field to explode in a giant blaze (awesome) and deal amazing damage. The combo also required five chips in the correct order and could be circumvented by using any of the staple defensive chips, raising one's shield, or simply stepping back and firing the buster.
Magical Battle Arena has a classic usage of this trope, Yagami Hayate (Magical Girl Lyrical Nanoha) can dish out a ton of damage AND freeze enemies that are on nearly half of the entire battlefield. The catch? It takes an impossibly long time to charge.
The Flamethrower in Saint's Row 2. Sure, it sets your opponents on fire, which will normally kill them. But it has limited range, combined with the fact that enemies running into you while blazing will catch you as well, maybe torching that bastard isn't the best idea.
Similar to the flamethrower, there is also the final upgrade for the Tek Z-10 SMG in Saints Row: The Third which fires incendiary bullets. Given the close-range nature of an SMG, similar situations break out, only with a "flamethrower" firing in excess of 800 RPM with the potential for a stray bullet igniting someone you aren't aiming at. Doing this in a crowded area will likely cause most of that area to burst into flames. Yourself and allies included.
Note that this does become somewhat more practical later in the game if you purchase Immunity to Fire. Any allies following you are still at risk, of course, but upgrading them gives them enough health that they can survive being burned without fear of being downed from the damage. Of course, by the time you've got all these upgrades, you're probably close to invincible...
Xenon 2 Megablast has the Super Nashwan Power, which can be purchased in between half-level sections. It gives you a fleeting glimpse of awesomeness, upgrading your ship as far as it can be upgraded at any stage of the game, only to take it all away again after ten seconds.
Grand Theft Auto IV, since they took out the Rhino, Hunter and Hydra from its predecessor, San Andreas, The Annihilator' Helicopter remains the only weaponized vehicle in the game. While it does indeed sport a pair of twin miniguns either side of the cockpit, they are beyond impractical - if not completely useless. They do not auto-aim like the aircraft guns in the previous game (i.e. they fire directly forward at all times) meaning that to hit anything on the ground, you have to pitch the chopper forward at such an angle you either zoom straight over the target or crash (often both), but they also don't seem to do any more damage than handheld weaponry anyway. Furthermore, after just five seconds of firing, they have to reload.
Bottle rockets in Bully dart in and out of this trope. Fire once, and it usually hits for some nice damage. Fire more than once, they'll start ducking, or the bottle rocket will miss entirely. You can use it to make the sledgehammer boss a lot easier. (Since he moves so slow, he won't try to dodge and is easily kited.) But against trying to take out random people? You're better off using that to get the first strike and then switching to a more reliable weapon. (Even the Spud Gun is better.)
In Blood Omen: Legacy of Kain, you acquire the Soul Reaver, which both looks awesome and kills in one hit. On the other hand, it's a two-handed weapon, prohibiting magic of any sort, had a one hit combo which takes as long as two hits from the regular sword, and it drains your magic bar. As a result, the mace and the other swords are much more useful in the long run.
Raziel's version of the Reaver in Soul Reaver 2 could also qualify. It's a pretty powerful weapon and becomes more so if used constantly, but it destroys the souls of those it injures and kills (depriving Raziel of his major food source), and if overused will leech away Raziel's health.
The pistol in La-Mulana is half again as powerful as the Boosted Mace, hits instantly, and hits everything in its path. Problem? You get it pretty late in the game, and you can only get ammo by buying it at 400 coins minimum for 6 bullets per purchase, and you can carry a maximum of 12 bullets. It's only useful if you're trying to find all the ROMs, if you just want to massacre your way through the Dimensional Corridor, or if you're going for Hell Temple.
Gradius ReBirth's Type E powerup configuration has the Vector Laser, which pierces through any object, even structures, is as wide as the Ripple laser, and looks cool. Unfortunately, not only is it weaker than the other lasers, but it cannot destroy the regenerating walls in Stage 2 or the destructable dot walls in the bonus stages. Which means if you enter a bonus stage or get caught behind a solid regenerating wall in Stage 2-2 or 3-2, you're fucked. The only way to circumvent this once you've gotten the Vector Laser? Switch to the Vertical Shot, which is probably the worst Gradius powerup ever: it shoots upwards and downwards, but not forwards.
In Sailor Moon: Another Story, you start off with the Holy Grails of the present and future that can power up Sailor Moon and Sailor Chibi Moon (respectively) into their Super forms. Sounds awesome, right? Except Super Sailor Moon and Super Sailor Chibi Moon lose all of their attacks and combo attacks in exchange for one attack each. You've given a power-up to two characters who are better used for healing than for attacking, and taken away their healing abilities.
''The Legend of Zelda has the Magic Book, which makes your wand shoot fire. But this is worse than impractical. The fire does less damage than the wand spells to begin with, and more mosters are immune to fire than to the wand.
Zelda II: The Adventure of Link has the Thunder spell, which does heavy damage to every enemy on screen. But the cost for the spell is extremely high - with a fully leveled magic power and fully exteneded magic meter, one casting of Thunder still drains half your magic. And this is Zelda II. You'll be needing that magic for healing.
In The Legend of Zelda: Ocarina of Time, bombchus qualify. There's a finite amount of them available and there's no part of the game where they're particularly useful, let alone necessary.
The Ice Arrows and the Skulltula Family's renewable 200 Rupee reward also count. Having a source of infinite money is nice, but collecting all the Gold Skulltulas to get it takes almost the entire game to complete, at which point money really isn't an issue. And the Ice Arrows look cool but aren't any more powerful or versatile than any of Link's other weapons, thus making the difficult sidequest needed to obtain them into something of a fool's errand.
The three Goddess Powers in Ocarina were all hideously expensive to cast (Roughly half of the small Magic bar and 2/5ths of the boosted Bar) and their utility is questionable to say the least:
Din's Fire might be the least impractical; it burns everything in a 12 foot radius, but rarely you do fight enough enemies at a time to justify the cost, and it won't do much to minibosses. Its only justified use is opening the entrance to the Shadow Temple.
Nayru's Love made you invincible for its duration, but also impedes the use of items that also use the magic bar. Good on paper, but it can only be acquired around 70% into the game, when you have enough hearts to not worry about health anywhere but boss fights. The kicker? Not only is it useless against the Final Boss, he also requires Light Arrows to beat in his first form.
Farore's Wind might be the most worthless of them all. You use it as a Warp Whistle inside of a dungeon. You need to have been where you want to warp already, it fades upon warping meaning you must cast it again if you want to keep the warp point, and you need to get through an overly complex dialog for what it does every time you cast it. Enough progress to justify its use would imply one has already opened a Door To Before or acquired the dungeon's resident item which usually serves the same purpose. It's also pointless as an Escape Rope, since any Genre Savvy player knows that saving and resetting the game will do just that, for free.
The Legend of Zelda: Majora's Mask. The Fierce Deity Mask requires you to get all the masks in the game, and then beat the boss. The boss is made incredibly easy with the FD mask, and then its only usable in boss rooms after the end. And the Giant's Mask, incredibly awesome concept (giant Link!), but only usable against one boss.
In Twilight Princess, you can eventually purchase 3 different varieties of bombs, the third of which is bomblings, which crawl along the ground and then explode (exactly like the Bombchus of previous games). However, they're more expensive per-bomb and you can carry fewer of them than of the other varieties, and there aren't really any situations in which they're actually any better than regular or water bombs. Most players will fill a single bomb bag with bombling just for variety, and then never end up using any of them.
Bombchus were mainly used in OoT and MM for blowing up rocks that are on walls, ceilings, or out-of-reach areas. In TP, this is better done with Bomb Arrows - all that's required is regular bombs and the bow and arrows. It doesn't help that the bow is available quite a bit before bomblings.
There's a very expensive suit of Magic Armor which causes you to, when hit, lose Rupees instead of health. In theory, this could turn you into a nigh-indestructible juggernaut protected by his cavernous pocketfuls of gems, but it practice it's pretty useless because you end up running out of money (which is difficult to re-acquire) very quickly; stockpiling potions is easier and more effective. The armor also drains Rupees continuously — even if you don't get hit at all, 999 Rupees will only last you a few minutes — and when you run out of money its only effect is to make Link walk really slowly.
The Horse Call, which allows you to call Epona anywhere in Hyrule field, eliminating the need to find horse whistles. It's a neat item — at least it would be if it wasn't given to you about 3/4s of the way in the game, by which point, the player can have Midna warp them around Hyrule and has access to most, if not all, of the points you can warp to.
The good bee in a Link to the Past would attack enemies for you and more importantly not attack you. Unfortunately, it isn't anywhere near as helpful as fairies or potions, dealing scratch damage, easily being lost, and limited by Contractual Boss Immunity. The one boss exempt from that is Mothula, but one bee isn't strong enough on its own and having more than one for that one fight takes up too many valuable bottles.
Microsoft's space sim Freelancer features a single-player campaign that raises the player to about level 18 and ends with only a portion of the available star systems visited; the maximum level cap is 38, and there are many more systems that can be explored, including Easter Egg worlds inhabited by monkeys and robots. Players also gain access to exponentially more powerful ships and weapons at higher levels, such as alien lasers that inflict massive damage and require no energy to fire. However, once the main campaign has ended, there is no storyline to follow except for missions that are randomly generated by the computer, all of which follow a given template. The player has no real motivation to reach the higher levels except for the sake of completion.
Any game (that includes both video and table-top) that has a Necromancy/Shadow/Death magic skill-set with an exploding dead guy spell. It requires you to lure an enemy over a pile of corpses and then make them go boom. You'd be much better off simply raising those bodies and swarming him.
Imperishable Night's Malice Cannon does absurd amounts of damage, but requires you to focus and unfocus constantly in order for it to work. This makes dodging harder than it normally is. And considering that the series as a whole is intense even on Normal...
Prototype has a number of high-end moves like this, such as the Bulletdive Drop, which has awesome damage but has horrible prep time and is difficult to aim or hit anyone with since it has poorer tracking than the other drop moves.
Doing barrel rolls in the Afterburner games. You can dodge a number of missile swarms this way, but there are some frames of vulnerability as your bird comes out of it, so you can still get nailed if you aren't timing it right. If you're skilled enough, standard juking is better.
The Fusion Cannon was slow to charge, drained your shields if you charged it a bit too long, and was Nerfed in the second game. The Phoenix Cannon is a suped up ricocheting plasma cannon, but it chews through your energy too fast.
R-Type Final features 101 different ships to play through the game with. A few of these ships have "Final Charge" Wave Cannons, that can be charged up to truly devastating levels and kill anything, including every single boss, in one hit. The problem is that building up to the Final charge level requires forty-five seconds, in a game that sends waves and waves of enemies at you. Even bosses will likely require you to be constantly shooting in order to get rid of some of their projectiles. The only time it's useful is against the final boss, when every other ship gets it automatically (though it's not called such).
Every weapon in Iridion II that isn't the normal shot or the search laser. Thanks to the game's accuracy-based scoring, the latter two are the only weapons that can be used to get a high score; the radial gun, for instance, fires a needless amount of bullets in a sweeping pattern, which means your shot accuracy is going to go out the window if you use it.
Emperor Doviculus of Brütal Legend fights with a guitar with four necks.
Speaking of Brütal Legend, Lars Halford is this trope made flesh. A leader so inspirational people who have just met him will gladly march to their deaths beside him, but completely incapable of managing the day-to-day recruitment and logistics of an army in the field.
In general, many combo videos, whether for 1v1 fighting games or one-v-many beat 'em ups, are staged against a training mode dummy or a lone target and thus the very impressive-looking combos showcased aren't much good in real gameplay against an active target or a mob of enemies.
In The Godfather: The Game, the unupgraded Tommygun may fall into this. Tearing into enemy mobsters with a hail of bullets is awesome the first time, until you realise that your accuracy goes to hell with sustained bursts. Plus given that you will get torn apart fast if you stand out in the open rather than Take Cover, you can't really make best use of it in a real combat situation, as opposed to a solo encounter. The best way to make use of the Tommygun is to get into close range - and then why aren't you using the shotgun? It's far better to stick to taking headshots with a handgun. The more complicated Execution Styles also fall into this - while you will get more Respect for killing enemies with them, the setup is complicated enough as to not be worth it. To elaborate: One of these, the Traffic Accident execution, requires you to have someone else assist you in running over your target. To do so, you'll need to be at a roadside. You'll have to kill any other enemies first, since you're vulnerable while dragging your to-be victim around. You also need to position your target just so, then give him the shove that puts him in the path of an oncoming vehicle. Far too much trouble.
The Crystal Flash: L+ R+ Down+ PowerBomb when you have at least 10 missiles, 10 S. Missiles, and 11 (when you do it) Power Bombs and under 50 Energy with nothing in reserve tanks. This one is helpful in a pinch if you're trying to get out of wherever you are, but performing it is tricky and it doesn't work well for speedruns.
The Blue Suit. A glitch on the Draygon boss allows the player to kill it via shinesparking and be kept in a state of pseudo-dashing. During this time, pressing down prepares a One-Shot shinespark (not using it then causes you to revert back to blue suit), pressing Y cancels out of the blue suit altogether, and stopping on sand also disables it. however, as long as you are in blue suit mode, anything you touch takes damage, and you have no invincibility frames when you actually do end up taking damage. Reason for its impracticality? well, apart from the obvious (you can't shinespark without losing it, etc.), landing on a row of spikes is effectively death, and you can't save, quit, load the game and keep it. It's also not even practical for a Tool-Assisted Speedrun due to not being able to run.
In the Cult Classic Lucasarts game Afterlife (in which you develop a planet's Heaven and Hell), the generic zoning has many advantages. You don't have to deal with zoning your astral realm according to the seven virtures or sins, and the ultimate generic buildings hold more souls than the other, specialized zoning options. The problem? The best generic buildings are 4x4, while the others are 3x3, making a mess of your carefully-planned astral realms.
Sonic Adventure 2. Sonic's magic Hands ability. Useful on about one enemy in the game in Cannon's Core, only accessible after you have beaten most of Hero story, and it's usually much easier to homing attack enemies. However, the power to magically turn enemies into nothing like that is awesome, just that enemies have no health bars, which were introduced in Sonic Heroes.
zOMG! has the Fire Rain ring, which rains fire on surrounding enemies, ranging from flickers at low rage to firey meteors at high rage. Looks great, but rendering all that fire causes enough lag to give it the nickname "Lag Rain", which is not good when you're surrounded by angry enemies...
The Raiden series has the purple "toothpaste" laser, which can twist around to continuously hit enemies that it touches, often damaging enemies that get in its way. However, it doesn't just bend - it turns and loops, potentially several times, causing ludicrous figure-eight lightshows which are a pretty distracting sight when you're trying to dodge enemy fire. It also deals less damage than the spread shot at point blank or the blue laser.
The Hunter weapon in the Thunder Force series, a homing weapon that can pass through structures to hit enemies. While it allows you to happily fire away without any thought to aiming, it inflicts pretty poor damage, making it less-than-ideal for enemies that don't die in one hit. It also cannot destroy destructible walls in Thunder Force II through IV.
The Wave shot in Thunder Force V is a microwave-style Wave Motion Gun that pierces everything, but like the Hunter, it's pretty weak.
The lightning in MediEvil. Powerful (if slow) distance attack, but when you're out of ammo, it's gone forever; it can't be wasted at all.
Nukes in Cyber Nations. Using one on another nation inflicts massive damage to its military (including the loss of all defending soldiers), destroys massive amounts of land, tech, and infrastructure, and inflicts instant Anarchy (citizens take a massive drop in happiness, resulting in far lower collected taxes) for 5 days. Unfortunately, they're very expensive (and get even more so with each purchased nuke) and have strict requirements, one of which is having your nation be ranked in the 95th percentile or better. On top of that, every nuke launch is reported onto a publicly available page for all players to see, so if, outside of an inter-alliance war, you nuke a nation that's in an alliance, or are part of an alliance and fire a nuke without permission from superiors, you will be in very deep trouble.
Red Ivan, the trigger-happy explosives expert henchman in Evil Genius. His bazooka attack is one of the deadliest weapons in the game, but unless you micro-manage him during an attack, he'll probably end up doing as much damage to your base and your minions as to the forces of justice. Arming your mercenaries with flamethrowers is ill-advised for the same reason.
Pirates: Legend of the Black Buccaneer likes to play with the player's idea of the usefulness of certain abilities. One ability that you receive after the first dungeon is the ability to summon zombies that fight for you. The problem? They drain your health, can only be summoned at certain spots (justified, as they're probably graves or voodoo ritual spots. They can be found nearly everywhere, so thats good), can only be summoned one at a time, and move so slowly, that by the time they can actually be useful, you will have lost half of your health to the enemy, and half your health as price for the slow moving zombie. The exact opposite happens when you fight through a huge hell-like arena, killing demons, and your reward is the ability to cut down plants. Seriously, except its an extremely useful ability, due to the amount of plants around the early parts of the game.
Spore has the Planet Buster, which is Exactly What It Says on the Tin. However, buying even one of them is ridiculously expensive, and actually using it - even on an uninhabited planet - is against the Galactic Code and can instantly spark a war with every empire within several hundred parsecs. Besides, it deprives you of a planet to conquer, which is usually the reason you'll be attacking the planet in the first place. It's pretty pointless, really, unless your objective is to get certain Acheivements or to make a Youtube video with an epic conclusion.
In the Facebook flash game, King of Kung Fu, the skill "Pet Charm" is quickly becoming this. It allows you to control one of your opponent's pets (many of which are quite powerful). However, the three most commonly used pets are immune to it, and most of the others are simply not worth the use of it taking up one of your 18 skill slots.
In Raptor: Call of the Shadows, if you hold off on the fire button, you regenerate health. Problem is, it takes far too long to generate any decent amount of health back, and most of the things that kill you in that game do so by ramming, which is best countered by, well, shooting them first.
It sees use on a couple of bosses, where it's easy to dodge circles around them until you're back up to full health, saving valuable money on repairs.
Ace Combat 04: Shattered Skies's implementation of the Fuel-Air Explosive Bomb suffers from this. The FAEB has a bigger blast radius and deals more damage than the Unguided Bomb (Large). However, the only plane it's available on carries only 8 of the things. In contrast, the plane with the biggest UGBL payload mounts 18. You're thus more likely to get more targets sticking to UGBLs rather than using FAEBs.
The Grenade Launcher in Alien Swarm. Does major splash damage to enemies, but the shots arc and go to where your targeting cursor is, making it useless for run and gun tactics. The friendly fire damage from the weapon is INSANELY high, making accidental killings much more higher.
The Tricobalt torpedo in Star Trek Online is the most powerful torpedo weapon in the game. Fully skilled up and with weapon consoles that boots its damage, the torpedo can hit for 30,000-40,000 damage in a single shot; most fully skilled Cruisers(tanking class) only have 40,000+ hull hitpoints and 8000 shield points. The drawback? The torpedo takes 30 seconds to reload, compared with 6 seconds for a Photon Torpedo and 8 Seconds for a Quantum Torpedo, it cannot be overloaded with special weapon powers that can be done with other torpedoes, and unlike most other torps, it's incredibly slow flying and you can shoot it down with beam weapons, making it useless against a player with a good eye and fast reflexes. NPCs shoot them down fairly regularly too. Another problem is that torpedoes do about 10% of their normal damage on shields, so if the shields are up, you may do 3000 damage with it if you're lucky, and that's less than half the shield strength of a target ship.
It also takes 4500 skillpoints to fully skill up, and as skillpoints are very limited at max level, they should be spent wisely - a Quantum torpedo launcher takes 2700 skillpoints to fully skill up.
The Tricobalt can be great in an escort against big ships. If you can drop enemy shields and get close enough so that they can't retarget and hit it.
There's also the Federation Dreadnought Cruiser, better known as the Enterprise-D from the final episode of Star Trek: The Next Generation. Bulk of a Cruiser, firepower of an Escort, stealth of most Klingon and Romulan ships... And you'll probably be hard pressed to actually zoom into action and spend most of your time broadsiding and leaving your powerful Phaser Spinal Lance weapon to gather dust.
There's also the Transwarp consoles on the Advanced Heavy Cruiser series (re: the Excelsior and the Enterprise-B) Unlike the other, unlockable ones, this one allows you to warp to just about anywhere in an instant. The catch? Remember how it was sabotaged in Star Trek III: The Search for Spock? It still is here. Which means, you got a 50/50 chance of it going "CLUNK".
Dead Rising - The Megabuster and Z-Saber both count, as getting the Megabuster required you to spend a playthrough killing zombies while the Z-Saber could only be acquired by going through survival mode and lasting five days.
Not that the sequel's any better. During gameplay, you come across a tiger and her master. After killing the master, if you can find and feed the tiger three steaks, it will join you like a survivor.
Plants vs. Zombies has the Chomper, a plant which can One-Hit Kill any zombie that enters its range. The only problem? After it eats a zombie, it takes forever to chew it, leaving it open to attack during that time. You could put some Wall Nuts in front of it, but because of the Chomper's short range, any more than one will render the Chomper unable to attack. Thus, it'll likely only be able to eat one zombie before it's dead meatvegetable matter.
Put a second Chomper behind the first, though, and if the zombies eat your Wall-Nut, the Chomper behind the chewing Chomper will take out the first zombie to come at it.
An even bigger example of this trope is the Cob Cannon. It's an extremely powerful weapon - a flying Cherry Bomb with extra-wide splash damage. But the effort needed to plant even one (You have to plant two Kernelpults next to each other in the same row, then plant the Cannon upgrade on top of them at 700 sun points per, total), plus its slow rate of fire (takes about sixty seconds to recharge), slow shot speed (about four seconds between firing and detonation, plus you have to aim it manually) and its vulnerability (its large size and lack of toughness makes it a Glass Cannon) makes it a weapon only for achievement seekers. Tapping it once arms it, so an accidental tap will most likely end up with a wasted shot.
The Doomerang in Ty the Tasmanian Tiger, a remote-controlled boomerang, that can one-hit kill any enemy. The downside? It leaves Ty standing there defenceless, prime for attacking. It doesn't even go that far, either, so you can't just snipe enemies from 50 feet away.
Not strictly a spell, but the Plasma Bolt weapon in Wipeout tends to fall under this. It charges up for two seconds, then fires a straight line projectile that instantly kills the target? Great! Until you realise it usually isn't about kills (and when it is, you usually don't get this weapon) but about winning the race, and what you really want to do is not slow down to aim a plasma bolt and probably miss anyway, but hit the target with anything ASAP and overtake. Regular homing missiles are almost always a better idea. The weapon could have been more useful in the later games where you often end up competing with the ship in second place for a lap and eliminating this ship would give you an easy win, but its damage is severely reduced in recent titles.
The Visual NovelEien no Aselia has a lot of gameplay features. Near the end, you get some units called Eternals. They have what are the strongest attack, defense and support skills in the game... and you might very well never use them. Why? Because every time you use them, you lose a lot of Mind and they require you to have a lot to work at all. So you get to use them about twice before you have to switch back to normal attacks.
Scarface: The World is Yours. Drug-runs run the risk of gang retaliation. Pulling into the parking lot of a be-seiged business you own and machine-gunning all the bad guys? Awesome. But it attracts cop attention. So if it's one of the 'cheaper' places it's just ever-so-much cost effective to finish the run and use and inconsequential amount of the cash to repair the damaged place. Same reasoning is behind the rocket launchers. Plugging the car of assassins is fun; but more practical to outrun.
Air Force Delta Strike: Certain bonus aircraft like the Twin Bee fall into this category.
The Flash game Flight has the "Mystery Upgrade", a heftily-priced item at the end of the list. It is "a mysterious and impractical upgrade that is probably not worth the cost".
The EMP torpedo in Escape Velocity Nova. Almost completely disables an enemy's shields and ionizes them, reducing their turning to near zilch, but weighs two tons a torpedo, moves very slowly, can hurt you badly if you're caught in the blast and will get any government shooting at you if they scan you and spot it in your weapons load out. If you have escorts that use EMP torpedos, the only thing you have to worry about is the blast radius.
Also the Vell-Os Winter Tempest. It does crazy amounts of damage, and it skips shields and goes directly to damaging the armor. If you're surrounded by small-to-medium ships, spinning around once with it active will kill them. Grazing large ships twice will kill them. The problem is, it will drain your energy completely in a couple of seconds.
The Neddle of Fury in Otogi: Myth of Demons jacks your attack way up, letting you tear though enemies with ease. But lowers your defense a lot and disables your magic, most of the time you're better off with magic.
Cave Story: The Level 3 Blade is the most awesome-looking weapon in the game, summoning the ghost of King to slash wildly at everything in a fair-sized area. The Level 2 Blade, however, can do damage more rapidly and doesn't have to wait to reload. The Blade also maxes out immediately on attaining Level 3, so you have to avoid taking hits if you want to keep it there.
Missile frigates and bombers in X3: Terran Conflict can level entire sectors and lay waste to whole fleets by pouring huge salvos of missiles into them at extreme range. Fielding them requires the player to build up supporting industry to manufacture munitions and a strong supply line to deliver them. The same job can be done for roughly half- to two-thirds the money and with much less prep time with a Boreas or Osaka destroyer that has the player at the helm. It doesn't help that in stock X3TCthe AI doesn't know how to use their missile spam properly: it launches missiles singly rather than as massive barrages. (This last problem is fixed to a frightening degree in X3: Albion Prelude.)
Mitigated by the fact you only have to build the supporting industry and supply lines once. Late-game players looking for Self Imposed Challenges have little problem fielding M7Ms and M8s.
With that said, M2 destroyers are also Awesome, But Impractical as the player flagship. They're tough, powerful, and also really slow. They're also too big to dock at the vast majority of stations, and they can't carry any fighters for use as shuttles. For day-to-day operations, most players favor the high-end M6 corvettes (which don't need a shuttle), M7 frigates with docking ports (the Panther and Shrike are common choices), or the faster M1 carriers.
On the other side of the spectrum we have M5 fighters; they exist solely to be ridiculously fast and hard to hit, but the faster they are the less heavily armed they tend to be, resulting in the equivalent of mosquitoes: lightning-quick and almost impossible to hit, but also unable to deal any significant damage to shielded objects - and if something does manage to shoot them it's instant goodbye. Handing them over to the AI pilots also tends to result in them splatting harmlessly against anything possessing significant mass.
Through care or an exploit, Terran Conflict players can capture the carrier-class Terran #deca, and reverse-engineer it. Effectively gate-maximum size and with enough hangar space for fifty heavy fighters, it's a massive and frightening machine that dwarfs most other ships. Outfitting it with any weaponry, however, requires farming Kha'ak destroyers, and the resulting equipped ship is extraordinarily capital-intensive compared to most of the other options.
The ATF Valhalla, an enormous super-M2 that has 14 gigajoules of shielding, 32 Point Singularity Projectors, and 24 Starburst Shockwave Cannons. However, it is absurdly slow and hard to maneuver, and its size gives it some huge blind spots in its turret targeting. And it's so wide that it can't fit through jumpgates. Seriously, when it enters a sector in Terran Conflict, it bangs into the gate rim and loses its shields, reducing the ATF's trump card to a sitting duck. There's a good reason it doesn't spawn in vanilla TC. This last behavior is corrected in Albion Prelude, where the Valhalla warps in next to a jumpgate, not inside it.
Capital ships in general will ruin your hard earned reputation through no greater fault on your behalf than commanding one. Wherever you attempt to enter a sector, you should check the upcoming area's traffic if you can. If there is a merchant fleet anywhere near the jumpgate, your Capital ship will spawn on top of a number of them, destroying them instantly. Your ship will also be stripped of its shields and take a respectable amount of expensive to repair hull damage.
The Mortar in Gears of War, post-nerfs. It is great for flushing enemies out of cover, but it only has 3 shots, slows you to a crawl while carrying it, takes several seconds to fire, telegraphs its target, and locks you in place for several seconds after you fire. And on top of that, it's range can't even reach the opposite end of Gears' small maps.
The Boomshot in the first game is a grenade launcher capable of one-hit killing anything even through cover (and it's invaluable on the final boss), but its ammo is so scarce (it must be obtained from dead Boomers) that you'll probably discard it after emptying a magazine.
In Guardian Heroes, the character Randy has a super-powerful Kamehame Hadoken attack. The problem with it is that, for one, it costs a ton of MP, and two, it takes a long while to charge before firing, leaving you open to attacks from enemies. Worse still, if you get hit while charging, you will have wasted your MP.
In Gunz The Duel, a lot of moves qualify depending on the skill of the player, mainly because of how unlikely it is that you'll get in a situation where an awesome move will actually work. Then there's the usual useless moves like dodging which looks cool but it's slow and uncancellable and will likely get you killed. Those melee moves you see all the rookie k-stylers do? Nobody actually uses those unless they're out of ammo. Spamming shotguns? Better make sure you actually kill someone because you only get five shots per gun before you need to reload. Spamming a machinegun when you're not doing a quest? Please don't, you'll only get killed faster. Doing an über melee move that will land you a guaranteed kill? Better wait till you can safely close the distance because you will get killed if you don't. There's a good reason most players rely on the same basic tactics most of the time, all that other stuff is just too unrelyable.
Minecraft has Golden Armour and tools. The tools are the fastest in the game, gold armor can hold better enchantments than anything else, and they all look really cool, until Reality Ensues and we find out that Notch opted for the realistic structural density of gold, rather than the JRPG version, leaving you with what amounts to some "slightly tasteless evening wear."
TNT falls under a similar problem. For general use, TNT is good to destroy a wall or the like if you are mining, but the explosion radius is quite large and items caught in the center of the explosion are destroyed so if you are not careful, you can wind up destroying the rare diamonds or the even rarer emeralds.
If you beat all the races in Dethkarz with all the car classes you're given the Templar, an awesome-looking secret car with all the stats maxed. Which means it's very fast... so much that it's ridiculously hard to control it.
The bigger a weapon is in the MechWarrior videogame series, the more likely it is to belong here. Huge cannons, lasers and missiles make big booms and do a lot of damage, but they invariably have serious drawbacks that make them less efficient than a wise application of medium weaponry. Extremely large ballistic weapons have little ammo per ton and take a lot of space inside a mech, while large missile systems - not to be confused with the Boring Yet Practical ones that simply spew dozens of small rockets - add to those drawbacks the problem of being very vunlerable to anti-missile systems. As for energy weapons, the really big ones typically produce massive amounts of heat; your mech can dish out a lot of hurt every now and then, but while you're waiting for it to cool down you'll receive a lot of damage from less fancily-armed mechs that can chain-fire their medium lasers pretty much all day long.
Most homing attacks (and the characters who specialize in them) in shmups—i.e. Reimu-A in Touhou, the Hunter in Thunder Force, and Isha from Giga Wing. While their enemy-seeking properties allow you to "fire and forget", homing attacks tend to also be the weakest shot types in their respective games, doing less damage per second than full-forward shots and even point-blank Spread Shots.
In addition, they can really bite you when facing a boss that surrounds itself with self-replenishing Mooks, as your homing shots will go for the closer mooks instead of the boss.
In DoDonPachi SaiDaiOuJou, Expert shot falls under this. You get the best of Shot type and Laser type...but in return, the game jacks up the difficulty to the equivalent of second loop in other DoDonPachi games.
The S-Rank units in SD Gundam Capsule Fighter. While there are a number of amazing units in the S-Rank category, only a handful of them can be used in the Expert Co-Op modes that net you rare B-Rank unit blueprints and nine times out of ten, it's either the Neue Ziel or the Heavyarms Custom (Endless Waltz). In PVP mode, the effect is even greater, granting or losing the most points in Normal (+5), Boss (+25 if a boss/killed by a boss) and Grid (-5), only have 1.7 lives (your second life only has 70% HP) in Death mode and putting your unit in a ship for Grid and Tag takes up half of the space allotted, meaning you must use weaker units to get the most of your ship.
The Blunderbuss in Red Dead Redemption: Undead Nightmare. It's stupidly powerful and is very easy to obtain ammunition for, but can only load one shot at a time and has a very long reload animation, meaning that unless your first shot obliterated every enemy in the area, you're going to have to spend a lot of time running away as you reload. Oftentimes it's much more Boring, but Practical to equip a less powerful weapon with a larger magazine.
Shattered Steel has the huge rotary cannons. Flashy, noisy, powerful and perfect examples of the More Dakka philosophy, a Planet Runner armed with them can make short work of pretty much everything. However, somewhat ironically for a game whose tagline is "In the future you don't rebuild - you reload"... you can't. You get the 750 rounds at mission start, and that's it. Finishing them relegates you to only secondary weapons, which are powerful but themselves limited. Finish those as well, and you might as well reload the mission - and choose the lasers as primary weapon instead, which do less damage and sound a lot less awesome, but regenerate their charge when you're not using them.
In The Dead Linger, one of the melee weapons you can find to slaughter zombies with is an electric guitar. While it may be awesome to run around bashing undead hordes with a Les Paul, the thing is rather slow and weak and takes up more space in your inventory than stronger and faster weapons.
In Video Game/Phantasty Star Online Episode I&II, has a long list of weapons that requires ultra specific circumstances to obtain weapons that are no better then most other commonly dropped weapons. These weapons have underwhelmingly weak attacks coupled with special effects and animations found in no other weapons.There are however a few weapons that have insane stats nearly maxing out several stat categories, but by the time you can use them you're more than 70% to the max level cap and can easily solo most the dungeons, or they require you to be a specific raise and class. Great example of this is the Olga Flow weapons one there are one for each class they require a maxed stat for the class's specialized stat you're using but they deal insane damage and the effects are only triggered at 10%> HP. Reverse to this is the insanely rare weapon Sealed J Sword, dropped by a Gi Gu on 2-3 specific player ID colors with a drop chance very similar to that of winning the powerball. The sword for the work you put into is completely disappointing, even unsealed it is barely a step above the market trash you can purchase from merchants.
In flash game Epic War 3, the titan units are this. They're giant, have tons of health, do tons of damage, and just look cool, but take up a ridiculous amount of mana (which means you have to use less or weaker units and spells), are slow as molasses, and take so long to summon that you can easily beat even the Caves of Trials before they even show up. Worse, since the max populationnote maximum amount of units that can be summoned is only one for each of them, using another high-level unit with a larger population will usually end up dealing more damage per second than the titans!
The Ghost ability in Kirby: Squeak Squad, which has the cool ability of possessing enemies and using their attacks against other enemies, but encounters a few problems. Ghost Kirby moves very slowly and has no method of dealing with anything that can't be possessed aside from a possessed enemy's attacks, and almost all enemies that can be possessed are both exceedingly weak, quite slow, and die in one or two hits. As another tidbit, Ghost Kirby, much like UFO Kirby, can't climb down ladders, meaning if you come to a section where you have to do this to progress, you have no real choice but to abandon the power.
Metal Gear Rising: Revengeance: Lampshaded in-universe. Sundowner has a pair of swords that can form into a single scissor-like assembly on one arm. Raiden, Boris and The Doktor both question the tactical validity of such a weapon; given what a psychotic individual Sundowner is, it's quite likely that the sheer cruelty of the weapon is what attracts him to it.
Grief Syndrome, a Puella Magi Madoka Magica fangame, has various Signature Moves that turn a portion of the health bar blue (regenerates unless you take damage, at which it becomes permanent). Mami's is, of course, Tiro Finale, a BFG that fires a destructive blast capable of decimating most bosses. However, the gun itself is too long; the barrel is about a third the width of the screen, which means it does absolutely nothing to enemies at point-blank range. To make matters worse, it turns nearly all of the health bar blue, meaning if you take a hit after using Tiro Finale, you'll almost certainly die. (Which is fairly accurate, all things considered.)
Mami in general could be considered an example. She's the game's only character to mix ranged and melee attacks, she's very mobile, and she has the second-highest Soul Limit next to Madoka, but her mixed attacks have a deceptively small range and low damage, and when she dies, it takes a substantial chunk of her energy to come back (from 15% to 26%, the highest in the game). She's considered to be the hardest non-joke character in the game to play properly.
Ingress has pretty portal animations...that also reduce your mobile device to the speed of molasses. It's to the point where an illegal third-party client offers the option to turn the animations off.
In Resident Evil 0, you can, for the first time in the series, have two characters in the same room shooting at zombies for the entire game, whenever you want. While this may sound like an awesome and much-needed bit of help, it's ultimately impractical since the character you aren't controlling is controlled by the AI — which does a piss-poor job of managing ammo and helping you out when you're getting attacked. It will routinely waste shots on enemies who can't be hit (such as a zombie in his "getting up" animation), or fire more than enough bullets to drop a zombie. In a game where ammo is already scarce, bringing your AI partner along mostly just increases the difficulty by emptying the ammo that you worked so hard to secure. Not to mention that the AI does nothing to avoid enemies and simply stands their ground while firing. This means you'll often have to use hard-earned healing items on a stupid AI partner who could've easily avoided an attack.
In Xenonauts, there's the Singularity Launcher. It's the last infantry-weapon you can research, and sets off a HUGE explosion - easily two or three times great in area than anything you could do with even the Fusion Explosives that comes before it. On top of that, it's got great range, and remarkable accuracy. In the right situation, it could annihilate an entire squadron of entrenched aliens with a single shot. However... such situations are liable to be rather rare, in what little remains of the game at that point. Using it inside a UFO is practically guaranteed to destroy something you were hoping to recover and needless to say, any aliens caught in the blast will have their equipment junked as well. More problematically, it's so heavy that it can only be wielded by someone wearing Predator Powered Armor - a clumsy Mighty Glacier suit that lacks the flexibility and flight-capabilities or the more advanced Sentinel Battlesuit. Worse yet, the Predator is too clumsy to use anything BUT the heaviest of weapons - so you can't pack a sidearm for use in situations that warrant something less than the most extreme explosives in the game. And even WITH the strength of that massive suit behind it, the Launcher is so heavy that it'll slow the unit down by a margin of over 30% - leaving him or her lagging far behind your less encumbered troops, not to mention having to practically remain stationary for the turn if he actually wants to FIRE the thing. Oh, and if you want to bring any extra ammo for it, that'll slow you down even more. Comparatively, the basic Rocket Launcher is available right from the start of the game, with incremental increases in firepower as you develop better explosives - and with Fusion Rockets, it's quite capable of pulping most aliens in a single shot. A well-trained soldier can easily carry it AND several rockets AND a sidearm, without any encumbrance.
It compares interestingly to the Blaster Launcher from the original X-COM: UFO Defense, an Awesome Yet Practical remote-controlled missile-launcher capable of destroying enemies from around corners and the other side of the map. Most likely, the refusal to include such a weapon in Xenonauts was a deliberate choice on the part of the designers, since it was kind of a Game Breaker in the original...
The Matrix Path Of Neo has an awesome, unlockable combo called "The One". It's just too bad that it has about thirty buttons to press, along with joystick movements on console versions. Even if you start it against an enemy, unless it's a very powerful one, that enemy is likely dead by the time you're, not even, half finished. Thus, making it practically useless.