It's bad because it simply changes the winning strategy from one optimal unit to an optimal combination of units. If you remember, the Civ4 system of "units counter units" has led to the so-called "Stacks of Doom" phenomenon, where because the combat system always chose the best defender, the only optimal strategy was to combine all different defensive units in a single stack so that it literally counters everything and then add up enough offensive units there to instantly capture enemy cities. The Civ5 has then, to solve the problem, has introduced positioning-and-manoeuvring as a large part of combat system, which made the combat much more strategic.
I remember the stacks of doom showing up, but I think the primary reason was the removal of a rule that was in Civs prior to 4: When a single unit in a stack was destroyed, every unit in that stack was destroyed, with the exception of stacks at forts and cities. Certainly you would want to stack your units in Civ 4 (unless someone was hitting them with artillery), but you'd have done the same thing if that rule didn't exist in civ 2, since your best attackers and your best defenders would still have been different units (unless you were using mainly cavalry/howitzers, since no defensive units could keep up with them unless they were on railroads).
It's pretty much the same with every other inexplicable/naked RPS system of balance - it doesn't actually create any strategic depth for the game. It adds an illusion of depth, but there isn't any actual depth to it after figuring out the best combination.
In general, any system that makes things easier from balancing perspective should be viewed with suspicion. There is no such thing as free cheese, and here "cheese" is "strategic depth" that you can very much lose due to resorting to "easy" balance methods. And without strategic depth, people ain't gonna keep playing the game for long.
There's a reason why most people stop seriously playing the actual rock-paper-scissors when they get older than, like, 10, despite it being literally perfectly balanced.
I can agree with that.
MoO II was pretty fun even though the AI couldn't really think to counter players' strategies, but asking it to think is a ridiculously high bar. For example, I designed ships that tractor enemy ships, they just have to fly up to a ship, tractor them, and board and capture them. The AI tries to counter it by self-destructing its ships when you close to point-blank range, or by retreating if they're outnumbered IIRC, both of which are self-defeating. A more sensible plan might be to design ships with 360 degree weapon arcs, and have them fly away from your tractor ships while continuing to shoot them. Since they're not carrying a bunch of tractors and troop pods, they'll have more room for weapons in theory, even if making them 360 degree cuts into the space.
I don't really care for when something feels artificial, either, but in some cases being based in some part on an RPS-like system appears to make some sense. In Halo Wars, a Halo RTS for the 360, for example, on the human side, for vehicles you have:
The Warthog (jeep) for scouting, mainly,
Scorpion tanks, which have machine guns and canister shells that make them better than the other vehicles against infantry but also good against vehicles,
Wolverines, which are anti-aircraft vehicles which fire guided missiles (and can be upgraded to be able to use them on units), but are weak to infantry and anti-armor units,
Cobras, which are anti-armor vehicles which fire railguns. They can be locked down to boost their range, and can be upgraded with deflection plating which deploys when they're locked down). They're also good at blowing up buildings, iirc.
And units specific to specific commanders, like an EMP unit for one and a better tank for another.
If I built a combined-arms group of vehicles, I usually built a bunch of cobras, the same number of wolverines, and a few tanks. Tanks were the least useful.
But! The infantry and air units by and large aren't RPS-based. The infantry consists of Spartans (limited in how many can be active at a time, tech upgrades give them better weapons, they can hijack vehicles or drive yours, making them better), flamethrowers (anti-infantry), and marines (upgradable to ODST), which can throw grenades and later fire rockets. In my limited experience, ODST are capable of stomping everything except the Covenant's Scarabs, provided you drop a ton of them on whatever group you want to kill.
The covenant units didn't seem to have many that used RPS mechanics, either (and they have one that wtfpwns everything, if your opponent doesn't kill you before you can build it - but you can fit two in your upgraded pop cap, if you get rid of almost all your other units).