Metroid Prime had a system like this; the game would load the room you're in and all adjacent rooms. But if you very quickly rushed two rooms over, you'd have to sit and wait for the second door to open because the game hadn't finished preloading it. More RAM might've allowed it to do so.
Well, here's hoping the new console will have more RAM, and that would help the old Wii games with this.
Yah you can see that in Oblivion pretty plainly. If there was more room for textures then the loading times would have been cut down a bit. Draw distance increased too probably. I did not know my 360 only had half a gig of memory. Wow.
I heard that having a large HD in a 360 the game gets stored on the hard drive for drastically reduced load times.