"A flashy feature that has limited usability for victory"
Uryu Ishida from Bleach possesses an attack like this—the deadly Sprenger, which requires him to set up a pentacle of Scheele Schneiders around his opponent and detonate their stored spiritual energy in a focused blast. He remarks that the attack takes so long to set up that it's impossible to use it in battle without a partner to distract the enemy.
Even his ultimate attack in the Soul Society arc functioned that way. Tearing off the glove briefly gave him immense spiritual power, after which his powers were drained to nothing. Awesome.
And now, we have Soifon's bankai, which is big, clunky, requires three days for full recovery from one use, and above all loud and flashy, not exactly the best tool for a (covert) assassin.
And yet, it still didn't work out. Naturally, it would kill everything, but we all know the reason for it not working out.
The Final Getsuga Tenshou, which Ichigo used to curb stomp Aizen. Funny thing is, he didn't even NEED to fully release it, Aizen had been turned into a pin ball even after going One-Winged Angel. But what makes it really impractical? Ichigo loses his Shinigami powers.
Then there's Ikkaku's Bankai, two massive swords attached to an even larger blade on Ikkaku's back. The weapon gradually gains power as Ikkaku fights, and is incredibly strong when it reaches full strength, but the problem is that his Bankai is very brittle and unsuitable for prolonged usage.
Made perhaps the king of this trope for Bleach in that with the reveal that Bankai very rarely naturally repair themselves, having a Bankai with the side effect of "becomes easy to break" essentialy means he has the self sacrificing price of the above abilities... but a power boost that only puts him on even footing with other captains.
Kyoraku's shikai leaves him Blessed With Suck, as the 'games' apply to him and can be exploited by his opponent. And, while not shown, it's reasonable to assume it would be problematic if he were to use it in the company of allies, because anyone nearby is said to be subject to Katen Kyokotsu's rules. And she appears to change those at whim, with multiple sets in effect at once.
Yamamoto's shikai alone, given that even his Battle Aura has a paralysing effect on anyone lacking the spiritual power to withstand it. There are three people who have been known to fight him when using his zanpakuto. Two are his students, the other is Aizen, who technically didn't bother, being Crazy Prepared enough to bring along a power nullifier.
Subverted in the "Phantom Bullet" arc of Sword Art Online: Photon Swords in Gun Gale Online possessed incredible attack power, but were considered this trope due to their limited range, meaning that by the time anyone got close enough to attack with one, they'd be riddled with bullets. Kirito, however, manages to transform the Photon Sword into a Lethal Joke Weapon, using his personal experience in VRMMO games that emphasized swordfighting to not only dodge bullets, but deflect them using his Photon Sword before going in for the kill.
In Project A-Ko, Mad Scientist babe B-Ko unleashes her killer mecha the Max 5000. It combines from several shape-changing vehicles in a drawn-out Transformation Sequence, she boasts of all its amazing killer powers and exotic weaponry, it's even referred to as "The Blue God of Death". But its gadgets and elaborate transformations don't leave any room for the pilot to control the thing, so it just stands there inert until the Heroic Schoolgirl A-Ko kicks it and it falls down.
The Double Zeta Gundam, a massive (even by Gundam standards) Combining Mecha with many BFG's, the most powerful one built into its head. The catch? it can only fire them about two or three times before the suit's capacitors are completely drained of energy.
MS Igloo is based on this premise, though some Super Prototypes like Hildolfr tank turn out to be effective. The worst offender is the Jormungand's BFG from first episode, being a big target with low accuracy and a very long cool-down period between each shot. What makes the Jormungand stand out even more is the fact that it was hauled out and only fired three shots... from its own crew out of sheer frustration that Command was ignoring them. When their XO decides to go out and give his crew the info they needed, Char Aznable shows up and tells them to just go on back, the Mobile Suits got this. Which most likely means that Zeon build a humongous gun for nothing more than show.
The Zeon loved this, constantly creating new classes of mecha instead of just concentrating on a few like the Gouf and Gelgoog. In particular, the Big Zam was based off of the "super tanks" from World War II that were prohibitively heavy and slow. It could take on long-range roles, but was useless at close-range.
In Victory Gundam, beam weapons have become so powerful, that the equivalent conventional weaponry (read massive Gatling gun) has too much recoil for it to be aimed properly.
Gundam Wing, the Wing Zero, a nigh-unstoppable mobile suit designed for wholesale destruction, rapidly becomes this when used for anything requiring just a bit more precision. Nothing says Awesome, but Impractical like trying to defend civilians when your primary long-ranged weapon can one-shot the entire colony you're fighting in, and your ultra-sophisticated cockpit system is busy trying to Mind Rape you into pulling the trigger.
Side materials claim the Wing Zero's Twin Buster Rifle (and by proxy the Wing Gundam's singular Buster Rifle) have variable power outputs and can be altered to "standard" beam rifle levels on a whim. That does little good in a space colony, since any kind of weapon discharge (even machine gun fire) may puncture the colony's hull and spell destruction for everyone within it. The latter was why the Crossbone Vanguard of Gundam F91 primarily used shot lancers (machine guns with a jousting lance attached) instead of beam rifles.
From Gundam X is the Gundam X. On one hand, it's pretty good specs-wise and possesses the almighty Satellite Cannon (designed to vaporize colonies). On the other, the damn thing has hilariously long charging and cool-down times, requires a direct view of a moon-based facility, and needs a Newtype pilot for the first activation. What's more, it's only mid-range weapon is a weird rifle/shield hybrid, making the use of both at the same time impossible. Thank god the Double X ironed out most flaws (it has both a rifle and a shield, and the cool-down of its Twin Satellite Cannon is greatly reduced). And in the meantime, the original got remodeled into the Gundam X Divider which scrapped the Satellite Cannon and replaced it with the Divider Shield containing lots of smaller beam cannons.
The Phase-Shift Armor of Gundam SEED. When it was first developed, it was Plot Armor, as it was meant to deflect everything short of beam weaponry, which ZAFT didn't have at the time (aside from in the form of battleship cannons, which are too slow-moving to effectively target mobile suits). However, when ZAFT stole four of the five Gundams, they got their beam weaponry, it was practically rendered moot. This doesn't include the fact that the armor ran on the suit's battery, which meant that it was draining from it and using the high-powered beam weaponry some were armed with drained the battery faster.
PS Armor and its weaknesses are actually a standard theme in Gundam, spawned from the original series itself: Gundams are almost always equipped with super tough armor against any and all non-beam weapons, which allows them to be invincible at the beginning of the series (when everyone is still using machine guns and heat blades) but more or less vulnerable later on when everyone has gained beam weapons. That said, one could say that Gundam armor in general (Luna Titanium/Gundarium/Gundanium/E-Carbon/whatever) is Awesome, but Impractical, especially when it comes down to the fact they're usually too expensive to use on mass production units.
In the Mobile Suit Gundam SEED Astray OVA, Lowe obtains a BuCUE head from a dealer at an auction. While having a twin-bladed beam saber that rotates is awesome, it takes away a hand from his Red Frame, keeping him from using the Gerbera Straight katana, still uses too much energy from his suit and, when he leaves it behind, it laments on the fact that he just lost about a million credits.
In Gundam 00, the Burst Mode of Gundam Virtue's GN Bazooka it powerful, but it takes Virtue five minutes to recharge after firing it. And on the battlefield, five minutes can make all the difference.
00 had so many Awesome, but Impractical devices that the whole series practically ran on them. From the Gundam Nadleeh's Trial System (only worked on MS connected to Veda, which were few and far between in the first season) to the GN Drive Tau (produced limited energy and early models were radioactive) to the Gaga (little more than a piloted missile, and it took them by the dozens just to break through a GN Field) to the various side story prototypes that were thrown out at random, one wonders how any of these things made it off the collective drawing board, or why anyone would have wanted to use them (well, except the GN Drive Tau anyway).
Seven Swords... Why on Earth would anyone find it necessary to carry seven swords on one Mobile Suit? It only has two hands to hold them, after all. Sure, it's awesome and Setsuna manages to put all swords to use once, but the one time he does it on-screen, he destroys six of the Seven Swords, just because he had to destroy the enemy he'd stabbed six of the swords into.
Dynames' GN Full Shield counts too, since its huge size makes it impossible for Lockon to use the shield and snipe at the same time (the shield covers the entire front, save the head). In fact, the only time the GN Full Shield becomes remotely useful is during the first Lockon's final battle, while he's using the GN Arms, and when the GN Arms Type-D is destroyed and Dynames fights on its own, its arms and legs are blown off, so the shield is just drifting through space, being utterly and completely useless.
In the original Mobile Suit Gundam, beam rifles were exactly this, as they were insanely powerful at the time (as they were the means to make a Mobile Suit have the power of a battleship), but they had a few drawbacks - your suit needed to have the sufficient power to power up a beam rifle's charges to fire, you had no way to reload on the battlefield and they were quite expensive. It wasn't until suits began having stronger and cheaper powerplants and the advent of the E-Cap device would beam rifles become more practical. For example, the GM (a toned-down mass production model of the original Gundam) had to make do with a shorter-ranged and less accurate "beam spray gun" because it couldn't handle the Gundam's full-power beam rifle.
Mobile Suit Gundam 0083: Stardust Memory and the RX-78GP02A Gundam GP-02A "Physalis". Designed for fortress smashing, it was armed with a nuclear warhead in the event another Zeon would try that stunt again. After the nuke is used... it isn't much use. All it had afterwards was two beam sabers and head vulcans, which meant it needed help should the opposition confronts it afterwards.
Similarly, Dragon Ball Z has Goku's massively powerful Spirit Bomb attack, an attack so ponderous it takes several episodes to fully charge—he also invariably needs help holding off the baddie while he does so.
Goku's Kaiohken can multiplicate Goku's energy several times for a short times but his body is seriously damaged when using it. It's not weird that he stopped using it after fighting Frieza.note Then again, he got the Super Saiyan transformation while fighting Frieza, so he didn't really need Kaio Ken anymore.
Also, Piccolo's Special Beam Cannon, at least at the beginning of the series when it takes an inordinately long time to charge. It's because when he uses it, he's in the middle of not having an arm. According to Piccolo, having both of his arms would've let him charge faster.
Also, in the two advanced stages of the Super Saiyan (not to be confused with Super Saiyan levels 2 and 3, those are different), the Saiyan becomes far more powerful, but as a result he slows down greatly. Goku and Vegeta realized this drawback of the form right away and worked out a way to only partially use it so that they'd only lose a small amount of speed, while Trunks learned the hard way.
Super Saiyan 3 itself also counts. It's the most powerful Saiyan transformation seen in DBZ, but consumes an enormous amount of energy, which has the side effect of reducing the length of a normally 30 minute fusion to about 5 minutes. It isn't even an Eleventh Hour Superpower like the first two. it appears early in the arc and is well outclassed by the villian's later forms, as well as Gohan after his hidden potential is released, who suffers no power drain.
Now we got Super Saiyan God, a form that requires six Saiyans and has such a high power drain, Goku barely has time to use the thing.
Zoids: New Century Zero - Bit Cloud gets the amazing, highly offensive Panzer unit upgrade for his Liger Zero, at the cost that he cannot move (in the anime; in the manga, he can move slowly) and the zoid overheats and he nearly melts in the cockpit.
This covers of the special "Invisible 9" units in Pumpkin Scissors in a nutshell, which feature soldiers transformed into supersoldiers in order to counteract design and equipment failures, or to accomplish things that could otherwise be done cheaper with technology improvements. The 908 High Temperature Troopers, for example, use souped-up flame-throwers that are so powerful they cook the users alive. Their "protective suits", rather than actually shielding them from the heat and dispersing it, are instead filled with special chemicals that keep them pain-free and able to function, though they die quickly and in hideous pain if they take the suit off.
Sailor Saturn, otherwise known as the Senshi of Ruin, has power that far eclipses the other senshi (save for Usagi herself.) In the anime, she states that she can destroy an entire planet by herself. The downside is that using it kills her; she takes out one season's Big Bad (she gets better) and then against all the others after that never gets to use it again.
Giga Slave from Slayers. Sure, you can destroy any enemy/mazoku with it, but chances are you're going to bring on Instrumentality or worse, be possessed by the (dark) creator of the Universe and most likely die from that mere fact (like it happened to Lina in Next, only surviving when the Lord of Nightmares saw Gourry's Undying Loyalty to her) and if/when you manage to dodge all the above, you still lose your magical powers for a few days andyour hair might turn white until you recover.
The very first episode of Yu-Gi-Oh! (Duel Monsters anyway) has Yami Yugi winning the match by summoning a monster named Exodia, whose power is defined as... well, winning the match. Obviously it's as powerful as you can logically get, but the caveat is that you need to gather all five parts of Exodia in your hand... which is roughly equivalent to drawing a royal flush of a specific suit, one card at a time, while your opponent tries to kill you, after managing to collect five extremely expensive and rare cards, and then adding them to your deck despite the fact that they are absolutely useless by themselves. When confronted with it, his opponent is suitably shocked and claims that nobody had ever managed to win this way before, which is pretty believable.
Though later in the series we're introduced to a character whose entire deck is built around Exodia and cards to stall the game or draw cards. And a character who uses Exodia Necross, which is basically Plan B for an Exodia deck.
There is also the anime-exclusive card "Ragnarok", which Yugi uses to finally get rid of Marik's Winged Dragon of Ra in the Battle City finals. It removes all of the cards on your opponent's field from play, which is good, but under what conditions? First, you need to have two "Dark Magician" family cards in play, which isn't hard to manage. Second, you need to remove every other monster in your hand, deck, and graveyard from play. Presumably, you could smack the opponent for 4,000+ damage with the Dark Magicians afterwards, which is more than the player starts out with, but there are easier ways than THAT. Even then, Yugi still could have lost. The real point of the move was to weaken Marik's Superpowered Evil Side which had fused itself to the Winged Dragon of Ra to power it up. Destroying the dragon freed Marik from his evil side's control. His evil side tried to convince Marik to continue the duel, but Marik, grateful to Yugi for freeing him and abandoning his lifelong grudge, forfeits the duel.
Yu-Gi-Oh! GX has several Duel Disks that certainly qualify. There's one than spins like a buzzsaw, one that appears to be made of three razor blades, and one that fires cards like bullets out of a gun. A type similar to the latter shows up Yu-Gi-Oh! 5Ds, which also features some very impractical motorcycles.
Speaking of GX, Adrian Gecko plays a few cards in his Cloudian deck that kill him if he doesn't win that turn. Big Summon Cloud sends all the cards in his Deck to the Graveyard just to Special Summon a Cloudian from his hand. He wins anyway.
Yu-Gi-Oh! 5Ds has Sleeping Giant Thud, a monster that can't be beaten in battle, or spells and traps, and can destroy any monster it battles. Only problem? You can summon it only if you have a LV 1 Monster on the field for 20 TURNS. In an anime that barely gets past 7, this is...near impossible. It's never been summoned past Team Taiyou.
The titular character of Gash Bell has the spell "Jikerdor", which magnetizes and immobilizes enemies. Seems awesome, until you realize you need a large amount of metal for the spell to make contact with, and very rarely do the opponents conveniently fight in a location like that. In fact, a filler arc where the Mooks consisted of iron-suited baddies were the only time the spell became truly important.
Drifting as dramatized Initial D looks good in the anime, watching the cars slide and such. But in practice, drifting is more or less a spectacle rather than an often used technique in racing. Most of the reason is because your tires will wear out very fast.
Averted in the later seasons(stages) when exhibition drifting wasn't really used. The drifting in the later stages were more akin to drawn out versions of rally drifting. 
The Pactio cards in Mahou Sensei Negima! can be used for Telepathy. Cool, right? Except that you can only use them to send thoughts to a single person, who can't communicate back unless they have their pactio card with them, and telepathy is very easily blocked. Several characters point out that a cell phone is generally superior, as you have two-way communication, and can call multiple people with it. Negi also has a very limited mind-reading ability, which requires him to put his hand on his target's head for a few seconds, and walk away with maybe two words, which are likely useless. To be fair, Pactio telepathy has been around for a thousand years or more. On the other hand, telephones are less than 140 years old, and decent cell phones have only been around for less than two decades. So yeah, Pactio telepathy is far inferior to cell phones, but they've been around and in use for much longer. Modern day technology has simply made it redundant.
Also, we may not be seeing Pactio telepathy at its best. All of Negi's own are a probationary version meant for people who are considering making a Pactio - just backed by such ridiculous levels of power that it's possible to overlook the difference. The others have been either involving people equally immature or other "abusers of the system". No reason to think two people who have fully committed to it and spent a decade or two working on the bond couldn't do better.
Well, telepathy is just a bonus power of pactio, after all.
In One Piece Luffy's Gear Third as soon it has been used will reduce Luffy's size turning him into a midget, or toddler as he is smaller than even a small child. As well as reducing his size, his power is also limited, and he loses the ability to use his devil fruit powers. This requires Luffy to run and hide until the effects wear off. This lasts for the same amount of time that was needed to use Gear Third.
However, after the Time Skip, he seems to be now able to use it without shrinking at all, and can use the Color of Armarments to make it stronger.
Also similarly ALL of Frankie's attacks can be considered this as he uses up hiscola using normal attacks, more so when he uses his charged attacks. This causes him to seek more cola, until then his strength is severely reduced, also causing him to employ his teammates help. Also, any edible liquid other than cola can be used as a power source; HOWEVER, doing so can and will alter his personality, to a degree which causes him to NOT want to attack. Therefore it is imperative that his only powersource be cola.
A recently introduced character can punch hard enough to destroy fortresses and (allegedly) could at a minimum severely injure a yonkou. The catch? He has to warm up for an hour to throw a single punch.
In InuYasha, Miroku's potentially phenomenally powerful wind-tunnel, which sucks anything and everything in the vicinity into a void in his hand, is rendered and utterly useless by a permanent swarm of lethally poisonous insects called Saimyosho which constantly buzz around the main antagonists. There are a few instances in which Miroku uses the curse anyway as a last resort, but the vast majority of the time he is prevented from using it. To be fair, without the Saimyosho to balance the field, his power would be quite a Game Breaker. Also later on in The Final Act, Naraku orders the Saimyosho to stop protecting Hakudoshi, allowing Miroku to use the wind tunnel to destroy him.
Break Shot manga. Chinmi and gang do ALL SORTS of awesome and cool and flashy shots which involve jump shots, 8 ball combos, air resistance, etc... all to sink a shot that could have been done in 1-2 normal straight shots most of the time. The crowning moment is probably when the 8 ball is in the corner pocket and all Chinmi has to do is tap it in... but he's afraid of sinking the cue ball. Does he do what any normal player would do and tap it on the side or put some backspin on it? No, he does some crazy 20 foot high jump shot!
In the Shaman King anime the main characters develop giant weapons referred as Giant Oversoul (Giant Spirit Control in the dub) that drain his spiritual energy. Due to the excessive use of energy the main character Yoh reduces his Oversoul-Spirit of Sword/Grand Halo Blade so that it will not use to much energy, secretly making it stronger since the energy is controlled and stabilized. In the manga the characters instead learn the third, improved version of the Oversoul, Armor-type Oversouls, that is less tiring, stronger, and maintains furyoku.
The power to steal souls (or portions of them) at the cost of your own
Gaara's Armor of Sand that coats him in a skin-tight, impenetrable armor capable of blocking high-speed taijutsu. but drains his chakra much faster than his active defenses and significantly limits his movement.
The signature jutsu of the Yamanaka Clan, the Mind Body Switch Technique. How can a jutsu that can swap your body with an enemy allowing you to do absolutely anything to them not be awesome — by creating a technique that leaves your original body defenceless, by creating a technique that doesn't allow you to physically injure your opponent in any way because it would injure you too, by creating a technique that only moves in a straight line and is slow enough to be dodged by first year Genin, by creating a technique that uses so much chakra it can't be used immediately afterward forcing you to wait whilst your enemy uses their less stupid and useless jutsu on you. Yeah, awesome technique that.
Which is why the Yamanakas have a more advanced jutsu that lets them take control of an enemy's body without losing control of their own. It's just that Ino hadn't mastered in Part 1.
Naruto's incomplete version of Rasenshuriken. Causes damage on a cellular level and one-shots most of his foes, but it deals extensive damage to Naruto's own arm to the point that he can't use that arm to eat temporarily. If it weren't for his Healing Factor, he would've lost use of the arm permanently, and the rapid cell division required to heal the damage shortens his lifespan.
Madara Uchiha can summon a freaking asteroid, and yet the size of the intergalactic projectile makes it impossible for Madara himself to dodge its impact.
Sasuke's ultimate attack until he got the Mangekyo Sharingan, Kirin. Powerful enough to destroy a mountain, it takes an ungodly amount of preparation to use. First he needs to generate enough heat via flame jutsus to heat the atmosphere and form a thunderstorm. Then, he shoots even larger fireballs directly into said thunderstorm to generate a massive potential in the cloud. Finally, he needs to shape the lightning and guide it to his target. And in the end, it didn't work. It's awesome in that it's instantaneous and undodgeable even by extremely skilled and fast ninja... Impractical because it can be blocked just fine.
The Mangekyo Sharingan causes its user to goes blind from overuse. And for some people "overuse" can happen rather quickly.
And for Uchiha Itachi unless in Edo Tensei form where he has unlimited stamina and chakra. It's much worse. Itachi is one of ''the strongest'' ninjas in the whole world, gifted with three incredibly haxxed techniques. But Itachi...has genin level stamina. He has only three uses of it before he exhausts his normal supplies of chakra, more than that kills him. Using the MS five times during his battle with Sasuke is what partially killed him-accelerating his disease AND adding chakra exhaustion to boot.
Hidan's curse jutsu. It is a highly complex technique to follow: first, Hidan must ingest the blood of an opponent, from which he must then undergo that skeleton man transformation and then draw a large Jashin marking (an upside down triangle within a circle) on the ground. So long as he's within the circle, the jutsu is active, whereas if he moves out of it the jutsu is cancelled out; which means he's now stationary. That doesn't sound so bad, right? Well, then there's the real clincher: it can only be used on one opponent at a time. That's right, after all that setup, Hidan can only inflict damage on one target, all the while remaining vulnerable to said target's teammates, who can more or less work to force him out of the circle. Oh yeah, and due to the nature of the jutsu, whatever damage Hidan wishes to inflict on the target he has to inflict on himself first; in other words, if it weren't for his immortality, he would have to kill himself just to take out one guy. Overall, the jutsu seems to only work for sadomasochists (which Hidan happens to be); otherwise there are far more efficient and effective techniques in the Naruto universe.
Kakashi's Chidori was this when he first developed it. The extra speed and power the technique grants its user also causes tunnel vision, making it impossible to avoid counterattacks. His teacher Minato (the future Fourth Hokage) warned him not to use it again since it was an incomplete technique. A Sharingan can remove the tunnel vision, making the Chidori more practical in the hands of a Sharingan-user (if it could actually kill someone).
In Digimon Frontier Velgemon's strongest attack is a OHKO technique. Probably. You can't be sure, because everyone he uses it against manages to escape the blast radius since it takes so long to perform the technique.
MetalKabuterimon has a similar technique - according to Wikipedia, it's the strongest attack among the 10 warriors. But he needs to be perfectly still when performing it and it takes long to charge up.
Excalibur is a variation on this trope. First off, he is quite literally the most powerful weapon to have ever existed, having enough power to let an average student defeat three of the top meisters in the school in only a few seconds. Second, despite having so much amazing power and invincibility, he also has a perfect soul wavelength, allowing anyone to wield him. His powers are pretty amazing too: although other weapons are very unique, he gives his meister abilities that one would usually have to either resonate or become a death scythe to obtain, without even trying. These include making the air vibrate by simply turning into a weapon, giving his meister "wings of light", allowing him/her to fly, not to mention the ability to move so fast that it appears as though they are teleporting. With just one swing, he and his meister can travel through space! What could the downside possibly be? Well, it isn't that his powers are double-edged. It isn't that he consumes energy. It isn't that he overtakes his meister. What could it be? It's his PERSONALITY!!! Excalibur is perhaps the most annoying, arrogant, pompous narcissist to ever exist, and when people call you that while BLACK☆STAR is in the room, then that's a serious issue. On top of that, whoever wants to wield him has to obey one thousand rules, including entries like sitting through his daily 5-hour storytelling time. It's even acknowledged in-universe when the same three meisters all try to wield Excalibur after reading about how legendary his power is in the library, but they all decide it's not worth it.
Only two people were ever even capable of withstanding Excalibur's personality. One being King Arthur (cause even Merlin couldn't stand the Holy Sword), and the second being Hero, who did wield Excalibur for a while, before backing out due to his excessive sneezing fit (which is ridiculous, since sneezing is a thing anyone could expect from anybody). Excalibur himself claims that he partnered with Sherlock Holmes as well, but it's unknown if this is true or not. A lot of Excalibur's stories are clearly nonsense after all.
Crona and Ragnarok's partnership. The good: first they have scream resonance. It's a unique resonance that greatly uplifts their already amazing powers. Second, Ragnarok's black blood hardens within Crona's body to make even the strongest of attacks barely faze him/her. In the rare occasions that Crona does bleed, the black blood will become another offense for Ragnarok to control while Crona attacks. Plus, the more souls Ragnarok eats, like with most weapons, the more powerful he becomes, only the fact that he eats the souls of those who aren't on death's list ends up making him even MORE powerful, and causes him to change form; going from a small creature, to a huge muscle god, to an even more muscular dragon. The bad: The more of these illegal souls Ragnarok consumes, the more insane he and Crona become. And they weren't exactly pillars of sanity to begin with. Also, when their souls are disrupted, it may cause them to go through soul rejection, as two beings aren't truly meant to share a single body. It is unknown if this can kill them, however. There's also the fact that consuming so many illegal souls may also result in Ragnarok losing his mind and consuming Crona's soul. Later on in the series, Crona and Ragnarok return to Medusa and descend even further into madness, causing them to gain an evolved versian of Black Blood called Mad Blood, which is so powerful it can flood entire cities with black blood, and if anyone tries to break hardened mad blood, they will go insane, the problem is, as stated above, to have such a power, Crona has to be so dangerously close to madness, that even standing near him/her is a total danger.
Lyrical Nanoha has the titular character's Starlight Breaker. It required a really long charge time so its caster must be protected or the target needs to be immobile, and its strength is only equivalent to the total amount of scattered mana in the area, making this technique only suitable as a last resort. It also practically guarantees victory if it is cast successfully. Hayate Yagami also views her abilities in this regard, as her techniques are mostly large scale heavy bombardment spells that require a full unit of Bridge Bunnies to help her aim if Rein is not around. Hayate's literally incapable of using weaker, and generally more practical, spells.
Jet Alone from Neon Genesis Evangelion. A nuclear-powered giant robot that doesn't need a pilot...whose main reactor is governed by an easily-hacked central computer. In short, an A-bomb on stilts, and NERV takes immense pleasure in showing the world exactly how unreliable and dangerous it is. By causing it to go on a rampage. Also the designers hadn't figured out how to actually replicate an AT Field, meaning that it would be absolutely useless compared to the Evas.
The Evas themselves would also easily qualify under most circumstances. They're Humongous Mecha which possess incredibly powerful defensive and offensive abilities, however...they're reliant on the pilot's emotional state to function, Piloted by a Forsaken Child, picky about who pilots them, the damage dealt to them is mirrored on their pilots when they're at their best, and they need to be plugged in to a power source to operate for more than a few minutes. What saves them from this in the series itself is both the fact that they operate almost entirely within a fixed area (so they're usually plugged in) and for the bulk of the series are pretty much the only thing capable of killing an Angel, making their utility outweigh their impracticality.
Rebuild of Evangelion has... quite a few things that qualify. First off, there's the entire Operation Yashima, which just like in NGE requires that NERV uses a Positron Rifle that uses every single Watt of power in Japan! And they authorize that without knowing for sure if it'll actually work or not. Secondly, there's Evangelion Provisional Unit-05, one of the new EVA-Units. Since it's more mechanical than most Evangelions, and it serves as a prison-guard for an Angel, it is practically useless, since only few people can pilot an EVA. The only upsides is that the pilot is there and the EVA has the power to keep the Angel from causing too much trouble. The downside, however, is that it only gets power while it's running on the rail-tracks running throughout the prison, and the EVA isn't designed to fight Angels, only keep them away from Tokyo-3. As a result, the only way it can defeat the Angel is by self-destructing, and the self-destruct mechanism itself gives the pilot enough time to escape unharmed.
Unit-02' is a partially mechanical version of the original Unit-02, only it can replace its mechanical arm with various awesome weapons. However, the arm can also easily be destroyed, making it slightly less awesome.
Mark.06 arrives carrying a spear designed specifically to stop an Awakened EVA. That's its only weapon. It can't fight Angels (14 years later, it is the 12th Angel), but it can stop EVA-01 from destroying the world before Gendo is ready for it to happen.
Unit-08 is the only EVA in 3.33 that actually subverts this, since it's a Combat Deployment Type just like Unit-02, which means it is designed for combat. All its weapons are Anti-AT-Field Weapons, making it specialized in killing things with an AT-Field (humans, Angels, other EV As).
Mark.09 is the only Eva so far that can function without its head and it can spontaneously grow a giant booster-rocket out of its back, which appears to be awesome enough, if it wasn't for the fact that it sucks in combat. Its armor is soft and flexible, allowing the Eva to grow the giant booster-rocket, and as a result, destroying the EVA takes very little effort. Its only enemies are also other EV As, making it way more useless...
Unit-13 is considered powerful enough to kill God. However, it requires two pilots to launch (although one of its pilots dies before the end of 3.33) and even then, the pilots need to synchronize not only with the EVA, but with eachother as well, and with only one pilot left, it's pretty useless, unless another pilot appears. It does, however, have four arms, allowing it to use two Lances of Longinus at the same time, which causes it to ascend to God-hood. It also doesn't have an AT-Field, making Anti-AT-Field Weapons used by the AAA Wunder and Unit-08 absolutely useless.
Full Metal Panic!: The Tuatha De Danaan (TDD-1) submarine has a revolutionary stealth engine that allows it to go undetected by conventional sonars. The drawback is that the TDD-1 has to stay regularly on dry dock for several days for maintenance.
Black Lagoon: Revy's twin shoulder rig for her pistols wouldn't exactly work in real life. With the exception of a sitting position, it'd be too easy to lose the guns if you move too much. That being said, they do earn major style points.
One Heaven's Arena fighter in Hunter × Hunter had the ability to use nen to create an entire duplicate of himself. The ability seems impressive at first, with the fighter being able to go toe to toe with Hisoka and even rip off his arms. However, it's quickly revealed that the fighter's innate nen style is the complete opposite of his ability, and the duplicate ability is so complicated that he can't learn any other nen abilities as a result, leaving him as a one-trick pony that's easily thwarted once the ability's weakness is found.
Panzer World Galient: Galient's Flight Form makes quick travel to other places convenient, but since it has no weapons to speak of, it's completely vulnerable to aerial attacks. Luckily, this is eventually fixed via the Assault Galient upgrade.
Many of the cool but incredibly hard sword techniques in Rurouni Kenshin like Hiten Mitsurugi.