RE: About having a large SGA for a DataWarehouse

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Mon, 19 Jul 2004 08:03:37 -0500
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D607B00A29@EXCHMN3>

Hemant

I agree with you. The theory of having a large buffer cache for an OLTP database is that there are certain database blocks that will be used quite often and if those blocks are in memory, then performance is much greater.

With a DW, the situation may be different, depending on how the DW is designed. Usually a DW query spans a lot of blocks, so the possibility of keeping a particular block in memory decreases rapidly. Because many operations are aggregations, full table scans are the rule. That is why the partitioning option is often worth the additional cost. In a FTS, Oracle doesn't even try to keep the blocks in the buffer cache.

There may be exceptions. You may have some smaller tables that are frequently referenced. Consider creating a KEEP pool.

Best idea -- test! Don't speculate, conduct tests to see what yields best performance.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
I said it "looked" clear - Riddick

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Hemant K Chitale Sent: Monday, July 19, 2004 7: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 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


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

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

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:06:14 CDT