Seeing dead people is not cool. It's terrifying. So terrifying that my sixth grade self almost left the theater after the first 10 minutes. I ended up watching the film all the way through, but the nightmares I had after watching it lasted for about two weeks.
The Sixth Sense is frightening because it's psychologically disturbing. Seeing ghosts is one thing, but being a child who is constantly being sought out by ghosts who have proven that they have the ability to hurt him is an extremely scary concept to think about. Of course, not all of the ghosts who come see Cole are malevolent spirits, some are very kind, but look scary on the outside. Some of them just require help from someone who is still alive and able to communicate with them and Cole is one of the few humans capable of doing this.
The scariest moments in The Sixth Sense, in my opinion, include the psychologist's encounter with his first patient, Cole being trapped inside the cupboard with a crazy ghost, the old woman in Cole's kitchen, and the girl in Cole's tent. These scenes always successfully creep me out, no matter how many times I watch them. Great acting in this film, especially by Haley Joel Osment.