You may as well ask what's best in life (waits for it...).
For starters, lets look at the major gameplay overhauls (and no, I'm taking the time to link - Google is your friend):
- Oscuro's Oblivion Overhaul (OOO) rebalances the entire game, and adds in a ton of new monstors, quests, items and locations. It makes the game a lot harder to start out, but lets you kick ass and take names at high levels (where you only have a few high level NPCs to fear). This is the mod I run, and I enjoy the hell out of it, and makes the game feel more like Morrowind.
- Francesco's mod also does a lot of rebalancing, but it limits itself to the level lists and items. I've found it to be easier than OOO. The community is mostly split between OOO and Frans; some like OOO's more radical changes, others prefer Fran's more limited approach, which is more compatible (although I've only had two compatibility problem with OOO).
- Martigan's Monster Mod (MMM) has versions that work with both OOO (not sure if its released) and Frans (for several versions now). It adds in whole bunch of monster spawns (1000+, IIRC), and lots of brand new monsters (far more than OOO or Frans). You'll know fear when two dozen pissed off wolves decide your level five ass is lunch. I played this mod once, but I haven't gotten back to it since I switched back to OOO (it was incompatible at the time).
- OOOFransMMM (I think I got right) tries to combine the best of all three. It was in beta last I check. All of these have some dedicated developers and lots of testers.
- Warcry makes the game more like Diablo, with lots of action. It conflicts with the above mods, but it has a loyal following (especially in Germany, and it does look cool).
Now, if you want to change the PC leveling system to something more realistic and in the background, I'd suggest checking out Kubo's Character Advancement System (KCAS). It looks hard to use at first glance, but you can install it and run with default options with no problems.
To spruce up the graphics, look no further than
TOTO. It has the best texture overhauls, with different versions to fit your machine. Also, the site contains pages dedicated to quest mods, as well.
For better environments, look up the Unique Landscapes project on the official Elder Scrolls forums. They do good work, and I have all but two of the current releases (I didn't like the themes for the two I didn't download). Just ignore the couple of posters that like to spam the thread...
Weather gets a nice overhaul with the Atmospheric Weather System (AWS), which also adds in weather sounds in doors. Another modder has made several extensions for it, as well (lightning, rainbows, etc).
For magic, you might want to check Mighty Magic. And there's another mod, PJ's something or other (hey, I'm a sword and heavy armor guy...).
Also, there are tons of character mods, which upgrade the NPCs looks and add in new races. Beautiful People, Improved Facial Textures, Cosmetic Compilation, and others. And you can find adult content mods as well, if you go looking (you won't find those on the official forum).
That's all the major ones I can think of at the moment.