You need O availability for most upgrades. And elite/elite+ can upgrade Y to Z.
Agree that award redemptions are generally better value than upgrades but if you are never flexible with dates (thus can't book travel depending on when there is availability), and/or someone else is paying for tickets (and they tend to be Y/B/M/U), day of departure upgrades are an alright use of miles. And in my experience, no particular class needed, just an empty seat.

Obviously, this is a very specific set of criteria that make upgrades sort of worth it for me. But another thing people rarely mention is that award redemptions require all those surcharges which can be pretty high on some itineraries. With upgrades, you spend your miles and that is it.