I'm using fire and frost magic explosions by knef, they look really great when in motion.
You can dl them http://www.gamesas.com/bgsforums/index.php?showtopic=991190&hl=.
I forgot about those. I don't think there have been any other mods that change the explosion effect. I'd like to see more of those!
edit: I just tested it with a greater fireball spell: http://i18.photobucket.com/albums/b120/iamerik/MW%20and%20Oblivion/MGEScreenshot29-3.jpg http://i18.photobucket.com/albums/b120/iamerik/MW%20and%20Oblivion/MGEScreenshot30-1.jpg http://i18.photobucket.com/albums/b120/iamerik/MW%20and%20Oblivion/MGEScreenshot31-2.jpg!
http://i18.photobucket.com/albums/b120/iamerik/MW%20and%20Oblivion/MGEScreenshot28-2.jpg