I thought LA Noire would have been fine, except that most major gunfights were boring slogs through gray sewers and that every single case made you do the same things. Find clues, solve easy ass puzzle, interview witness, go here, catch guy running / driving away, interview guy, receive grade... it wasn't nearly as complex as the developers made it out to be. And the sidequests for 100% were mindnumbing and awful. I've got to get in every single car? And the film reels... the boring pointless film reel hunt. GTA San Andreas was triple the content / fetch quests and it never got boring because the hiding spots and methods were fun and clever. In LA Noire they just hid the film reels on front porches and in back alleys. I considered going for the platinum trophy on the PS3 thinking there was so much more beneath the surface, but there really wasn't.
My personal long ass single player experience was Okami on the Wii. Everyone raves about that game, but it just went on and on seemingly with no point. I killed the big bad villain and then suddenly there's another big bad villain. Battles just got so repetitive. Parts of it were cute and all, but it just was so in love with itself it was grating. Put me off a lot of Japanese culture based video games, and this is from a guy who loved Shenmue.