We've been having fun with NES tracks, and there are easily just as many good tracks on the SNES. I'll start us out with a couple oddball picks of my own:
First level of Contra III has some pretty dissonant music. Action packed, frantic to the point of being schizophrenic, and just oozing with a kind of Terminator style.
The 7th Saga is a game I discovered when I used Kaaza to download a pack of "every SNES game ever." It's an RPG where you walk around in a Mode 7 3D world. Pretty neat, with a very soothing, lovely overworld theme.