BioShock
Okay, I'm still playing it, so this could change, but IMO, the game peaks at the point where you confront Ryan, and after that, things don't work quite as well. Or at least, the shift becomes noticable after you fully remove yourself from Fontaine's mind control. I say this because while Fontaine isn't a bad villain, he isn't as compelling as Ryan, yet you're stuck with him as your part 2 antagonist. Secondly, Jack is a golem in every sense of the word. His lack of characterization up to killing Ryan makes sense in hindsight, and works thematically - he's a silent, unthinking 'slave' (that still cares about Little Sisters I guess, unless you harvest them). After that, he still has no characterization. So I can buy that Jack is pissed at Fontaine, but I feel that the latter half suffers from portraying Jack the same way as he is in act 2, just following Tennenbaum's orders instead of Atlas's.
Also, finding parts for a Big Daddy. Sigh...
Final Fantasy X
Sort of - I refer to the point where you defeat Yunalesca, and then have the entire map as your oyster while you prepare to take on Sin. A point where there's kind of lull, even though you've got to defeat this thing as soon as possible. The buildup to Zanarkand is excellent, and the Yunalesca fight is easily one of the hardest in the game. Sin/Jecht/Yevon are memorable fights as well, but there's kind of a lag in this period, at least narratively. And true, a lot of games do have the lag problem ("you have to save the world...but feel free to do as many sidequests as you want beforehand"), but I have seen it done better (e.g. Golden Sun: The Lost Age, how the music changes in the final act to convey the severity of the situation, even if you're still free to wander the map).
Fire Emblem: The Sacred Stones
More or less when you defeat the king of Grado, and it's recovered that Lyon is really the big bad and so on. I say this because in terms of plot, it's fine. In terms of gameplay, you get Myrh at the end. Myrh, who can practically one-shot anything. Myrh, who makes the actual final boss of the game a pushover. Like I said, fine in terms of narrative, but in terms of gameplay, the difficulty level plummets as soon as Myrh becomes playable.
Marathon
Sort of. I refer to the very last level of the first game, when the s'pht turn on the pfhor. Should be great, right? Your former enemies now your allies, fighting beside them in a final battle? Well, no, not really - they keep blocking my shots and the corridors are too narrow. This 'last hurrah' is more like a last whimper.
Star Fox Assault
One of the most enjoyable levels is the space-gate one - full space combat, teaming up with the Cornerian Fleet, taking out aparoids all over the place, etc. After that, we get the aparoid homeworld, which is easily one of the most frustrating levels in the game. And, of course, the fleet is useless. The absolute final level is decent, but, well, yeah.
Xenoblade Chronicles
Not a very egregious example, but I feel the game kind of peaks at Egil. After that, well, things get weird. Like, weird for even the game's standards. Entering the Bionis, Zanza's dreamscape, the tower that may or may not reference the Tower of Babylon, and has things in it that aren't explained, but, well, yeah. As I've said elsewhere, Xenoblade is absolutely bonkers (a good kind of bonkers though), but it really becomes batshit crazy at the end, especially with the final revelations at the end. Egil, at the end of the day, is more of a compelling antagonist than Zanza, even though it makes sense that Zanza is the big bad.