If you're thinking of getting it, look for the £5 Oblivion and Bioshock deal. I picked it up and I've spent more time on it than any PS3 game I've bought recently. It is only vanilla oblivion though, no knights of the nine or shivering isles. Still, mods more than make up for that.
I've got quite a few mods going though. Main ones for me are:
Oscuro's Oblivion Overhaul - must get (or at least some form of overhaul/balancing mod)
CM Partners - quite a universal companion mod, and there are plenty of downloadable companions.
A keychain mod - very useful to clear up all those keys
The Lost Spires - an epic quest mod
Streamline - a performance boosting mod, eliminates a lot of "loading area" messages.
Open Cities is cool, but incompatible with a lot of mods. I've stopped using this because it breaks a lot of stuff.
DarNified UI - interface mod, makes it much more suitable for PC resolutions.
Supreme Magicka - magic overhaul, makes it more powerful but not overpowering, and adds a few new spells. There's also Midas Magic, but I found that overpowered and a bit silly, with things like nukes and transformers

Natural Environments - a great aesthetics mod, and hasn't given me any noticeable slowdown. Remember to put HDR on with this.
There's loads of others, but just take a look at TESNexus and PlanetElderScrolls.
Remember to pick up OBMM (Oblivion Mod Manager) and OBSE 0.17 (Oblivion Script Extender 0.17), they're very useful for mods. I'd also advise getting BOSS (Better Oblivion Sorting Software) to optimize your mod load order, if you're running lots of mods. Wrye Bash seems to be recommended a lot, but I've not looked at it and I'm not quite sure what it actually is yet.