Why pray? To connect to the God of the universe in faith and expectations of good things, of definite responses, and definite answers to definite prayers.

You are correct that the expectation of petitionary prayer is to obtain an answer.

You are also correct in pointing out that results are often explained (away?) in confusing ways.

There is of course the failure of faith that needs to be addressed, because people are known to pin their own failures on God, when He absolutely still heals and saves people today, and we have many promises Jesus made in regard to that ("whatever you ask in My name, I will do").

Jesus always got His prayers answered. On the other hand, there was also that point in Gethsemane where He said "not my will but Yours" and so it's always a good idea to find out what God's will regarding a situation is, before one sets themselves to petition for anything. However, Jesus already knew the Father's will but was struggling to submit to the reality of it. He already knew why He had come. He already knew what had to happen. But He was also human and was daunted and distressed by the nearness and reality of what was to come. He knew at any point God was able to deliver Him. But He also knew that He had already agreed to do what needed to be done and was determined to see it through.

People die. It's part of life. God often delivers people BY death on to escape this present suffering on this sin-filled planet. Death isn't a punishment, neither is it a failure. It's just part of our current human experience.

When God puts someone on my heart to pray for, it's always in a specific context for a specific reason. It's on me to take that back to God and see to it that the matter is satisfactorily bathed in prayer to the point where God tells me "ok you can stop now." So then I stop, kick back and relax, and the answer always comes in the physical like God told me it would, at times almost immediately, at other times weeks or months or even years later. It's not about me, so I don't care; long as God gets what He is after, that's all that matters.

But as one begins to give onself to prayer as outlined in Scripture, there's also resistance to be overcome, and faith being challenged, and so in that way prayer can put a workout in the gym to shame because it really does require a bunch of things that a person unfamiliar with God and prayer cannot readily see because they happen in unseen ways. Prayer is nuclear power as far as the true essence of its potential is concerned, because God's power is infinite. But it all has to happen in submission to His overall plan.

If more believers gave themselves to prayer unequivocally and put their faith in action in this way, we'd see a lot more things than we do now when it comes to salvation, healing and deliverance. Of that I am certain. Because God has not changed. Not even a little bit. It is to our shame that we have only words at our disposal that an unbelieving crowd of people hears but cannot reconcile with the evident lack of God's power on display before them. I got that. And, I honestly have nothing to say to its defense. However, there are a great many people who put their faith in action and who really do get a hold of God and who He really does listen and respond to, but then again you won't find many of those on a message board or out in public because prayer is their life and they prefer secrecy and get their recognition from God alone.

Which obviously makes prayer a subject that cannot truly be judged based on outward evidence, as so much of it boils down to transactions between a person and God in ways that nobody else can see or hear. That's just the nature of the beast, and so beware of generalizations and sweeping judgments as they will get you no closer to the answers you seek.

Bottom line: Prayer is a complex thing, it's a marvelous thing, and so I'd encourage you to seek God for yourself to find out what it's really all about.