Five posts in and there are already more than five holes I dislike!
There are many fine holes that are the 18th hole, because too many courses save the best for last. But there are also a lot of cliches about the 18th hole and how tough it ought to be, that I hate. Phil Lipper's list of 5 covers most of them -- long and difficult hole, water all along one side, etc. [yawn]
For me, the success of a finishing hole is that it reflect the character of the course and where it is.
A few that come to mind from the U.S.A.:
18th at Garden City Golf Club - the only par-3 finisher that I really love
18th at Oakmont - perfectly in character with the rest of the course
18th at Stonewall (Old) - still probably my best finishing hole, I just love the way the clubhouse is a part of the hole, but it also demands two good shots and the tilted approach is reflective of the course's primary challenge