Significant performance advantage, if the applications you're using actually need a lot of memory.
PCIe x16 bandwidth is about 4GBps, AGP8X is about 2.1GBps. A Geforce 7900 has 51.2GBps bandwidth to its memory.
Getting a 256MB video card (not a HyperMemory or TurboCache, actual 256MB on the card) then you're pretty much able to run everything with good resolutions and texture detail and the like. Even 128MB is okay. If you end up only occasionally running things that require lots of extra texture cache, then it's not necessarily a big deal if the card has to go to system memory to get it, but if the majority of your time is spent with things that need more memory, you'd end up wishing you had more memory on the video card.
If you get a card with only 32MB of memory onboard, you may as well not even try to play current games, it's equivalent to having no memory onboard, no matter how much HyperMemory or TurboCache it supports. Even 64MB onboard isn't really enough anymore with the most current games. 128MB is the minimum for some games to even run. Although the games will run if the video card has 64MB onboard and reserves 64MB from system memory for 128MB total, performance will be significantly affected compared to a real 128MB card.