Pros: Useful for playing melody in some keys, especially D and G. Also useful for producing interesting harp-like arpeggios as backing, which can be nice for more mellow pieces. Low D gives nice drone note, and having open strings close together allows high drones on other notes as well. And it being almost an open D5 chord means that you can do some "power-chord" stuff with it if you're really short on inspiration.

Cons: Not so useful for playing melody in some keys, unless you capo. Harp-like arpeggios can get very same-y if you overuse them, and only really work if you're the only instrumentalist. Drop-D tuning gives you the low drone note without as much retuning (this will do your "make your guitar look bigger" thing). If you're competent enough to go higher than 5th fret and use open strings to fill out the chord, you can get the high drone effects in standard tuning. Power-chords are just as available in standard tuning.

Basically, it's just another altered tuning. Some songs/tunes work in some tunings, and some don't. Personally I find myself using drop-D a lot, because it gives me a good bass range and still leaves me with the upper strings working normally without a reduced range.

Choosing the "best" tuning is like choosing the "best" pair of shoes. They'll all get you around, but some are better suited for one task than another - sometimes you want a walking boot and sometimes you want a sandal. Some are even only suited to one event, like sprinter's spikes. And anyone who says "this is the only shoe to use" probably just isn't doing enough different activities to see where other shoes are better. :-)

Graham.