About having a large SGA for a DataWarehouse

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Mon, 19 Jul 2004 20:32:23 +0800
Message-Id: <6.1.0.6.0.20040719202749.0245b388@pop.singnet.com.sg>

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 :

Performance impact in Checkpoints -- larger checkpoints, longer write times for DBWR
Performance impact in Free Buffer scans -- longer buffer chains
Performance impact on Cache Buffer Chains latch -- more buffers per latch means that the latch may be held more frequently
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Mon Jul 19 2004 - 07:36:11 CDT