When the Homeric audience listened to the Iliad, they were not being shown something impossible in kind, even if some of the heroes were born out of literal gods. Achilles was the greatest warrior in the world, but his greatness was human-level greatness: speed, force, rage, grief, the willingness to pay an unbearable price for glory. He was mortal though, he knew it, and he chose a short and glorious life anyway. The audience may not be as good, but it was on the same scale. You'd get inspired, you could understand the sort of character that produced Achilles. The hero was larger than they were, not different in species, like the gods.
That is the central structure. To me, a useful hero is the same kind of being as the audience, only raised to a higher degree, while in the same scale. The gap is achievement, discipline, courage, sacrifice. The story teaches admiration and imitation. It tells you that excellence belongs to your world, not to some sealed category beyond it.
The Greek heroes were also broken in ways that made the identification sharper. Achilles's weakness in Homer is not a magic heel. It is pride.1 Odysseus is cunning enough to survive and reckless enough to get men killed. Their flaws are not narrative accidents, they are the point of contact. The hero fails in human ways, and that is what keeps the greatness from floating off into abstraction.
Enter Marvel, DC
Superheroes change the form of the relation. Their powers are not higher degrees of ordinary human possibility. They are different powers altogether: flight, invulnerability, invisibility, cosmic energy, reading minds, regeneration... Ok, I don't have any of those things. So what do I do then? You do not look at Thor and think, "that could be me if I become stronger, braver, more disciplined." You look at Thor and watch a god (small g) doing god things. You don't watch superman and be like: "yes, I'll try and fly harder". You don't watch the following scene and think: "yes, I'll workout my eyeballs and stop bullets with them too".
Enter death now
In the Odyssey, Odysseus visits the underworld and finds Achilles there, which compares the life in the underworld and says that he'd "rather be a slave above than a king in death". Regardless of theological views, the Odysseus did not bring Achilles back. Orpheus did not bring Eurydice back and that was the point. In storytelling, sad as it may be, you need to be clear that death is permanent and a tragedy. Life is not a videogame, you don't save the game and rollback. Tragedy helps us empathize, the death of a beloved character will make us value our loved ones more, remembering us that we will lose them at some point. Life is precious.
In many superhero franchises, death is no longer a terminal human limit but a reversible plot event. Once consequence becomes optional, the tragic mechanism goes away and we disrespect life itself. Fear and pity depend on shared vulnerability. If the hero inhabits a world where people come back from the death, then what's the big deal about dying anyway?
John Wick makes the contrast visible in modern terms. He is extraordinary, but he is still doing something a human being can do: endure pain, prepare, focus, move skillfully, impose will through trained competence. He bleeds, he slows down, he suffers. Yes, I chose him because he is very unrealistic, but I can see him influencing viewers to become better (maybe at shooting...). And to care about their pets. The audience will never become John Wick, but the story still lives on the same human map as the audience. It invites the thought that discipline, craft, and resolve are capacities to pursue, not merely powers to witness.
And it's not that stories need to be realistic. Batman for example does inspire us to workout, become smarter, better. Fantasy too, Aragorn, even the elves in Lord of the rings, supernatural as they may be they're still in human ranges (although very very high end). Gandalf doesn't really do much that other humans can't do on-screen.
In storytelling, you have an opportunity to inspire someone. To make them reflect, improve, learn. Superheroes kill the desire to do that. At most, they make you want to have superpowers, but often they just make you feel inadequate without them.
Achilles's heel as a physical vulnerability is post-Homeric. In the Iliad, Achilles is most vulnerable through his pride, withdrawal, grief, and rage.