"I find that it usually falls flat thanks to a few loudmouthed jerks and people who exploit their way to "fame.""

For me, this. I'm actually all fine with competition and challenge. I loved deatmatch style FPS games. Ultimately I like a more cerebral FPS, and got addicted to Battlefield 1942 when it came out over stuff like Quake and Counterstrike. Still.

The thing I find turns me off of PvP in MMOs at least and for that matter lately with FPSs is the type of player it attracts. Basically the loudmouthed jerk and the guy who will exploit and doesn't seem to see the problem with it. Generally I've put it down to these people trying to compensate for serious shortcomings in their personal lives, but that still doesn't make the game fun for me.

So I'd rather have a game that specifically doesn't have PvP because I know the player base will likely be more mature and well adjusted.