Howto Use A Large SGA for Oracle 10g

[TwoProc]

I didn't see an indication of what the size is for "db_cache_size". This is the replacement value for db_block_buffers starting in 9i (except it is in bytes now, not in buffer count). I don't know if the param name changed/went-away in 10g though...

My guess is that your db_cache_size + shared_pool + large_pool + java_pool exceeds the 10g limit in your init.ora file.

Just to test, see if by changing the sga_max_size param to 20G lets the db come up. If so, then your problem is in the init.ora parameters, and something in there is blowing the memory requirements past 10Gig.

[Hein van den Heuvel]

A few of the replies seem to have a sentiment of 'who needs such a large SGA anyways'. And it is a fair question to ask. One should be able to justify it, but it can rally solve problems/ achieve performance level whcih are not possible without a VLM solution. 64-bitness is a real enabler in this space.

An extreme example of this is of course TPPC.
folks interested in questions like this topic should study a full disclosure report or two. Even if just just for grins, but notably to get a feeling of how far you can turn those knobs. For example, below some details from the 1M TPPC done on a 64P Itanium Superdome running HP-UX and Oracle 10g: http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=103110401
Physical memory: 1024 GB, Disks: 2000+, Storage: 38,000 GB.
SGA: more than 1/2 TB (500,000MB)

Full disclosure: http://www.tpc.org/results/FDR/TPCC/hp_tpcc_sd_1mil_fdr.pdf

hpux params:
:
tunable maxtsiz_64bit 4294967296
tunable maxssiz_64bit 1073741824
tunable maxdsiz_64bit 274877906944
tunable maxdsiz 3221225472
tunable shmmax 0x10000000000
tunable nfile 800000
tunable max_async_ports 2000
tunable unlockable_mem 1
tunable swapmem_on 0
tunable scsi_max_qdepth 32
:

Oracle config:
:
compatible = 10.1.0.0.0
db_name = tpcc
control_files = /project/oracle/build95k/dbs/tpcc_disks/control_001
db_files = 650
parallel_max_servers = 256
recovery_parallelism = 64
sessions = 2800
processes = 1850
transactions = 2000
db_block_size = 4096
db_cache_size = 22000M
db_2k_cache_size = 3200M
db_8k_cache_size = 620M
db_16k_cache_size = 200000M
db_keep_cache_size = 615000M
db_recycle_cache_size = 115000M
dml_locks = 500
log_buffer = 33554432


fwiw,
Hein.

[Yogeeraj_1]

hi,

allow me to add the following:

In our systems where we use Oracle 10g we have just set sga_target and let oracle itself figure the right SGA size out empirically over time.

i suggest that you also look into the possibilities offered by this parameter after you have reviewed your kernel parameters.

all the best!

kind regards
yogeeraj

[Chris Perry_5]

Hello,
many of the responses here did not address the real question, which was how to get the DB started. I said quite clearly that the DB hung on start without a single entry on the alert log. I wanted to know how to fix this. I did not need disitations on how to set the size.
We eventaully found there was some wierd qwirk in the memory which cleared after a reboot. Everything worked perfectly after that without kernel or DB parameter adjustment.
The real sting came after we went live. It turnes out 10.1.0.3 has a bug which causes excessive buffer latch waits if the sga is set above about 1GB. We had to resize with a tink SGA to overcome this.
Chris Perry.