Generally, it's a good idea to have a "theme" when you mod. Do you want to change the game entirely? Do you want to redo it the way it "should" be? Or are you just looking for anything that seems cool?
A lot of mods fall into the first two categories. Here's an example from each:
Change The Game Entirely: Oscuro's Oblivion Overhaul (OOO) does just that. It takes out the scaling mechanism entirely and replaces it with static levels. Did you ever notice how Bandits are wearing wimpy weak armor when the game starts out? Have you also noticed they're wearing daedric armor when you hit level 35? That's the scaling mechanism in action. OOO does away with it. It makes the game more like a standard RPG. The bandits you meet at level 1 will be the same when you hit level 35, as an example.
The "way it should be": Franceso's Levelled Creatures/Items mod, according to many, makes the scaling work like it should. Some people feel like the way Bethesda implemented scaling was partially broken and doesn't really work as well as it could. This fixes it.
Just two short examples. As a point of interest, not everything scales in vanilla (unmodded) Oblivion. I remember ending up in a dungeon when I first started playing and getting owned in about 3 seconds by a non-scaling high level NPC. Oops. Usually the NPCs that carry extremely powerful weapons don't scale, to keep you from getting it at level 1 and dominating everything with it.
Another big category are texture replacers. They make everything look good. Really good. Oblivion had awesome graphics for 2006 (and there wasn't a machine in 2006 that could handle Oblivion on max settings), but they're sort of lacking by 2010 (post-Crysis) standards. Machines can run Oblivion on max settings now and not even stress over it. Certain things (particularly faces) looked poor even in 2006, to be quite honest. Texture replacements came out within days of Oblivion being released. Some of them also look crappy by 2010 standards, but they were nice in 2006. Oblivion modding is not all that different from Morrowind modding, so almost all the early Oblivion texture replacers came from people who had done it in Morrowind and didn't need to learn much. A good example of a texture replacer is Qarl's Texture Pack 3. It'll stress out even modern machines, unless they are specifically built for gaming. There's also a mod out there called IFT (Improved Facial Textures) that makes the faces of Cyrodiil less frightening.
Here are the mods I'm currently using, as of 6/23/10:
OOO - I've never messed with the Overhauls much, so I'm trying out OOO first. Static appeals more than dynamic to me, so OOO seems the place to be. Hopefully I'll try out Fran's and Warcry at some point in the future. No opinion on this yet as I haven't seen a difference in my (under an hour old) save game yet.
Arthur's Natural Environments - A very awesome environment changer. It gives better looking water (though there are a _ton_ of mods out there that do that), adds insects and birds, and other improvements. You can also pick which elements you want. do you have replacement water you like more? Then you can disable the water replacer. Very, very highly recommended.
Better Cities - Makes cities better. Rearranges them, perks them up. This is an awesome mod. Keep in mind, though, if you use Better Cities it'll likely conflict with any other mod you have that alters the cities in any way, except for texture replacers. Luckily, BC is popular enough that you can find compatibility patches for a lot of conflicting mods. Also, this mod is slightly confusing (and, no matter what the people on message boards say, the installation instructions that comes with the mod DOES NOT give 100% complete instructions. I plan on doing a tutorial soon on the precise way to install it. I installed it incorrectly twice before I got it right.) Definitely give this one a look, though. Recommended.
Qarl's Texture Pack 3 - Mentioned above, you'll see immediately changes. If your computer has trouble keeping up with Qarl's, try Qarl's Texture Pack 3 Redimized. It looks nearly as good but isn't nearly as hard on your machines. Highest recommendation.
Bomret's Texture Pack SI - Qarl's only covers the original Oblivion. Bomret made a texture pack for Shivering Isle. If you own SI, this is also a must have.
CM Partners - True companions are added by this mod. I have a nice fellow named Arthur following me about in my game currently. The mod itself adds 70 companions, and there are "plug in" mods for more companions from other authors. However, don't think you'll have a small army of 70 people following you around. Your computer would roll over and die if you tried that. The mod author knows this and, thusly, there's a 6 companion limit. You can talk to them and give them orders. (Such as "harvest everything around here" which is so insanely useful for clearing out farms.) This is a great mod. I enjoy it a lot, strong recommendation.
Cute Elves - Adds a race called "cute elves", I only have it for some CM Partner plug ins that need it. If you like the idea of the race, get it. Otherwise, skip it. I'm neutral on this mod.
DarkUID DarN - Perhaps the ultimate interface mod. Only screenshots can show you the greatness of it. I love it, and I'll give you a link so you can see it:
http://www.tesnexus.com/downloads/file.php?id=11280 You can also download from that link. Very recommended.
Eve HGEC - A body replacement mod. Let's face it, the bodies in Oblivion are nice, but not that great. This changes it, but only for the women. You can pick multiple body types to get just what you want. You can even set it up so they're naked when they take off their armor. Let's face it: That's one of the biggest selling points, I'm sure. However, everything looks nicer overall. Recommended.
Robert's Male/Female Body Replacers: Same as Eve HGEC, just made by a different person. I prefer HGEC, but that's just me. DO NOT load Robert's Female Body Replacer and Eve HGEC at the same time. Oblivion will get confused and crash almost immediately. Eve HGEC and Male Body Replacer should be fine, though. Recommended only if you don't like Eve HGEC.
Low-Poly Grass: Having FPS troubles? Why not try out Low-Poly Grass? The grass looks about the same (some think slightly better) as vanilla Oblivion, but due to the lesser complexity of it, you'll see an FPS boost. Highly recommended.
Ren's Beauty Pack: Adds new hairstyles and a new race, the Mystic Elf. It seems to be conflicting with another mod I have so I'm not running it at the moment, though I have in the past with no problems. Recommended overall, however.
Oblivion Cats: Adds cats, simple as that. It gives the world a little more life and I enjoy it. It's exactly how you'd imagine it. Recommended for all cat lovers.
Crowded Roads: The roads are awfully empty, don't you think? This adds lots of life to them, with travelers. It even comes with plugins to modify the foot traffic on roads. Think the Silver Road needs more travelers than the default? Just install the proper plugin to increase the traffic. Highly Recommended.
No Psychic Guards: Have you ever noticed how, when committing a crime and someone yells about it, guards from all over the city come rushing in? That seems silly. This mod makes it more realistic, and basically limits it to guards within earshot of the person yelling. Recommended for those who dislike the way the guards act.
Mods I have installed but have not tried out yet (ie, not enough time to fully evaluate them or haven't gotten to the point where they're useful):
Kvatch Rebuilt: Don't you think it's odd that Kvatch stays as burning ruins for no matter how long you play the game? Yeah, me too. This fixes that. I haven't done the Siege of Kvatch mission yet, and this mod doesn't take effect until after that. No opinion as of now.
Sentient Weapon 2: There's a blacksmith who will build you a talking weapon. Sounds cool, might be annoying if done wrong. Haven't found the blacksmith yet (no map marker), so neutral on this one.
Origin of the Mage's Guild: Revamps the Mage's Guild a bit. My character just joined the Guild but this mod hasn't shown itself yet. (Literally, I joined the guild and then saved and quit.) Neutral.
Hoarfrost Castle: Similar to the official DLC Battlethorn Castle. Get control of a castle, build it up, add stuff to it. Personally, Battlethorn Castle is my favorite official DLC. I got Hoarfrost more for comparison purposes. No map marker, haven't located it yet. Neutral.
Bartholm: Similar to Battlethorn Castle, but your own ISLAND this time. Again, got it to see how it compares to Battlethorn. And, again, no map marker and I haven't located it yet. (Plus, even if I had, I couldn't afford to buy it yet) Neutral.
Kobu's Leveling System: An alternative to Oblivion's stock leveling system, which a lot of people (including myself) dislike. I logged 105 hours on Vanilla/lightly modded Oblivion, so I know I hate the vanilla system. Kobu's sort of falls into the "way it should have been" category. There's tons of other leveling mods out there, though. I plan on trying more of them eventually. No matter what sort of system you want, someone has made it. Neutral.
Unique Landscapes: Completely redesigns parts of the landscape. Comes with dozens and dozens of compatibility patches, so it should work with every popular, well known mod that alters cities. (for instance, Better Cities and UL conflicts.) Keep in mind, there's a huge series of Unique Landscape mods. I recommend you use the mod at
http://www.tesnexus.com/downloads/file.php?id=19370 which is a compilation of almost all of them. And is you use that, you'll want the compatibility all-in-one patch at
http://www.tesnexus.com/downloads/file.php?id=21496 I especially enjoy the patch because it auto detects what you have installed and only installs the needed patches. Much better than having to go through a list of 90 compatibility patches, hoping you didn't miss one you need. Literally installed it as I was typing this entry out, so no opinion yet.
Martigen's Monster Mod (aka Mart's Monster Mod or MMM) - One of the biggies. Revamps the monsters in the game. Hideously incompatible with a ton of other mods. OOO, Fran's, Warcry are all incompatible. Luckily, the people who maintain OOO have worked with the MMM team to develop a compatibility patch. It can be made to work with Fran's and Warcry via a mod called FCOM. FCOM is hideous to install, it is NOT for people new to modding or people who don't like doing a LOT of work. Rather than torture myself and try to setup FCOM for OOO and MMM, I just used the compatibility patch. If I ever want to integrate Fran's or Warcry, though, I have a lot of work in front of me. (To top things off, OOO and Fran's are incompatible as well.) Anyway, MMM is very configurable. I have never used it, though. It's installed and waiting to be tested, though. Neutral, but a lot of people say it's awesome. Probably a safe bet to download this one even without my review.
Unlikely to Ever Use:
(This is for mods I have but can't/won't use for some reason, unless fixes and/or compatibility patches come out.)
Cheydinhal Pet Shop - Incompatible with nearly everything that modifies Cheydinhal. Critically flawed and author has left the Oblivion modding scene. Unlikely it'll ever be fixed or see compatibility patches issued.
Insanity's Pet Shop - Developed as a working alternative to Cheydinhal Pet Shop. I just decided not to run it because I can't ascertain if it's compatible with Better Cities. CM Partners pretty much filled my need for companions anyway.
Hillsbourough Estate - Looks really, really cool. Buy a large estate, fix it up. You get a jewelry maker, wine maker, farm animals, etc. It looks like Battlethorn Castle on steroids. However, it's completely incompatible with both UL and Better Cities. There's lot of mods out here like this (I have two of them installed) so I decided I'd rather drop this and use UL and Better Cities. I am actively looking for compatibility patches for this one and they don't seem to exist. This mod is getting installed the second I find one, if that ever happens.
And that's everything! This entry is getting way long, so I'm going to end it here. The next few entries should focus on a basic introduction to modding (tools to use, etc), a short tutorial on installing Better Cities properly, and maybe an entry on the hell that is setting up FCOM. (I did it once. It wasn't fun.) And, of course, I'll add entries on my opinions of the mods I haven't rated yet as soon as I feel I've used them enough to make an informed decision.
Happy playing!