RE: About having a large SGA for a DataWarehouse

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 19 Jul 2004 09:39:03 -0400
Message-ID: <42BBD772AC30EA428B057864E203C999274476@MSGBOSCLF2WIN.DMN1.FMR.COM>

Data warehouses usually require using PQO, and this uses direct reads that will benefit nothing of big buffer cache.

Regards,

Waleed

-----Original Message-----

From: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg]=20 Sent: Monday, July 19, 2004 8:32 AM
To: oracle-l_at_freelists.org
Subject: About having a large SGA for a DataWarehouse

At my site we'll be migrating a [currently very small 20GB] Corporate=20 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=20
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=20
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=20 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


-----------------------------------------------------------------

----------------------------------------------------------------

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 - 08:35:40 CDT