The Haunted mansion from Vampire: Bloodlines. Most of Prince of Persia: Sands of Time (the library especially stands out).

Some of the most frustrating design was imo found in God of War, tedious 'platforming' sections don't belong in an action game. Most of the game is okay though.