"A flashy feature that has limited usability for victory."
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 wail 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 do 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 the 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 relatively short-range staff cannons with 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 from Power Rangers. 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.
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 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 severaly 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. 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.
The Panthermobile from the live action start of the 70s Pink Panther show. It has absolutely jaw dropping lines, but also no windows, no roof for the driver, and my goodness does it fishtail all over the place!
