COD4. Last marine mission. I was so shocked I couldn't speak for days and my poop turned green. Granted, it doesn't twist the
plot that much, but it was something I had never ever experienced in a game before, and thought I never would. At that moment I fell to my knees in silent praise for the writing team, and vowed eternal death for the antagonists.
Sadly, I accidentally spoiled Bioshock's key plot point for myself from wikipedia about a year before actually playing it, and therefore had a pretty good grasp on what was going on throughout the entire game. I was still somewhat surprised at some of the revelations.
For instance, I NEVER would have realised the true identity of Atlas! Only knowing the fact that he was controlling me, at some point in the game (when his "family" gets blown up) I actually started to believe that he was in fact the good guy. Heck, I even wondered if maybe
I was Frank Fontaine

Also the whole Ryan being your dad thing. Quite unexpected.
I never really got around to finishing KotOR (After stalling somewhere around the underwater part at Manaan(?) about two years ago I just never really picked it up, despite still regarding it as a totally kick-ass game with some of the best writing until Mass Effect. I still have my saves though, so maybe some day I'll keep going where I left off.) but from what I've heard and deduced I'm afraid the "incredible" plot twist is ruined for me =(
From what I understand, either the player character themself or Bastila is Darth Malak. I always suspected it could be Bastila's true identity, but if it is the player then I can understand the amount of surprise that's related to it.
Prince of Persia: Warrior Within - the Sand Wraith. It all made so much sense!
Team Fortress 2 - When I learned the identity of the commentator lady. I'll never be able to play that game the same way again!
http://www.teamfortress.com/war/administrator/