Gabe Newell famously sounded off about how useless it was and wouldn't transfer to any other hardware, but he ended up being wrong. The architectural model forced on you by the SPEs is the model that the industry (not just games but any compute intensive work) has embraced for the heavy multicore but coherent system world.
Rather than the "thread per vague activity" model, we've embraced a one thread per core with a work stealing scheduler walking a DAG of chunked compute tasks.
For a modern view of this, this is exactly how Rust's rayon library works.
The whole thing reminds me of a lot of the complaints about the N64, a lot of which ended up being the fact that DRAM was no longer a single cycle away like it was for the SNES. Yes cache conscious code is more difficult to write, but that memory hierarchy architecture was more a harbinger of the new world rather than a one off weirdness of a single console.
To be fair the PS3 port of the Orange Box runs like absolute crap in many instances. Although I believe it was developed by EA, not Valve.
Maybe - the Orange Box at least was outsourced to EA and the performance was reportedly not great. I think Portal 2 was done in-house though and I don't remember anyone complaining about it.