I can't agree that the super gravity gun is game-breaking - even if you don't like it (which I do), it's only in the last couple of levels. There's simply too much good stuff in previous levels for it to ruin what's gone before.
Fahrenheit on the other hand - now there's a priceless example of a game blowing it late, though replace "design decision" with "plot development". The QTEs were an annoyance; the vapidity of the story in the final act on the other hand really did let the whole game down, given that most of its power up until then was the way the story unfolded.
Oh, and to echo others:
the Flood in Halo (and especially Halo 3). Loathsome levels, Library be damned.
the save system in Dead Rising - probably the best example here of a single reason for me not wanting to play an otherwise great game.
stupid, stupid stick-bashing combat in Ico.