It's very good in the following situations:
1) You've got a ROCK steady tripod, and use a cable release.
2) Nothing in your scene moves between photos.
The alignment algorithms are pants, so if your photos aren't pixel-perfect aligned it wont give the results you need. Also, because it can't handle moving elements it's use is limited in climbing photography, for example. A lot of the time you'd be better off manually blending images taken at different exposures using complex layer masks.
Also, there are few occasions when it's really necessary, and produces hideous looking results when used gratuitously.
When it's useful - it's awesome. Peak scenic sunsets or sunrises are a good example.
