Start http://www.gamesas.com/index.php?/topic/449239-oblivion-mods-faq/.
http://tescosi.com/wiki/Main_Page is another excellent resource.
You can find 'prettyfication' mods on TES Nexus, under textures, and such like.
Oscuro's Oblivion Overhaul will make the world a much more interesting place, and adds a lot of content as well. Martingens Monster mod also does neat things to the world, and goes really well with OOO. if you are just getting started with mods though, best to start easy, instead of just leaping off into the deep end. (FCOM)
Oblivion Mod Manager and Wrye Bash both have the ability to help out with installing mods. There is a lot of overlap between the tool tools, but, I find them both Useful. BOSS is a must have, once your load order starts getting fairly long.... (all available on Nexus.)
If you modded for morrowind, you will find that some aspects of oblivion modding are easier.....