The hero that doesn’t fail isn’t relatable. Superman is a icon but basically having one weakness is outside anyone’s experience. Batman doesn’t have powers except being gifted with unlimited money, the peak of human performance, and supposedly the world’s greatest detective mind.
They can have interesting stories, but I don’t see a whole lot of the relatable human experience in them.
Spider-man gets it fairly close. He gets powers, but can’t pay his rent. He has real world problems, but still he can lift a truck with one arm.
I think the best characters are limited. They may have a gift, but those gifts are within the realm of human experience for the most part. John McLean in Die Hard dragging his bloodied feet, low on ammo, busted and bruised but not giving up. Riley in Alien takes a beating, is scared, keeps on and wins. Both of these characters were over-matched and outgunned.
I want heroes that have gifts and weaknesses. Even if they’re robots. Anything anthropomorphized can be relatable if it has a healthy dose of our own limitations and uncertainty and still overcomes overwhelming odds.