Hemant, We currently have a 1 terabyte warehouse. It's SGA is currently set at 4Gig. PGA Target at 2500M. I'm not sure how "big" your users want your SGA to be. The real answer (in my opinion) is that it needs to be as large as it needs to be. Picking a number out of a hat to start with might be the wrong approach. But you have to start somewhere. If you can post how many users you expect to be querying the warehouse during the day, we all might be able to help. Tom Mercadante Oracle Certified Professional -----Original Message----- From: Hemant K Chitale [mailto:hkchital@xxxxxxxxxxxxxx] Sent: Monday, July 19, 2004 8:32 AM To: oracle-l@xxxxxxxxxxxxx Subject: About having a large SGA for a DataWarehouse At my site we'll be migrating a [currently very small 20GB] Corporate DataWarehouse from 32-bit 8.1.7 Solaris to 32-bit 9i Linux. It will grow rapidly with Manufacturing Datamarts to probably 500GB in a year. Some of the DataMart teams are pushing for a very large SGA while I would rather have a large PGA_AGGREGATE_TARGET [not using 10g and Automatic Memory Management] My rebuttal was : We must consider how large we really need the SGA. A too large SGA [assuming that it is not too large as to cause excessive Paging and begin Swapping at the OS level] can result in : a. Performance impact in Checkpoints -- larger checkpoints, longer write times for DBWR b. Performance impact in Free Buffer scans -- longer buffer chains c. Performance impact on Cache Buffer Chains latch -- more buffers per latch means that the latch may be held more frequently d. Delayed Block Cleanouts -- modified blocks remaining in memory requiring cleanups and causing potential ORA-1555s Comments / Recommendations / Corrections from this list, please ? Hemant K Chitale Oracle 9i Database Administrator Certified Professional http://web.singnet.com.sg/~hkchital "A man's reputation is what other people think of him; his character is what he really is." -- Miner, Jack ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------