Metroid Prime has a 16:9 mode? That's news to me, and I have a 16:9 TV. Can anyone verify?
As a general rule, all 1st and 2nd party games from Nintendo support 480p. Most new 3rd party titles, like Viewtiful Joe or Soul Caliber 2, also support 480p. Ports and remakes from older consoles usually don't.
As for whether or not it's worth it: If you own an HDTV, which apparently you do, then yes, you owe it to yourself to get the component cables and run your games in 480p. If you run your games in interlaced mode on an HDTV, the TV will line-double the picture, which can produce some artifacts.
480p looks great, especially for newer games. Viewtiful Joe is breathtaking; even the FMV at the end appears to be rendered at 480p, and it looks amazing. There are a some games that manage to look screwy in 480p, because the developers apparently didn't take the time to do it right. The little FMV scenes in the Madden 2003 intro, for example, appear to use basic line-doubling when running in 480p. It looks like poop.
-Ryan