First, let's acknowledge that a major portion of EVE combat consists of managing a ship's resources. A player must remain aware of health bars, capacitor levels, module damage due to overheating, and ammo usage. Oh, and don't forget about your drones. Not only does a pilot need to monitor their health, but make sure their damage output is directed at the correct targets as well. Amazingly, EVE players manage to internalize the mechanics well enough that they can watch movies while performing PvE combat missions or kill local NPCs (aka ratting) to fund their PvP habits.
So what makes EVE combat so remarkable? Even more than the huge fleet actions that occasionally involve thousands of players, the attribute that sets EVE's apart is the possibility of loss. Many players, myself included, will talk about the rush they experience during combat. I have never experienced the rush of adrenaline while playing any other game that approaches what I experience in a PvP encounter in EVE. After one fight in which I destroyed a Thrasher that tried to gank my mining barge in a low sec asteroid belt, I spent 10 minutes walking around coming down from the rush.
If EVE combat is such an incredible experience, then why all the complaints? First, one can spend a lot of time just waiting for a fleet to form up. For those who play other MMORPGs than EVE, think about all the time spent gathering everyone together for a 24 or 40 man raid. Game developers have scaled down group PvE content because those numbers were too great. In EVE, that's considered a mid-sized gang, with fleets of 100 players not uncommon. Think of the organizational efforts required by players to form the really big fights. Game developers of most games don't even try to support such content.
Next, one has to search for PvP encounters. Other MMORPGs have battlegrounds and arenas in which, once a player enters, fighting begins. Not in EVE. Players in EVE's busy factional warfare zones like to boast they can get a fight within 10 minutes of logging into the game. But in other games, players begin complaining if they have to wait that long. And 20 minute queues? Delicate souls should not read certain chat channels if that happens.
Combat in PvE in some ways is much worse. Sure, the agent system ensures a steady stream of missions, EVE's version of quests. But for the most part, players have gamed and documented the content so well that ship loss is rare. Yes, CCP has added content with NPCs with better artificial intelligence over the years, like Sleepers, Incursion rats, and the more recent denizens of burner missions, but players have put those obstacles on "farm" status as well. The most exciting part of combat is when the outcome of a fight is in doubt, but that rarely happens in PvE.
Looking at the overall design of EVE, I would speculate that the lack of excitement is PvE working as intended. A game doesn't want to give incredible highs and lows every day. After awhile players either become emotionally worn out and need a break, or emotionally inured to the gameplay and start demanding bigger and grander spectacles. By putting in as much emotional downtime as CCP has, players are more apt to stick around in search of another fix.
One interesting statistic seems to support my belief that the emotional impact of EVE's harsh death penalty brings EVE's combat system to life. According to CCP, players who lose a ship during their first 30 days of playing EVE are more likely to resubscribe for a second month than those who don't. My personal belief is that a player who loses a ship has encountered a challenging situation and lost. People like challenges as long as they do not appear insurmountable.
Of course, not all ship loss produces the same response. If a player is involved in a fight that lasts any length of time, even a ship loss can feel good. One time I became careless in an ore site and an Amarr factional warfare gang caught me. I turned the tables for a bit when I declared the Hound I was dual-boxing with, at least until reinforcements arrived and my poor barge exploded. That was a good fight that both sides enjoyed.
What doesn't produce satisfying deaths is suicide ganking in high sec. Suicide ganking, especially of miners, is in many ways like sex between two sixteen year-old virgins. Due to CONCORD response times, a successful ganked is usually over in 15 seconds. The ganker, playing the role of the inexperienced male, is proud of himself because he scored. Then again, the actual gank was preceded by a lot of foreplay in the form of stalking the victim, preparing the warp-in, etc.
The gankee, on the other hand, may not even have known the gank was about to occur until the ganker appeared. By the time the fight or flight instinct takes over and the adrenaline starts pumping, the miner not only has lost his ship, but may find himself waking up in a a station in his medical clone. Much like the young lady expecting a magical experience, the miner is left emotionally frustrated. But unlike the disappointed young lady, the miner by now is filled with adrenaline and often expresses his frustration at the ganker, ironically giving the ganker further pleasure.
In conclusion, combat in EVE doesn't work due to its mechanics. The combat system works because loss is such a major component of the game world that the prospect of loss generates a physical response in players. The closer a player gets to loss, the better the combat. Fighting NPCs doesn't engender such emotions because players have pretty much figured out the system, so loss during PvE, especially in high sec, is rare. However, loss by itself doesn't ensure a good time; one must gradually build up to the climatic moment with a lot of preparation and then a lengthy battle. Without all of that, the combat system, quite frankly, is a bit meh. All in all, CCP created a system that is an acquired taste. A niche system for a niche game.