The "database concepts" guide is a fantastic resource for all of the workings of Oracle.

The memory is covered here

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/memory-architecture.html#GUID-913335DF-050A-479A-A653-68A064DCCA41

But in a nutshell...

1) SGA

Where common structures and data is stored. In a transactional system, then when *I* read a list of (say) products, then I want that stored in memory so that others wont have to read it from disk. In transactional systems, we commonly have lots of small tables that *everyone* wants to read all the time. And then we insert rows into our larger tables. But even those larger tables typically have lots of people reading the most recent data. "Show me sales from the last hour" etc etc. So we want to maximize the use of SGA, because its for those common structures and data.

Compare that a data warehouse. I'm going to query sales for 2019. You are going to query sales for 2018. Typically we're running huge queries that (a) often have little or no data overlap, and (b) could not the data entirely into memory anyway. The SGA gives me less benefit here, because we do not have many common ground.

2) PGA

Where information solely for a users session is stored. Each user gets their PGA (or chunk of the overall allocation for PGA). It is not shared. So when I do query that sorts, or needs temporary data which no-one could benefit from except me, it is stored in the PGA. On a transactional system, I probably don't sort much or sort large amounts of data. I'm getting just a few rows here and there for each transaction. I need *some* PGA for that, but not a lot.

On the data warehouse, I'm summing about all the sales for the past 10 years. That's potentially a *huge* amount of sorting. Queries aggregating lots of data will benefit from lots of PGA because ideally I do not want to sort on disk.