"A flashy feature that has limited usability for victory."
In Fractured, a Mass Effect/Star Wars crossover, the Trans-Galactic Republic's large Star Dreadnaughts are viewed this way due to using huge amounts of energy to operate, leaving them short-ranged and requiring massive supply lines.
In the Uplifted series, The Tiger tank prototype, as Hoch and Hanala find out when trying to take one deep behind enemy lines in Libya. It's clunky, over engineered, and unreliable in the conditions they face.
In Aeon Natum Engel and Aeon Entelechy Evangelion the Military views the Evangelions as unnecessary large Engel-prototype relics from the past. In the latter this view is somewhat extended to the conventional mecha, at least by the tank crews.
BlackMegaGargomon from the Tamers Forever Series has an unbelievably powerful technique known as the Black Moon Flare, which causes unfathomable amounts of devastation within a vast radius. Unfortuneatly, the technique happens to be a little too powerful, and D.C is only ever able to manage one use of it. And that ends up being a Worf Barrage
In Calvin At Camp, Calvin, Jason, and Marcus try to attack Kevin with medieval weapons, but find out the hard way that although the weapons look cool, they are impossible to lift.
In Shadow Snark, the titular characters wings are made of solid iron, which though badass doesn't give him the ability to fly. A very crippling handicap for a pegasus.
In Princess of the Blacks, Jennifer (A Gender Flip of Harry) uses a form of invisibility that's undetectable to Moody's magic eye. However, since it works by bending light away from a person, they're likewise blind for its duration. The only reason Jennifer uses it is because she's already blind (though on a potion regimen to regain her sight).
In the My Little Pony: Friendship Is Magic fanfic, The Dusk Guard: Rise resident Wrench Wench Sky Bolt has several ideas for things like fast and powerful airships and protective armor, but acknowledges that they are too expensive and material-heavy for mass production and general use. For a six-pony Elite Army such as the Dusk Guard however, they're absolutely perfect. In fact, it is the ability to pursue these ideas that Steel Song uses to entice Sky Bolt into the unit.
In Bleach/Naruto crossover True Warriors Never Die, Naruto is the jinchuuriki of the Juubi which (in addition to having 2000 years to hone his skills) gives him destructive power comparable to Yamamato. The downside is that he has to fight the Juubi roughly every 1000 years to insure it doesn't try to destroy the world again. As he recently found out, the more he uses its power, the faster the Juubi recovers. In fact, despite using his jinchuuriki mode only a few times, the Juubi is already stirring, 900 years early.
The killing curse and other magical means of murder are this in Broken Souls. They're guaranteed to get the job done, but are often seen by others in the area because they're so flashy. This is why a group of Soviet assassins were able to get away with so many hits; they would apparate near the victim, slash their throat, and leave without any flashy spells.
In Getting Too Old For ThisHarry Potter can modify firearms to be so powerful, they can be fired at a ship in orbit and still "ruin someone's day". However, he's the only person in the galaxy who can make said modifications. And it takes him at least thirty hours for a single gun, and if he makes a single mistake it could result in a nuclear explosion. Thankfully, those are the only drawbacks; once a gun is completed, it's just as safe and reliable as any gun like it.
In George Orwell's Nineteen Eighty-Four, it's implied that the Forever War going own between the three superstates is a sham, and all the weapons they build are deliberately designed to be as flashy and intimidating as possible and waste resources as much as possible. They're not actually built for fighting and winning wars. This really bites them in the ass in America's Stepbrother, America's Enemy V2.0, where the real life United States of 2012 is planted into the world of 1984 by spatial anomaly and Oceania decides to invade it. Up against far more advanced and practical American weaponry, the Oceanian military completely gets its ass kicked.
In Supernova Nami learns to use Soru as early as the fight with Captain Kuro. Unfortunately she's isn't in good enough shape to use it properly so each use leaves her legs in agony.
Johnny and Yosaku's ultimate techniques during the Alabasta arc in Shinobi Of The High Seas leaves their legs and hands useless respectively.
Fire Emblem Fates: Aftermath: Corrin's crown is ornately decorated, but it's also pretty heavy. He elects to have a more practical crown made to be worn on a daily basis, delegating the fancy crown to only be used for special occasions where it would be appropriate.
In When Fate Intervened a crazy wandmaker comes up with a nearly two-foot monstrosity which only works for Harry. Its spells are six or seven times more powerful than usual - at the cost of thirty times the magical effort. And this is just for a Lumos spell.
In Butterflied, Xander is (due to the actions of a Sith) The Ageless and automatically copies the form, skills, and memories of anyone he comes in contact with. The problem is it's automatic so he can't not do it if he makes skin contact, and doing so knocks him out for several hours afterwards. The Sith who made him that way intended to make him her new host body but realized the shapeshifting was rather useless for infiltration.
In This Bites!, Soundbite quickly realizes that with his new transceiver, he can call every Transponder Snail in the world or none of them, no in-between.
Somewhat averted in the latest chapters when he realizes that he can both call people like a regular transponder snail, and also that he can create a 'dead zone' the size of say Enies Lobby where they are unable to receive the SBS broadcast and thus unable to preempt the attack.
In A Third Path to the Future despite her desire to install such devices in every room, Jean Grey quickly realizes she'll have to only install hard light projectors in a few important locations in her universe's equivalent of Hogwarts. While Harry Potter got the land for free, hard light projectors require technology so prohibitively expensive that six of them drained nearly the entirety of his vast fortune. If not for Death rewarding him with the fortune of an immortal he'd killed, Harry would've gone broke very quickly.
In The Black Emperor, Lelouch and Milly designed several prototype Knightmare Frames but had to can each of them for various reasons. One designed for long range artillery support was too heavy to move faster than a crawl; another that changed into a plane was exceedingly fragile and couldn't fly for long due to fuel constraints.
Lelouch views the Knightmare lances this way. While they can inflict a great deal of damage, they also require you charge straight at your opponent; otherwise they're just less efficient tonfas.
The Unforgivables are seen this way by Harry's group in For Love of Magic given that while they can't be magically blocked, they're all single target only so they lose their power upon hitting anything (such as a butterfly in the cloud of them conjured to block spells). Compounding the matter is the fact that two of them (Imperius and Cruciatus) require the caster maintain the spell and the third has an unnecessarily long incantation.
A spell Harry develops called the "Crown of Glory" causes him and his girls (Tonks, Fleur, and Luna) to temporarily gain an aura of power and righteousness that basically magnifies anything someone else finds impressive about them while diminishing anything they dislike. Unfortunately, said spell massively inflate their ego as well, meaning the other three are required to keep each one in check and after even a couple hours of use results in each of them having to go their separate ways afterwards to blow off steam lest they kill and/or rape each othernote Harry and Fleur begin plotting sexually dominating the other three while Tonks would kill them for even trying or for insulting her. Harry and Fleur each find someone to sexually dominate for several hours while Tonks absolutely destroys a punching bag with her bare hands before going flying to blow off steam. Luna, as the least affected, simply goes for a long walk in the woods. Regardless, while it's a powerful tool to control others, the drawbacks are far too great for anything but sporadic short-term usage.
A notorious fanmade Star Wars ship design was that of a Star Destroyer-type ship that was 260 kilometers in length. (For comparison, this would make it a little bit smaller than Switzerland.) A response to it◊ was the commander of the ship reporting on its maiden flight, and apologetically explaining all the incidents that resulted from the ship's size (cases of vertigo from looking out the three-kilometer bridge window, its six-million man crew causing massive outbreaks of dysentery and warring with one another, a case where the ship, while taking two hours to turn around, accidentally bumped into a nearby fleet and annihilated it in the process).
Multiple cards and/or decks in Jaden's Harem: Return of the Supreme King are shown to be nearly useless compared to more basic cards and well-rounded decks. Dimitri manages to summon Black Luster Solder - Envoy Of The Beginning during his duel with Jaden and immediately loses it due to a trap card returning it to his hand. Zana's deck is built entirely around summoning Cyber End Dragon with Power Bond so when Power Bond is removed from play early in a duel, her deck is crippled.
In Forum of Thrones, Bear's knife gloves are terrifying, but practically useless in a real fight, since he can barely parry with them. He mostly uses them to intimidate, or when he gets into a brawl, but the moment he meets a real opponent, he has to waste precious time with drawing his sword.
The Targaryen dragons, of all things, while very much essential for the war, can get into situations in which they are very impractical, namely in a battle. Their main weapon, breathing fire on their opponents, is kind of hard to use without causing massive casualties to their own troops, through literal Friendly Fire.