Firstly and simply what is PGA and SGA in context of Oracle database? What are role of PGA and SGA and how stuff works?
Is it true that data warehouse need more PGA and less SGA?
If true then what is rationale behind it?
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.