How big an SGA and PGA do you have set on your larger DB's? Options

[zekmoe]

I'm wondering what people have set for the SGA and PGA sizes for their larger usage, larger data, databases? I've been seeing one of our warehouses grow both in terms of tables (number and sizes) and users groups querying the database. We're at 96g sga and 10g pga, but was thinking in terms of 1/2 tB machine to pin some larger tables. I know we'll have SSD soon, but am seeing enourmous numbers of reports using windowing and analytic functions and in line sets being created. How big in general do you have your larger systems set to?

Bob

[burleson]

Hi Bob,

>> thinking in terms of 1/2 tB machine to pin some larger tables.

An SSD rack? What vendor does 500 gig SSD devices?

***********************************
>> How big in general do you have your larger systems set to?

Good question!

They use 80-90% of the available RAM, on a dedicated server for one database . . . .

Use it all, no reason to waste!

The SGA sizes tend to track with the RAM on the servers.

Below is an IBM Regatta series with 128 gig RAM and one database.



It has a 96 gig SGA and 20 gig PGA.

PLease read the specs on this monster database:

http://www.dba-oracle.com/t_server_deconsolidation.htm

It also depends on the percentage of the database that gets cached.

For example, it's not uncommon to see 100% data buffer caching for smaller databases, say, anything under 50 gig.

With SSD, SGA becomes irrelevant, and a 100 gig SSD you don't need a large data buffer at all.

There is a good book on SSD here:

http://www.rampant-books.com/book_0501_ssd.htm

*********************************
>> We're at 96g sga and 10g pga,

I'm guessing you have 128 gig on the server?

[eric30mu]

I'm wondering what people have set for the SGA and PGA sizes for their larger usage, larger data, databases? I've been seeing one of our warehouses grow both in terms of tables (number and sizes) and users groups querying the database. We're at 96g sga and 10g pga, but was thinking in terms of 1/2 tB machine to pin some larger tables. I know we'll have SSD soon, but am seeing enourmous numbers of reports using windowing and analytic functions and in line sets being created. How big in general do you have your larger systems set to?

Bob

I am not sure about pinning larger tables, assuming that they will be accessed parallel. Doesn't parallel access by-pass the buffer cache? If you're not using parallel query, then you will need enough memory to cache the entire segment(s). Have you look at the memory advisers? I suspect some of your hash joins will use a lot of PGA.