In fact that is always the first game that comes to my mind when a question like this gets asked.
I loved resident evil 4, but to be fair I disliked all the games that came before it. so if you where a fan of those I see your point.
Resident evils 1-3 were brilliant survival horror masterpieces for their time. They captured the horror of a zombie apocalypse well, each in its own unique way. Resident evil 1 was scary because you were stuck in this mansion, surrounded by undead where everything is a death trap and your commander is evil. Resident evil 2 was scary because the infection hit the city, the police station, and you had this monstrous tyrant running around. Resident evil 3 was scary for similar reasons as 2 plus the addition of Nemesis lurking around random corners trying to hunt you down.
I can understand that the gameplay was offputting, but the games were otherwise excellent.
artificial horror. the scary part was "am i going to be able to manipulate these godawful controls quickly enough to beat that super slow moving, stupid zombie ---
Is this going to be one of those times when you pretend not to have a plan until the last moment? And then turn out to really not have one?