The extent to which different DMs use the dice is covered under "The Role of Dice" on pp 236-37 of the DMG. You seem to be advocating for the approach known as "Rolling with It". One of that approach's drawbacks, according to the DMG, is that it can lessen roleplaying if players' die rolls always determine success or failure, rather than their decisions.
I'm not averse to rolling dice. I'm not a proponent of "Ignoring the Dice", as you seem to be making me out to be. But I do think, for me, fun is heightened when game-play is punctuated with fewer, more dramatic die rolls, rather than when die rolls become more routine. Again IMO, this is best supported by what's described as "The Middle Path", which takes a healthy dose of DM adjudication and is summed up with the quote, "Remember that dice don't run your game -- you do." I believe this is the playstyle the game is best set up to support.
My "middle path" isn't going to look just like anyone else's, however. What it means to me is that I'm only going to engage the fortune mechanic in situations where there is enough uncertainty about the outcome, in my view, that it could go either way. And I'm not an arbitrary or capricious DM by any means. I simply have some guidelines I follow about what sort of circumstances will make the outcome of certain actions uncertain.
Is a ninja, or anyone else for that matter, stealthy enough to escape the notice of a sleeping creature that by dint of being unconscious is unaware of its surroundings? Yes! All it has to do is declare that it's being stealthy and the sleeping creature won't notice it. And even if the ninja doesn't try to be stealthy, the sleeping creature won't wake up unless a loud noise, or some other disturbance wakes it up, so all the ninja has to do is not make a loud noise to avoid waking the creature. So unless the ninja's player tells me they are making a loud noise or trying to wake the creature, it stays asleep.
Does the sleeping creature wake up in time to defend itself? I'm not sure how Wisdom (Perception) applies to the situation. For me, it usually comes into play when there is an actual attempt to detect something. Sleeping creatures aren't trying to detect anything. Perception "measures your general awareness of your surroundings", but we already know how aware the sleeping creature is of its surroundings. It's unconscious and therefore completely unaware of its surroundings. To me, whether the creature wakes up in time is better measured with a DEX check. When combat begins, roll initiative. If the sleeping creature wins, it wakes up in time to take action. If it loses, the ninja attacks first.
Why? If appropriate conditions prevail, you can very easily gain surprise over your victim and strike the first blow. If they don't, then your particular talents aren't coming into play. Stealth doesn't keep people asleep while you're attacking them after all.
Unaware of surroundings is unaware of surroundings. You can't keep watch while you're asleep. Even the Alert feat requires you to be conscious to avoid surprise.
A more apt analogy would be if an enemy was about to attack you, and you told me you were going to make no attempt to avoid the attack. In that case, I would rule it an auto-hit. A sleeping creature is making no attempt to notice things around it, so its Wisdom check is irrelevant in answering whether it notices something.
Your "logical disconnect" is in thinking a Perception check is required to hear and be awakened by a gun shot going off next to your head. Why on earth would it require a Perception check to hear any loud or even normal level of noise within hearing range? In the games for which I DM, hearing any such noise is automatic, and a sleeping creature is nearly always awakened by a loud noise. So, while in the situation you describe, there would be no chance of you hearing and being awakened by the person sneaking into your room, the gunshot would result in your immediately regaining full consciousness, no check required. Does that clear things up?
Then let me make my point more succinctly: what's the point in choosing either Stealth or Perception as my trained skills? They would be totally wasted skill choices because you don't ask for skill rolls in situations where a creature's skill level in either 'noticing' or 'not being noticed' comes into play. You just 'decide'.
In your game, how would I make an assassin? I know you're going to just declare that I'm automatically successful in my Stealth attempt, or that I auto-fail. Therefore, my 'stealthy assassin' concept is better realised without wasting one of my skill proficiencies in Stealth.
As a parent, my son only has to make a very little noise to wake me up, and he's down the hall, through a closed door, and in another room. My wife who is a much deeper sleeper often sleeps through both the noise AND my getting in and out of bed.
Adventurers would be no less hyper aware while sleeping, since it's basically their job to be aware of potential threats. Adventurers who are not hyper aware while sleeping usually end up dead from a surprise attack.![]()
Yeeeeah. If you want a serious response from me, you are going to have to respond to what I said, not that fantastic fiction you made up.Since you treat non-elves as fully conscious and keeping watch when asleep in your games, and elves remain only semiconscious when trancing, why would elves choose to use trance instead of just going to sleep with everyone else?
Yeeeeah. If you want a serious response from me, you are going to have to respond to what I said, not that fantastic fiction you made up.

(Dungeons & Dragons)
Rulebook featuring "high magic" options, including a host of new spells.