I dunno about any vids of what each superweapon does, but I can give you a rundown of each. And if that fails you, you can always play a large single star map against one easy enemy to test it out.
TEC Novalith Cannon: the most straightforward of the three, it's a big bomb shooting cannon. It hits planets for damage, and more than 3 in your empire can focus fire any planet. It's got it's uses, namely to break choke points, but against a good opponent it's pretty much negated, as they will learn to keep colonize frigates near. Still, hitting one then moving on is a decent way of winning a resource war if you fould yoursel losing it (which, granted, as TEC you shouldn't.)
Bottom line: Planet shooting gun, fires even across star systems (but slow), can be nice to help give you an advantage in late game stalemates.
Advent Deliverance Engine: In essence, a bi gun that shoots culture, it fires a "signal", and when that hits a planet, enemy culture disappears, and friendly culture spreads from there for awhile. This thing really plays off being an advent, if you neglect culture before this point, it can be an easy way to get back in the game. If you haven't, it can be an easy way to reduce hostile culture before or after attacking a planet (before gives you a slight culture bonus, which might fade, but's still nice; after speeds up the process as you don't wait as long for culture to spread, and gives you a slight boost.) It's nice to have one, if you can afford it, and certainly helps in multiplayer, but not vital to winning as Advent.
Bottom line: Speeds up a game you've already won, and adds nice bonuses to areas for you, but if you have the creds to spare, probably best to just build a few more ships.
Vasari (Returning Armada?): I don't hardly play Vasari, though they're great fun, so I don't know much about their tech tree, but I'm assuming the ultimate weapon of theirs is RA. You get out what you put into RA, again, it's a very good mirror to what a Vasari empire should be, full of phase gates. If you've a large empire with many phase gates, this is the key to "not losing" I've yet to see a player in an evenly matched game lose once they've a good RA groove going.
Bottom Line: in smaller maps it's not necessary but nice. In larger maps, you've already got the infrastructure there, just buy it, and you're not going down without a fight.
So, I guess superweapons are exactly what they imply, powerful weapons that were primarily designed to break stalemates and decide a winner.