I have 4 gigs, and it's not *quite* all it's cracked up to be (and won't until I make the x64 switch).
Main problem is that the 32-bit Windows systems are all limited to 4G total, and so between your PCI devices (video card inc'l) and your RAM, you can't have more than 4. Each system handles this differently, but with my current 7900 KO-256 in there, XP recognizes 3.5 gigs of RAM.
Does that help with Oblivion more than 2 gigs would? I'd say it depends on how clean your OS is. If you can minimize the amount of tasks going on, I doubt you'd see a huge improvement, since (if memory serves) Oblivion is more CPU- and video- intensive than RAM intensive, beyond a certain point.
What it would let you do is not have to worry about closing every last task whenever you wanted to do something like play Oblivion.