Swedroid have made an amazing video on a gaming session with the ASUS Transformer Prime, using a PS3 DualShock3 controller. This tablet they have is running Honeycomb. All they did was simply connect the controller via USB to the tablet and they were ready to go. There's no need to hack, tweak or download any extra things to get it working, very straight forward and simple.
With a lot of the emulators out there for Android you will need to re-map the keys, but this is pretty straight forward. It's also great if you want to use your own customized killer setup, instead of the usual mapped keys.
They test games such as Mario Kart, Legend of Zelda, Crash Bandicoot and more. In the description they state that everything ran solidly at 50-60fps.
This is great and a huge step forward for Android gaming however, I don't think it will wipe out console gaming. Consoles are usually a lot more powerful and have their games designed and made around them, making the most of the hardware. They're two different markets which I think should stay different.