I'm the proud owner of a brand new 20" iMac which has, as you can surmise, Intel inside. It runs like a dream and is a screamin' upgrade over my old, beleaguered G4. However, despite Rosetta being pretty slick, it doesn't solve everything. Some apps, like Second Life, run really poorly in Rosetta. Others, like Flip4Mac's WMV player, require a little hoop jumpming. Others still, like NeoOffice, don't work at all.
There are lots of resources tracking the progress of Intel ports (and re-writes). The ones I favor are MacInTouch's Universal/Intel Binary list, their Rosetta Compatibility List and Versiontracker's Macintel Compatible Software section.
However, I wanted a list of just the apps I care about. So I made one and you can access it here or under the Pages menu over on the right.