I think one of the most frightening things about narcosis is that one often isn't aware that one is impaired at the time, but only in retrospect.
It is not the gross narcosis that you feel that is going to get you into trouble; it's the subtle impairment that causes you to make judgment errors, or get tunnel vision and lose your buddy, or misread (or forget to read) your gauges.
Here's a story that made a big impact on me: My Fundies instructor is an extremely experienced diver, who has done deep exploration and survey dives and owns and uses an RB80 rebreather. (Offered as some evidence that he's quite capable, as one has to pass a fairly rigorous evaluation to be permitted to buy one of these, and then a difficult class to be allowed to dive it.) Anyway, he was diving in a remote area with his girlfriend, where helium was unavailable. The dive the GF wanted to do was on a wreck at about 100 fsw. My instructor didn't particularly want to do this on air, but reluctantly agreed. They went down to the wreck and he had a leak from his left post. He reached up and shut the post down, but the leak continued, so he isolated and thumbed the dive (five minutes into it). On the way up, the leak persisted (as it would), so at their first stop, at 50 feet, he fiddled with the valve again, and discovered he hadn't closed it.
Now, this is a man who TEACHES this stuff, who can probably run through a faultless valve drill in his SLEEP, and he had turned the valve the wrong way. THAT's the kind of thing narcosis is going to do to you, especially when you are unaware of it. It will impede a swift and effective response to an unexpected problem.
As someone who has had a couple of EXTREMELY unpleasant experiences with paranoid narcosis, I have enormous respect for the capacity of nitrogen to interfere with my ability to make good decisions and get things done.