RE: About having a large SGA for a DataWarehouse

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@xxxxxxxxxxxxxx]=20
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=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 :
   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=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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: