"A flashy feature that has limited usability for victory."
In Auction Kings, Paul has sold many devices that fit these criteria:
Gasoline-powered blender? Gasoline-powered pogo-stick? Motorized (and gasoline-powered) barstool? Paul has sold all three.
The Excalibur from Crusade had the ability to fire a supercharged shot that could kill any ship it faced. Downside? It almost drained the ship, leaving it vulnerable for a minute. A minute in which the destroyed enemy ship's buddies could use to whale on it. They only try to use it as a last resort. Besides, the ship has top-of-the-line armor which absorbs and dissipates something like 80% of incoming energy.
The staff weapon. It looks great, it fires loudbright bolts of plasma, the wounds look horrific, it doubles as a melee weapon... but it's also really hard to aim, rarely does damage beyond twenty meters, fires only once a second, and the wounds are self-cauterizing, meaning basic medical care makes them easily survivable. Badasses who've trained for years such as Teal'c and Master Bra'tac can hit a human-sized target at range two times out of three, but Teal'c switches to P90-s in the later seasons.
Made explicit in one episode where O'Neill (with two l's) is training some rebel Jaffa to use P-90s. After an impressive demo comparing the firearm's superiority, he explains "This [the staff weapon] is a weapon of terror. Its purpose is to intimidate the enemy. This [the P-90] is a weapon of war. Its purpose is to kill your enemy."
The ship-mounted weapons are the more powerful version of staff weapons by several orders of magnitude (their firepower is in the megaton range). They are also much more precise than their handheld cousins. Presumably, the Ha'taks have some sort of a targetting system. Good luck trying to hit anything in space based on visual aiming alone. Then again, their weapons consistently fail to hit small craft.
The Death Gliders can fly in space and in atmosphere and their shape is specifically meant to terrify enemies. They're also equipped with more powerful versions of the staff weapons. They also appear to lack any devices present in any modern jet fighter, such as targetting systems and friend-or-foe recognition. Now imagine humans taking the useful parts of the Death Glider, such as their drives and inertial dampers, replacing the slow-firing and relatively short-range staff cannons with faster-firing railguns and long-range guided missiles, and adding the above-mentioned systems. Now you got an awesome fighter. Which shows when a single X-302 manages to own a pair of Death Gliders in the space of a second.
Done often on MythBusters. Driving a car so fast that it can't be caught by a police radar, making a child float by using thousands of balloons, chopping down a tree with a machine gun... the list goes on.
Serpentera: ostensibly powerful enough to destroy a planet, but it kept running out of power when it came time to actually fight the Power Rangers. Despite being the largest Zord ever in the series, it was hardly effective beyond its first few appearances. The behind-the scenes reason for this was the limited footage of Serpentera available from the original Sentai, where it was an envoy of the Powers That Be that told both sides to stop upsetting the cosmic balance with their fighting, not a weapon of the villains'.
The Megazord itself became this from time to time, as it often seemed more practical to split the zords up, either to take on multiple monsters or to flank and overpower single one. Notably, while fighting Commander Crayfish and the mutant Ranger clones, the clones held Crayfish on their shoulders, leading Billy to say he was out of their weapons range. Never did it occur to anyone to disassemble and let the Pterodactyl Zord (which can fly) attack from above. This became even more apparent any time they had bipedal humanoid zords, such as the Super Zeo Zords, Rescue Zords, or the Shogun Zords, since, being realized via stuntmen in costumes, they were utilized more often than animal or vehicle-style individual zords.
The original opening sequence to the Battlestar Galactica miniseries. It was originally meant to show the Armistice Officer arrive at the station several times at different stages in his career, from a lowly lieutenant to his final rank as colonel. They even cast a young actor to play him, putting him through lots of makeup to show him accurately age over the 40 years. Realistically, this would have stretched the opening to maybe 10 minutes.
The bat'leth, while definitely a Cool Sword, isn't very practical in a realistic swordfight. For one, the normal stance requires the use of both hands and severely limits the reach. If you grip it in one hand, then you have an unbalanced weapon with no hilt and a pointy end facing your gut. Made more jarring by the fact that Klingons do have normal swords, but only one is ever used. Duras uses one in his fight against Worf but loses, as Worf has the advantage of vengeance on his side (Duras having just killed Worf's girlfriend). Also, most fights with the bat'leth show it as a slow, cumbersome weapon that could likely be overcome by a nimble swordsman with a rapier. Additionally, let's not forget that Klingons still use swords in the days of directed-energy weapons. It's even explicitly mentioned that an old lady with a phaser could hold off a dozen Klingon warriors with bat'leths.
Major Kira described a Starfleet phaser rifle like this (at least in terms of guerrilla warfare). Sure, it's got multiple power settings, gryo-stabilizers, and multiple-target acquisition capabilities - but putting all of that into the weapon means it's much more prone to breakdown in less-than-ideal conditions. She says that the Cardassian disruptor rifle, while simpler and more brutal (with just "stun" and "kill" settings), could be dragged through the mud and still fire.
On Star Trek: The Next Generation, the Enterprise-D's saucer separation abilities proved to be this. It was the reason the Enterprise-D was designed the way it was, and it was anticipated to be used regularly, but it was too expensive to do, and on the show, it was used only three times: the pilot episode "Encounter at Farpoint", "The Arsenal of Freedom" (also from season 1), and "The Best of Both Worlds part II" (from season 4), before being used for the last time in Star Trek: Generations.
Snooki & J-Woww: Snooki loves her chunky stiletto pumps, perhaps a bit too much. When she's about to leave her pad (mind you, she's knocked up here) along with J-Woww, she takes a tumble down the steps like she's pounded one too many.
Lampshaded in an episode of Castle where the titular mystery writer and his cop friends are shooting the breeze about what the (working class) cops would do if they won the lottery. One of them claims he'd buy a Ferrari, at which point Castle, a millionaire, says he has one and it's actually not as impressive as you'd think. When the cop points out that they're "hella fast", Castle retorts that a Ferrari is just as fast as any other car on the street when it's stuck in rush-hour traffic.