Dead Space is as scary as a raccoon jumping out of your trash can. No suspense, no horror, just grotesque half human things jumping out of trash cans in a poorly lit room. The game is fun and all but I would never say it falls under the "horror" genre. More like a sci-fi tragedy, kinda like the movie Aliens. Its about the same concept in execution.
Bioshock is pretty scary. It will make you feel secure and think "hey, this person I'm talking to is a sane individual." Then you start realizing that maybe this person isn't so right in the head after all, especially when you hear them talking about making super people or curing cancer with orphan blood... Then before you know it you're running for your life from them as they throw knives at your face because you didn't like the way they styled their hair. That about sums up the Bioshock atmosphere. A sort of feeling you get when you know there's more to the story than everyone else in it is telling you. Very well executed.
But I think the scariest game I've seen is Amnesia: Dark Descent. The game prides itself on messing with your head. Making you think you're not safe when you really are, or making you think you're safe when there's some out of focus monster lurking out in your peripheral vision. Much like the Herobrine hoax of Minecraft http://tinyurl.com/6d2l7vp. Very scary.
I thought Amnesia was a little too one-note to be truly frightening. It did give me a couple of good chills at first, but thanks to the predictable nature of the enemies I got used to it fairly quickly and it all became a routine. Still well worth the money. I heard the 3 Penumbra games from the same developer were actually better because they were not nearly as scripted as Amnesia. (which, by the way, just got a free update with an extra campaign) It's not on the PS3, though.
For what it's worth, I actually think Silent Hill Homecoming is much better than its reputation (even more so if you haven't played Silent Hill 2).
I do share craelon's sentiments towards Dead Space, by the way. It's a lot of flickering lights, loud noises and buckets of gore. Horror doesn't get much cheaper than this.
"If god didn't want us to eat in church, he would have made gluttony a sin." - Homer Simpson
In addition to some of the others mentioned, I found FEAR to be a pretty high tension game with a good atmosphere. Though I played it on PC and I hear the PS3 version isn't that great (but I think most computers could handle it since my old laptop from 5 years ago could and it wasn't a gaming laptop by any stretch of the imagination)
"I've got half a mind to kill you...and the other half agrees" Church from RvB PSN:Mickleohb GT:MickleohbLite