Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> How big an SGA and PGA do you have set on your larger DB's?
zekmoe
post Mar 22 2012, 02:22 PM
Post #1


Newbie
*

Group: Members
Posts: 7
Joined: 15-August 05
Member No.: 2,740



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
Go to the top of the page
 
+Quote Post
burleson
post Mar 22 2012, 03:36 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,640
Joined: 26-January 04
Member No.: 13



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?


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
eric30mu
post Mar 24 2012, 07:22 PM
Post #3


Member
**

Group: Members
Posts: 16
Joined: 9-March 05
Member No.: 1,590



QUOTE (zekmoe @ Mar 22 2012, 07:22 PM) *
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.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 1st November 2014 - 06:03 AM