Hmmm, I don't remember button sequences in Shadow of the Collosus. You just had to climb up and hit the weak spot, that "weak spots" in general seem like something we'd move past with better graphics and especially in games that attempt realism, even if in their own imaginary world's rules. (was gonna say GoW too, then I realised you didn't mean Gears of War)
I see what you mean with only being able to kill a boss by solving a puzzle. Though I do think it can be done in a way that's much more tolerable. If you can't kill it with your normal arsenal, at least show that it hurts when you try, and make sure the player knows that they're doing a puzzle. Liek have an NPC yell at them "that's not gonna work, you have to hit it with something bigger", or something like that.
Gears of War 2 - midway-ish
[spoiler]The fish boss. First it grabs your raft with a tentacle/flipper/whatever and you have to chainsaw it to get him to stop. That pisses him off and he rushes at the boat with his mouth open and you run in. Then he shooted shit at you from spots in the back of his mouth. You shoot those down so they don't kill you, and I think shoot down his throat at the right time to actually damage him. It's all unbelievable over-scripted. It's fun, but would be more fun (and more believable) if you could just shoot him to death from the outside, but make him take more damage when shot in the mouth or something. That way he'd have the same weakness, but it would feel less scripted. Gears is one of those games that tries for a certain level of realism, and finding the weak spot on a giant fish breaks the immersion.[/spoiler]
Now I hate myself for starting this. Everyone's talking about Bioshock and I just started playing it, so I can't read all these spoiler tags, haha.