I definitely think Enlightenment is only really useful in the Vicious dungeons. Not only do you end up with more XP in most of them (more per-level gain), but they're the only dungeons I can think of where the kind of careful play required to prepare for it (enough beads to really feel the effect, notably) doesn't end up being complete overkill. In Hard dungeons or easier you're better off either burning your Piety on Protection + Potions or Converting... and this is also often the case in Vicious dungeons too, especially if there's a Dracul altar lying around (GG - Dracul is one of the best God combos by far) Though sometimes I still use it when I don't need it, 'cause I like burning all of my Piety on the 100 Piety boon
You can still feel some of the effect, especially if you use the bonus XP for a catapult, and like Darvin says +5 mana isn't bad at all.
Aside all that, Enlightenment is for dedicated GG worshippers, but I mean *really* dedicated. The following factors contribute:
-Being a Paladin
-Desecrating an altar or two (*very helpful*)
-Heavy Conversion (this is another reason why Vicious dungeons are better for this, since they usually give you more gold to buy conversion fodder)
-Maximizing the opportunities to get piety from Burnt Offering and Poison/Mana Burn (this isn't necessary for casual GG use tbh, it's more of a question of laziness and attention
-Undead Heavy maps (situational)
-Not using the Church Preparation to prepare the GG altar(which makes the planning all the more difficult)
And with all that, careful management of your Piety. Probably 5 Absolution shots, and a bunch of Cleansing (which you can both justify and attenuate cost-wise by repeatedly getting yourself poisoned and mana-burned!) to both get more beads and prevent Piety overflow. Like I said, it's pretty rare that you actually need to do this to beat anything below Vicious, and few dungeons below Vicious give you the opportunity and resources to pull it off...
But when you do... Man, does it feel good! XD