I kinda misread the topic, Best single player experience was prob Zelda:OoT partly because I kept bumming around not wanting the game to finish. Mass Effect series did the same thing to me, I wanted to make sure I finished all quests before killing bad boss. FFIX was the only game in my entire experience (bar sonic 1 and altered beast) that makes me want to play it again and again and again and again.
FFIX is significant in that aspect for me because it is a single player game but there is sooo much to do before you can free reign and the fact that I was happy to do this time and time again shows what a great experience I had with it :)
And here I thought I was the only one. After the end of FFIX, was the only real game in where I was legitimately sad it was ending. One of the most "Fantasy" driven FF's ever made.
About Heavy Rain, I did find the game quite good in terms of its story and intense moments, which I agree were pretty cinematic and effective. Although, I spotted the ending too early and was kind of sitting on the idea saying "I'm hoping this isn't how it's going to be," hoping that I was wrong.