The Cell was a very flawed implementation.
Load hit store issues where you could wait around almost a hundred cycles (and stall it because no OoO). Getting rid of SMT and adding complete OoO support would have been a much better result even if the die area was a bit bigger as a result.
The SPE had NO branch prediction and were pretty close to VLIW in design. It relied on the provably false trope of a "sufficiently advanced compiler" that can somehow solve the halting problem and of course leading to a 50-50 chance that you plowed ahead on the wrong branch and now must wait almost 20 cycles for everything to reset. As it only had 128 registers to unroll loops into, this problem is basically guaranteed to happen a lot.
They didn't provide any cache per core. Instead, programmers have to micromanage the 256k of RAM as if it were cache. Accurately predicting every possible memory pattern is impossible (it would require proving all execution paths which the halting problem says is impossible). Even modern GPUs include cache and choose the appropriate fetching patterns on the fly.
I'll also note that the SPE used a different ISA from the CPU (PPE) which adds yet another layer of headaches.
We'll never know how powerful the cell could have been because it had so many footguns that devs were seemingly incapable of avoiding all of them. These problems were called out by places like Anandtech or Real World Tech a year and a half before the PS3 launched and was then reiterated by the people using it.
Sony allegedly took to threatening teams that wanted to release their finished xbox 360 games before the PS3 version and even took to flying around a couple teams of cell developers to try to get games out the door quicker.
A system using the same ISA for both CPU and GPU isn't such a common idea in practice. There are supposedly a couple companies trying to use RISC-V to do this. I guess we'll have to wait and see what they can come up with. I certainly don't see them repeating these major mistakes.