Recently went on an Oblivion spree, adding some new mods. In the Nexus' hot files slideshow on the main page, I noticed a mod by the name of Realistic Water. I've tried water "fixers" or replacers before, and always been underwhelmed. They either come out pretty crappy, or murder my FPS in a manner that would normally demand a trial be convened at the Hague.
I'm pleased - if surprised - to note that Realistic Water fits neither criteria. It does improve the looks; and did not make a noticeable hit to my framerate.
All in all, success.
If you're in the market for such a mod, I can recommend this one.