What I am wondering now is, what kind of adventures, campaigns, and play style is D&D 3rd edition actually best at? What part of the rules seem to have been widely misunderstood or misapplied? And what small tweaks might make a major positive difference?
Looking at the rules for characters, you generally are good at one niche and are rewarded for specializing. You can be OK at a couple things by spreading some resources around, which can be decent, but big power is really at specializing.
Take skills.
First the mechanics provide opposed checks and some set DC things with tougher more useful things having higher DCs, but a lot of failure if you are not good at them generally through skill ranks and stat.
Also there is class skill versus non-class skill skill rank caps and skill point cost, a 10th level character has a 5 lower bonus in a non-class skill than a class skill for the same number of skill points spent on them.
Compounding this are classes with fewer skill points per level and possible skill point penalties if you dump int (fighter, rogue, cleric).
It is real easy for a character to have very few skill areas they are character level decent competitive at, and a lot of areas they are terrible at.
Maxxing out hide and move silent or spot and listen is fairly key to success with these skills as these are opposed checks and the opposition gets more resources as their CR goes up.
Spending a few skill points to up something a bit can be OK, nobody is a specialist in everything so if you hit an opposed check against somebody in an area not their specialization you can do decently. But usually you have to sneak past observant guards/guard beasts or you are trying to spot sneaky ambushers.
A few things are ok for low level skills, the linguistics one gives you a full spoken and written language for each point spent.
But mostly it really pays to do your niche thing and focus on that.
If you are a fighter with one or two skill points per level it pays to get intimidate and be decent at it and generally leave most everything else to others.