I'm definitely for mods although not heavily modded/ too many mods. I myself usually run no more than 50 at a time with no real issues. If it weren't for mods, I would have moved onto another game by now.
Unofficial Oblivion Patch is an absolute must for all the fixes it makes in the game.
Oscuro's Oblivion Overhaul (OOO) is another one many people use and I'm no exception. It may not necessarily be a must but it certainly adds a lot to the game and I've had no real big issues using it.
Apachii mods are another set of my favorites. They may be fairly large downloads and could be considered cheat mods since there are lots of high quality items you can get at relatively cheap prices (merchants shops), but they are some more mods that I commonly use and have had no big issues running them.
Reznod Mannequins so you can put full sets of armor, you collect, on display.
Portable Campsite - carry around a bedroll, chair, and a tent with you.
Companion Share and Recruit for a companion mod. Relatively simple/ easy to use mod where most any NPC is recruitable if your character meets certain conditions.
There are lots of excellently rendered armor and weapons sets out there.
Of course there are also plenty mods out there that aren't problem free, but I usually read people's comments about the mod before I download them.