I feel like I'm forgetting about a lot of great levels, but I adore the first mission against camouflaged enemies in Metal Gear Solid Peace Walker.
There's something really cool about taking a stealth game and saying, okay you've got to stay hidden, but now your enemies are too.
Stalking through a jungle, crawling through shrubbery, ducking behind trees, scanning absolutely anywhere for a sign of the enemy, and then you might catch a glimpse of an oddly shaped bush, so you circle round it aiming to sneak up behind him, but a gun to his back and then... attach him to a giant balloon and yank him up to a helicopter. Because Peace Walker is all about steathily creeping through enemy encampments whilst silently kidnapping your enemies with balloons, helicopters and loud yoink's, because you know, Kojima.
... but anyway circling round the bush, getting ready to draw your gun. And then suddenly a sniper you missed lying in the bushes shoots you in the back. The best part about the mission is the enemy aren't hidden by any BS invisibility system or anything. They're wearing camouflage clothes and positioning themselves well in the landscape and it really works. You can stare at a patch of grass and suddenly realise you're looking straight into someones face. It's really impressive, you barely ever see examples of effective camouflage in films or TV, but here it works. It breaks up their lines and blends them into the shapes behind them.
--------------------------------------------------------------------
For a change of pace, my other favourite level is the Brothel for Slaking Intellectual Lusts in Planescape. First of all, it's a brothel where people go to satiate their lust for intelligent conversation, or interesting stories or a challenging game of chess etc. That's already cool by itself. But most of all it's an hour of running from person to person, uncovering more about who they are, revealing information and then running to another person to use that information to reveal another bit of information... Learning about the building, about the personalities within, and about the world in general. XP dinging up based purely on conversations and the good question of 'who is the tenth student?'