Skip to Main Content
  • Questions
  • PGA Vs SGA in Oracle Based Data warehouse/OLAP

Breadcrumb

Easter

Question and Answer

Connor McDonald

Thanks for the question, Rajneesh.

Asked: March 18, 2020 - 5:00 am UTC

Answered by: Connor McDonald - Last updated: March 23, 2020 - 4:09 pm UTC

Category: Database Development - Version: 19 c

Viewed 100+ times

You Asked

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?


and we said...

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.


and you rated our response

  (1 rating)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

Thanks for your help

March 23, 2020 - 11:03 am UTC

Reviewer: A reader

Nicely explained ..thank you.
Chris Saxon

Followup  

March 23, 2020 - 4:09 pm UTC

Glad we could help.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database