I have a pair of super tweeters in use, that were originally designed for Tannoy speakers. They are hardly audible at my listening position, and I have to go really close with my ear right on the diaphram to hear some screeching noise emanating. I had them for a month or so, and then removed them one day to see if I would notice them gone. Has'nt happened.
I believe it might eventually depend on one's hearing, the room and the speakers in question.
1. In my case, my upper frequency hearing is not very good. I get tested every year, and mine is below average for my age.
3. My speakers have decent Berrylium tweeters, which I think are all I can hear.
Having said the above, others with better ears might notice a difference. Though even I've read how they are supposed to open up the bass and mid range too, has'nt happened in my case.
In case you want to try out, the Batpure super tweeters from Japan are very cheap and are well regarded. Though you need someone to solder them onto a plate and then solder the wires too. The well regarded, but expensive options are townshend super tweeters and fostex ribbons.