ok looking at this from a programming standpoint there is different kinds of data that you need in ram for different amounts of time.
1) the programm code including the the game engine. the game engine itself has to be in memory at all times (if the processor would have to fetch a subroutine that he needs to branch to each time from harddisk before starting it will take times in milliseconds scale and not in nanoseconds), other parts of the programm like situational scripts ai routines... are usually not in memory all time but prefetched in time before they are needed and unloaded after they are no longer needed.
2) geometry data: big arrays with coordinates of the objects that build in the gameworld, these have to be modified each time when you move, or when the object moves.. so that data have to be in memory too to allow the cpu/gpu fast access to do geometery transformations
3) texture data: these are basically pictures that are projected on the geometry, the projection process usually takes part in the gpu, the texture data for all objects that could be visible at same time has to be in memory at the time when its displayed too (but if a object gets destroyed, or if you move so that a object gets out of visibility the texture can be removed and replaced by a new one)
4) framebuffer data: these are the fulyl rendered pictures after the gpu is done rendering that wait to be displayed on next picture swap. they take a fixed amount of data depending on the displayed resolution and the colordepth. ( should be roughly 4mb for one 1080p picture) so thats not too much memory needed for this.
5) sound data: these days sound can be streamed quite good from harddisks or optical drives directly with only a small memory need for the next few milliseconds of sound to cache. so this usually has a low effect on total memory usage.
lets look on the amounts of data we are speaking about:
texture data is definitely the number one here, high resolution textures are the biggest part what fills the game dvds/BDs. (you end up fast with a megabyte for a uncompressed single textures if you want them high res and each displayed gamescene usually has hundreds of textures)
depending on game type and how much objects you display at same time geometry data or program code will be next biggest part.
the rest only plays smaller parts in memory management since you have only fixed needs for this.
ok and to the ram types:
xdr is a latency optimized memory and good optimized for small things that need to be accessed often. it is optimiced for main processor needs to allow fast accessing or random elements in the memory.
gddr is bandwidth optimized, it can do very fast sequential reads, but it usually is slower in access times to a different memory area. it is built for graphic chips that need to read big textures as fast as possible so it needs fast sequential reads
ok after knowing all this we can look at the architectures of ps3 and xbox 360.
xbox360 has the simpler setup with just 512mb gddr ram, it has the advantage that you can dynamically switch how much memory you use for program code and how much for textures. but it has the disadvantage that the mainprocessor has to use bandwidth and not latency optimized ram so the processor might be held back by ram sometimes (which makes optimizing the program code for good usage of the cache memory inside the cpu even more important).
ps3 has split main memory and graphic memory of 256mb each. this means both the cell and the gpu have the optimized memory type for them but you loose the flexibility of allocation (in this part the ps3 is more like a gaming pc which also uses split memory architectures). in the ps3 the cell can read/write very fast to the mainmemory, he can write still quite fast to the graphic memory, but he is very slow when reading from graphic memory. the graphic chip can read and write fast to graphic memory and has also quite good access times to main memory.
so basically both architectures have advantages and disadvantages, both systems are a bit low on memory when compared to pcs. when you look at new pc games then these often already use 512mb graphic ram alone with the biggest part of that being allocated for textures, so both ps3 and xbox cannot run with that high detail textures as a modern game pc. if the developer can keep the game code small then the xbox can effectively use more memory for textures than the ps3. this leads to things like fallout 3 which has highest detail textures on pc, xbox360 as second for texture details and ps3 having lower details on some textures.
if the game has a very complicated game engine then the ps3 might have the advantages with the faster access speeds.