| 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
in 9i
Are the Hash/sort area sizes required even when using work_area_size_policy = AUTO?
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"
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
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 :
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
what he really is."
-- Miner, Jack
 |
 |