Ocarina of Time's was outstanding. I remember just being blown away by the beautiful graphics and everything taking place.
Metal Gear Solid's was also an outstanding intro. With all the sneaking around and after killing some guards having Snake take some stuff off and the title Metal Gear Solid-Tactical Espionage Action- popping on the screen.
Priceless.
Come to think of it those 2 may be my favorite games ever.
Oh yeah couldn't forget Xenogears' with Fei's Weltall going wild and blowing the crap out of the town. You were left with this feeling of WTF it was amazing.