Honestly, if you just pick up a handful of mods, it's good to go.
Oscuro's Oblivion Overhaul. You can get it patched all the way out or go with the last full release. There's only 2-3 patches I think.
Oblivion XP - turns the leveling system into more of a DnD style where you get to spend the points however you want.
Oblivion Mod Manager - makes it super easy to stack mods, load mods.
Oblivion Script Extender (OBSE) - needed to get the system to run the more complicated scripts from some mods
Beyond that, I like a couple of other cosmetic / simple stuff. I'm definitely a screw-with-it-as-little-as-you-have-to kind of guy.
For instance, there's better map, better interface (DarkUI) mods. You can get a "better water" mod. I highly recommend the saddlebags mod for your horse, so you can use him as a storage bin on those big dungeon / cave raids.
There's also some other game tweaky stuff like Better sneaking (that makes it a bit harder to sneak around in close proximity to bad guys).
But in all honesty, if you do nothing but OOO, it still kicks ass. The rest of it is just extra icing on the cake.