This tuning gives your guitar a wide, deep sound, when playing in the key of D. It gives you plenty of options way up the neck, as well, for playing the top three or four strings. If you playing chords up the neck while striking one of the three low strings for bass the results can be dramatic.

Experiment with the three fingered chords above the first position, and finger pick and alternating bass. Flat picked strumming also a deep rich sound. Wrap your thumb around the neck to hit the 2nd fret when playing E or A based chords(chords you may use in the key of D). And add your fourth finger on the Fifth fret of the 6th string when you play some G based chords (G works OK with the low D as well, though).

You'll find that the Dropped D is easy to learn. It provides you with lots of creative possibilities, and it extends the range of your guitar - but I don't think it is a commonly used at the Irish or Celtic sessions. There are some other D tunings popular among this crowd, but they are more extensive retunings - and probably a topic for another thread.