I just finished Amnesia, so I've been thinking about this quite a bit lately. Handy!
I'd say I was sufficiently unnerved the first few times I had to hide in a closet or a dark corner, but it started to wear pretty thin by the end. Eventually I started to anticipate the helplessness and prepare accordingly, and at that point it's really just another predictable play mechanic - only far more passive and, arguably, less engaging than straight-up combat.
My overall take? I think variety is important, and I think decisions about player vulnerability, or lackthereof, should be made organically with respect to the environment. A game where every threat is major, or where players cannot deal with even minor ones, quickly becomes one-note. On the flip side, games that build the player up into unstoppable, unwavering badasses only to arbitrarily shaft you with an unkillable enemy for obvious meta-game purposes? Those games feel pretty contrived.
Applying all of this to Amnesia, I'd say the game was about halfway there. The early scenes of vulnerability were effective, but I think they would have been even more effective if you were given some lesser adversaries who were vulnerable to more traditional combat tactics. Then, when you did run up against something truly terrifying, you'd have a greater sense of impotence. I think the sensation of helplessness is heightened when you discover the uselessness of previously effective strategies.
As a for instance: let's say you had some sort of ancient revolver and a puny sword. Early on, you might run into a few monsters that, while disturbing and sanity-draining, could be dispatched by a little swordplay. Then, as you progress, those monsters start to get a bit bigger, and suddenly your sword isn't so helpful. You can use the gun, but there's virtually no ammo for the thing. Then you have to consider the noise; sure, you blasted one monster, but the thunderous gunshot will probably attract others. So now you have to hide...
Of course there will still be monsters you can't fight, and that's when you have to run and hide. But doesn't it seem a lot scarier to see something, shoot it in the fucking face just like you did the last half dozen, and then run for your life when it doesn't even flinch?