How much randomness makes sense?

Like most people I got my first experiences in tabletop RPGs with games such as D&D and TDE that use d20s. Dice with a big range and high variance leading to very swingy rolls, especially at low and mid levels when the static bonuses are dwarfed by the range of the die. This works for creating the dramatic and memorable moments of critical failure and success that I assume are the point of such a dice resolution mechanic, but it also leads to weird failures at routine tasks and a general unreliability of character skill. I dislike this effect and became interested in mechanics that reschew randomness.

Ignoring systems such as Dread, where character skill is replaced by an unrelated player skill, there are systems based purely on resource management. Dogs in the Vineyard has its escalation mechanic and the Sorcery! mobile game manages to create suspense in a non-random combat system by not knowing how many resources the opponent invests until the round resolves. However, upon further reflection I found that human experience does include perceived randomness: Especially when attempting something one isn't already very skilled at, multiple attempts can fail and succeed apparently at random, because one is of course always trying to do it right and cannot always immediately understand what was different the time it succeeded.

Of course there are system like GURPS that use 3d6, creating a bell curve that makes for much more consistent results while still leaving the possibility of extreme success and failure. Dice pools similarly reduce the variance of the roll. But I wonder whether a resolution mechanic could combine resource investment and randomness in a way that feels less luck-dependent without completely abandoning dice. Roll-under systems are an approach to this, since the skill level can approach the dice range to a degree that make success quite reliable, but this is ultimately a false impression - target number systems and roll-under systems come out to pretty much the same math as there will always be a minimum/maximum roll to succeed with a growing static modifier that ultimately overtakes the dice range. Roll-under simply feels different, because the target number tends to vary less wildly.

I am now trying to experiment with hybrid systems that correlate skill with reliability in other ways, such as replacing dice with static bonuses as skill values grow, representing the consistency of well practiced actions, or allowing the players to replace dice with static values through investment of resources (such as actions or stamina). Finding a balance that results in engaging gameplay while managing complexity is difficult though, and I haven't been able to craft something that feels quite right.

So now I'm interested in other people's thoughts: How integral do you think is randomness to an exciting game experience? How reliable do you think character skill should be at different stages of competency? What games have you played that take an interesting approach to conflict resolution? How much influence should a player have on their chance of success beyond deciding whether or not to try?