I will try to run down some of the very best of the best when it comes to Oblivion mods. There are thousands out there that span many different sites. The best site to check out is probably Planet Elder Scrolls. There, you can check out various mods and they are rated by other users and free to download.
Francesco's leveled creatures and items mod
This mod takes away the main issue people have with Oblivion. When you first buy Oblivion, everything will level up as you level up. That means if you are level 1, all the enemies are level 1. If you are level 10, all the enemies are level 10. This does provide some challenge as xBox 360 and PS3 players can testify to but it also has a lot of problems. If you don't level up your hit points and strength every single time, by level 8-12, your enemies will severely hand you your behind.
Francesco's mod makes creatures in the game have more static levels. That means if you are level 1 and wander somewhere you probably shouldn't be, you will die. Animals are tougher at first but become easier as you level up like most standard RPG games. This means that rat that annoys you at level one is easily dispatched by level 5. In the non-modded Oblivion the rats did stop levelling at a certain point anyway but Francesco's makes it seem more natural. Random bandits could be various levels and you can usually tell by what they are wearing. A bandit in fur armor is probably easier to kill than a bandit in a full set of steel armor.
Beautiful People
When you first play Oblivion, it won't take long to realize that the entire population has been beaten severely with the ugly stick. Beautiful people changes the way the NPC's look as well as gives you a large amount of customization options for your character. You don't want to play for 50 hours looking at some hideous beast you made with the goofy standard options, now you can have a ton of hairstyles, eyes, and overall cool things. Combine this with a mod that adds nice clothes and armor, and you have turned ugly and boring into pretty and interesting.
Natural Environments
This mod is very simple in nature. It adds minor things to the environment that are missing. Birds, insects, rainbows, and seasonal weather are some great examples. These really add to the immersion of the game. You may not notice much if you first play with this mod but when you take it away, you definitely will.