| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: About having a large SGA for a DataWarehouse
If you have work_ares_size_policy set to AUTO, you don't need to set =
hash area and sort area size. Even though you set it, Oracle will not =
use it.
Chaya Patamalla=20
I/T Database Administrator=20
--"The opinions expressed herein are solely the author's and are not =
necessarily the opinion of USAA."=A0--=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sai Selvaganesan
Sent: Monday, July 19, 2004 1:13 PM
To: oracle-l_at_freelists.org
Subject: Re: About having a large SGA for a DataWarehouse
in 9i
Are the Hash/sort area sizes required even when using =
work_area_size_policy =3D AUTO?
=20
are these not supposed to be taken care of automatically for each =
process. so wehen we say "increasing sort and hash to 64M" should these =
be set to 64m or oracle will do this "automatically"
=20
please advise
sai
Hemant K Chitale <hkchital_at_singnet.com.sg> wrote:
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=20
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
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |