As other posters have mentioned, weird processors (notably freaks like the Cell) can require a lot of knowledge of the instruction set and chip quirks (see http://www.insomniacgames.com/category/research-development/ for a lot of good info on this), even requiring you to throw away your nice virtual object hierarchies and things to make the SPUs happy ( http://www.insomniacgames.com/wp-content/uploads/2011/06/GDC... ).
Also, just because a compiler targets an architecture doesn't mean that it can optimize code for it worth a damn.
EDIT: oh god it may even lie about its program counter ( http://www.insomniacgames.com/wp-content/uploads/2009/08/gdc... )
Actually, toolchain for the PS3 with its Cell processors and unconventional memory / processing model was a major, major pain.
Elsewhere, EPIC Itanium failed for the lack of sufficiently smart compilers.
Remember when Halo came out on the Mac, and it was running slow as molasses on higher-performance hardware than what the game was originally written for? That's in part because many of the optimizations that make code run faster on x86 did the opposite on PowerPC. And any code that relied on SIMD instructions would likely have to be rewritten from scratch.
I'm not particularly familiar with the Cell architecture, but based on what I do know I suspect that going between it and x86 would be an even bigger headache.
I think it is extremely naive to assume that the CPU's underlying architecture and microarchitecture are utterly mundane details abstracted by the C++ compiler, ESPECIALLY in the case of consoles. Consoles are underpowered by today's standards, and making today's game run on them requires a deft maximization of both the GPU and CPU, not a mindset like "well C++ will take care of the CPU stuff". In case you hadn't figured it out, common C/C++ stuff from Havoc Physics to little old memcpy() and strcmp() are all hand-optimized for their platforms -- sometimes multiple implementations that make use of various ISA extensions. Try stepping through them in a debugger sometime.
In terms of endianness, it's not a huge problem - the toolchain normally copes with this, as assets are built individually for each target platform. This is what we did last time I worked on a cross-platform game anyway.