It is a heuristic. Which is to say that it is a fancy way of saying that it doesn't really work. If you want to be a real control freak, it is possible to manufacture cases in which you want operations to go in any order you could possibly ask for, and there is no way for Perl to meet every theoretically possible case.
In fact if you want some real room for foot shootage, just look at exec and exit. If someone happens to include those in the code, your END blocks don't get to run at all...
The heuristic is that your BEGIN blocks are for initializations that you want to happen early, and END blocks are for final cleanup. Furthermore anything that appears after you might need your functionality, therefore your initializations need to happen before them, and your cleanup has to occur after them. Therefore BEGINs are FIFO and ENDs are LIFO.
Now to your bullets.
I want this piece of code in my main program to be the very last thing executed, so where do I put it? What is your reason for wanting it to be last? Control of program flow? END blocks are not meant to be part of normal program flow. If you try to use them for what they aren't meant to do, it is no surprise that you can cause yourself confusion and pain. If not control of program flow, then what? Well probably cleanup. In which case see above. You put it after any initializations that you want available in your END block, either in your code or in modules that you load.
Well, before anything that you want to execute before it. Regular code that appears after it will execute before it (if it executes at all). You only need to worry about its placement vs other END blocks. And there it mostly does the right thing.
So, I put it at the top of the program? You place it at the point in the program where it is obvious that it will need to be run eventually. Which is generally directly after any initializations that it needs to cleanup, and we like this because putting related code together makes synchronization errors less likely.
Well, no. If you do that then it will be executed after the END blocks in any packages you use which might not be what you want, so put it at the top of the program, except after any use statements for packages that might have END blocks that need to execute after your END block. Did you want to insist on it executing last, or merely to clean yourself up? END blocks have been thought through as a way to clean yourself up. I have yet to see a practical complaint about them in practice. (Now if you want to complain, go take a look at the calling of DESTROY in global destruction, every so often I need to explain why that messed up to people and tell them how to fix it with doing their cleanup in an END block...)
But how do I know if the modules I use need to execute their END blocks after I execute mine? You should not need to know whether they have END blocks or not. Place your END after your initialization, and your initialization after loading any desired functionality, and your END blocks normally will have whatever functionality is reasonable. However there is an interesting case here when the functionality that you need might be AUTOLOADed at runtime, and the AUTOLOAD might add an END block of its own. In this case you will want to be very careful to make sure that the functionality that you need is exercised before Perl sees your END block. Which means that you either make sure the initialization is in a BEGIN block, or put the initialization in regular code and wrap your END in an eval. But note that I have yet to see someone ask a question indicating that they tripped up on this, and even in this pathological case the principle of putting the END block right after all initializations have happened is precisely what you want to do.
Read the source Luke. And hope that the authors thought to document the need. If the authors used END blocks as intended, correct usage will be obvious. (Just put your END right after your initializations and don't worry about it...) If the authors chose to miscode their module and put an Easter egg in the END without warning, well this is but the smallest of ways in which bad code can cause problems for the person using it.
Come to think of it, why might they need to do that? Normally because they have some state that they want to be properly cleaned up?
Well, the module might class data shared by all its instances that need to be cleaned up. This cannot be done as a part of any individual instances DESTROY method, so it has to be done in a END block. Gotcha alert. Your instances might want to use that data in their own DESTROY methods. But they might be in global variables somewhere that is not cleaned up until global destruction, which happens after END blocks. If this is a problem (I have seen it be occasionally) then the module will want to also manage all of its instances and finalize them during the END phase. (If you want access to virtually any other data, including your own internal variables, then you want to do this. Ilya does have a patch which is in 5.8 IIRC which has a heuristic that mostly gets global destruction right, but it isn't perfect.)
Ah! But that goves me a problem. My program is converting some irreplaceable flat-file legacy data to DB format. I want to ensure that the file gets deleted after it has been successfully input, and that is what I am going to do in my END block, but I need to ensure that the data has been succesfully flushed to teh DB first. If there is any chance that the connection will fail and the data I stored is lost or corrupted, then I don't want to delete the file. How do I handle this? You have irreplacable data which you are going to allow to be automatically deleted by possibly buggy code in the middle of execution? That would seem to be your biggest problem right there... But we shall suppose that the coder has good reasons for wanting to do this (umm..you are out of space and management refuses to buy backup media, OK, attempting to live with a PHB, I sympathize), how do you accomplish the act? Well in that unfortunate case I would decide on how I am going to track success/failure, and then in my END block, wrap my unlink in an if ($is_success) {...} block.
You've got backups of the files haven't you:) Before doing anything automatic and possibly nasty with data, I insist on having backups. I know I am human. I have messed up often enough to not trust myself, and I definitely know better than to trust someone else who has not yet learned to take proper precautions.
In summary. It is a heuristic. It can theoretically go wrong. But I have yet to see the order of execution of END blocks to not do what is desired in real code if the END block is placed directly after the initialization that it cleans up. Unlike, say, global destruction. Or even the ability of people to unexepectedly eliminate the END phase with an exit or exec. (If you use END blocks, make sure to plead with the sometime C coders to not call exit...)