topic Re: Howto Use A Large SGA for Oracle 10g in Operating System - HP-UX

Howto Use A Large SGA for Oracle 10g

Chris Perry_5 — Thu, 19 May 2005 06:32:42 GMT

Hi,
I am attempting to start an Oracle 10g instance with a 10GB SGA. The box has 32GB RAM and there is 17GB free RAM when I attempt to start. The startup command just hangs. Nothing except the startup message is written to the Oracle alert Log.
IPCS shows dozens of shared memory segments being created. After a min or two I note that there is one shadow process running at 100% on a CPU. Shutdown abort does not work. I have to kill the shaddow proces by hand. All the shared memory segments are left behind. I have to remove them by hand too.
I can start an instance with a 5GB SGA, which goes into a single shared memory segment.
I suspect I have found either a HP memory management bug or have a kernel which is not tuned correctly. The OS is HP-UX PA-RISK 11.11. We have all the required patches for Oracle 10g on 11.11. We have also set all the kernel parameters as per Oracle's recomendations on MetaLink.
Oracle say that a 10g Instance can address 16 Exabytes of RAM!
The Unix support guys do not hev mutch Oracle experience.
I suspect the problem is with the OS, in some way.
Do we need to make use of HP Memory Windows?
Does any-one out there have a full set of kernel parameters used on a box with an SGA of about 10GB.

Re: Howto Use A Large SGA for Oracle 10g

Eric Antunes — Thu, 19 May 2005 06:55:58 GMT

Hi Chris,

I don't know exactly the requirements of 10g version but 10Gb SGA seems to me exagerate: the idea of the SGA is to SHARE data: SQL must be parsed once and executed many times. See also Metalink Note 30810.1...

Best Regards,

Eric Antunes

Re: Howto Use A Large SGA for Oracle 10g

Julio Yamawaki — Thu, 19 May 2005 07:01:08 GMT

Hi,

Probably you have a problem of HP-UX kernel.
This kernel I use with a Oracle RAC instance with aproximately 8 GB of SGA:

Tunable Value Expression Changes
NSTREVENT 50 Default
NSTRPUSH 16 Default
NSTRSCHED 0 Default
STRCTLSZ 1024 Default
STRMSGSZ 0 Default
acctresume 4 Default
acctsuspend 2 Default
aio_listio_max 256 Default Immed
aio_max_ops 2048 Default Immed
aio_monitor_run_sec 30 Default Immed
aio_physmem_pct 10 Default
aio_prio_delta_max 20 Default Immed
aio_proc_thread_pct 70 Default Immed
aio_proc_threads 1024 Default Immed
aio_req_per_thread 1 Default Immed
allocate_fs_swapmap 0 Default
alwaysdump 0 Default Immed
chanq_hash_locks 256 Default
core_addshmem_read 0 Default Immed
core_addshmem_write 0 Default Immed
create_fastlinks 0 Default
dbc_max_pct 10 10 Immed
dbc_min_pct 5 Default Immed
default_disk_ir 0 Default
disksort_seconds 0 Default
dma32_pool_size 268435456 Default
dmp_rootdev_is_vol 0 Default
dmp_swapdev_is_vol 0 Default
dnlc_hash_locks 512 Default
dontdump 0 Default Immed
dst 1 Default
dump_compress_on 1 Default Immed
enable_idds 0 Default Immed
eqmemsize 15 Default
executable_stack 0 Default Immed
fr_statemax 800000 Default Immed
fr_tcpidletimeout 86400 Default Immed
fs_async 0 Default
fs_symlinks 20 Default Immed
ftable_hash_locks 64 Default
hp_hfs_mtra_enabled 1 Default
intr_strobe_ics_pct 100 Default Auto
io_ports_hash_locks 64 Default
ioforw_timeout 0 Default Auto
ksi_alloc_max 33600 Default Immed
ksi_send_max 32 Default
max_acct_file_size 2560000 Default Immed
max_async_ports 50 Default
max_mem_window 0 Default
max_thread_proc 256 Default Immed
maxdsiz 1073741824 Default Immed
maxdsiz_64bit 4294967296 Default Immed
maxfiles 2048 Default
maxfiles_lim 4096 Default Immed
maxrsessiz 8388608 Default
maxrsessiz_64bit 8388608 Default
maxssiz 8388608 Default Immed
maxssiz_64bit 268435456 Default Immed
maxtsiz 100663296 Default Immed
maxtsiz_64bit 1073741824 Default Immed
maxuprc 3687 3687 Immed
maxvgs 64 64
msgmap 4096 4096
msgmax 8192 Default Immed
msgmnb 16384 Default Immed
msgmni 4096 4096
msgseg 32767 32767
msgssz 96 Default
msgtql 4096 4096
ncdnode 150 Default
nclist 8292 Default
ncsize 35840 35840
nfile 65536 65536 Imm (auto disabled)
nflocks 4096 Default Auto
ninode 34816 34816
nkthread 8416 Default Immed
nproc 4200 Default Immed
npty 60 Default
nstrpty 60 60
nstrtel 60 Default
nswapdev 10 Default
nswapfs 10 Default
nsysmap 8400 Default
nsysmap64 8400 Default
o_sync_is_o_dsync 0 Default
pa_maxssiz_32bit 83648512 Default
pa_maxssiz_64bit 536870912 Default
pagezero_daemon_enabled 1 Default Immed
pfdat_hash_locks 128 Default
physical_io_buffers 640 Default Auto
region_hash_locks 128 Default
remote_nfs_swap 0 Default
rng_bitvals 9876543210 Default
rng_sleeptime 2 Default
rtsched_numpri 32 Default
scroll_lines 100 Default
scsi_max_qdepth 8 Default Immed
scsi_maxphys 1048576 Default
secure_sid_scripts 1 Default Immed
semaem 16384 Default
semmni 4096 4096
semmns 8192 8192
semmnu 4092 4092
semmsl 2048 Default Immed
semume 100 Default
semvmx 32767 Default
sendfile_max 0 Default
shmmax 12674010144 12674010144 Immed
shmmni 512 512 Immed
shmseg 512 512 Immed
st_ats_enabled 0 Default
st_fail_overruns 0 Default
st_large_recs 0 Default
st_san_safe 1 1 Immed
streampipes 0 Default
swapmem_on 0 0
swchunk 16496 16496
sysv_hash_locks 128 Default
tcphashsz 2048 Default
timeslice 10 Default
timezone 420 Default
unlockable_mem 0 Default
vnode_cd_hash_locks 128 Default
vnode_hash_locks 128 Default
vol_checkpt_default 10240 Default
vol_dcm_replay_size 262144 Default
vol_default_iodelay 50 Default
vol_fmr_logsz 4 Default
vol_max_bchain 32 Default
vol_max_nconfigs 20 Default
vol_max_nlogs 20 Default
vol_max_nmpool_sz 4194304 Default
vol_max_prm_dgs 1024 Default
vol_max_rdback_sz 4194304 Default
vol_max_vol 8388608 Default
vol_maxio 256 Default
vol_maxioctl 32768 Default
vol_maxkiocount 2048 Default
vol_maxparallelio 256 Default
vol_maxspecialio 256 Default
vol_maxstablebufsize 256 Default
vol_min_lowmem_sz 524288 Default
vol_mvr_maxround 256 Default
vol_nm_hb_timeout 10 Default
vol_rootdev_is_vol 0 Default
vol_subdisk_num 4096 Default
vol_swapdev_is_vol 0 Default
vol_vvr_transport 1 Default
vol_vvr_use_nat 0 Default
volcvm_cluster_size 16 Default
volcvm_smartsync 1 Default
voldrl_max_drtregs 2048 Default
voldrl_min_regionsz 512 Default
voliomem_chunk_size 65536 Default
voliomem_maxpool_sz 4194304 Default
voliot_errbuf_dflt 16384 Default
voliot_iobuf_default 8192 Default
voliot_iobuf_limit 131072 Default
voliot_iobuf_max 65536 Default
voliot_max_open 32 Default
volraid_rsrtransmax 1 Default
vps_ceiling 16 Default
vps_chatr_ceiling 1048576 Default
vps_pagesize 4 Default
vx_maxlink 32767 Default
vx_ninode 0 Default
vxfs_bc_bufhwm 0 Default
vxfs_ifree_timelag 0 Default Immed
vxtask_max_monitors 32 Default

Regards,

Re: Howto Use A Large SGA for Oracle 10g

Eric Antunes — Thu, 19 May 2005 07:15:43 GMT

Hi again,

Anyway, if you realy want to allocate this huge SGA, I strongly recomend the reading of Metalink Note 69119.1 (HP-UX: SGA sizing issues on HP-UX).

What are your init.ora SGA related parameters (shared_pool_size, large_pool_size, java_pool_size, db_cache_buffers, db_block_size, ...)?

Eric

Re: Howto Use A Large SGA for Oracle 10g

Zinky — Thu, 19 May 2005 07:51:15 GMT

Chris,

I think your problem may very well be kernel params relating to shared memory / IPC. Our Oracle instaces have very large SGAs - the biggest is around 25GB of SGA on a 64GB RAM SuperDome. I recommend you take a look at your SHMMAX settings which should be able to accomodate your SGA in one segment. Ours is set to 0x800000000 (or ~ 32GB). I bet increasing your SHMMAX to say 0x400000000 (16GB) will fix your 10GB SGA instance from hanging at startup.

Other kernel parameters suggested for Oracle 9/10 on 11.11 systems are:

maxdsiz Max process data seg size for 32bit procs 1073741824 bytes or (0x40000000)
maxdsiz_64bit Max process data seg size for 64bit procs 2147483648 bytes or (0x80000000)
maxssiz Max process storage seg size for 32bit procs 134217728 bytes

maxssiz_64bit Max process storage seg size for 64bit procs 1073741824 bytes or (0x40000000)
maxtsize Max process text seg size 128MB
maxuprc Max # of procs/user ((NPROC*9)/10)
max_thread_proc Max threads/proc 256
nkthread Max kernel threads (((NPROC * 7) / 4) + 16)
nproc Max procs/system 4096
msgmap Message map size (2+msgmni)
msgmni Max msg queues nproc
msgseg # segs in msg queue 32767
msgtql Total msgs on system nproc
semmap Free sem map size (semmni+2)
semmni Max sems/system 4096
semmns Max sems for users/sys (semmni*2)
semmnu Max undos/sem (nproc-4)
semvmx Max sem value 32767
shmmax Max shared memory segment Large enough to hold the entire SGA in one shared memory segment
shmmni Max segments/system 512
shmseg Max segments/proc 120 per Oracle database
max_async_ports Max ports to asynch driver/system max. no of shadow processes + no of parallel query slaves (could go up to nproc)
maxusers Max simultaneous users/system set to number of concurrent Oracle DB users + 64
bufpages Pages of static buffer cache â older parm 0

dbc_max_pct Max dynamic buffer cache between 3 and 10 % of memory
dbc_min_pct Min dynamic buffer cache between 2 and 5% of memory
maxfiles Soft limit for open files 1024
maxfiles_lim Hard limit for open files 1024 (default)
nfile Open files limit (15 * NPROC + 2048)
nflocks File lock limit (NPROC) (at least 4096)
ninode Max inodes in memory (8 * NPROC + 2048)

Re: Howto Use A Large SGA for Oracle 10g

TwoProc — Thu, 19 May 2005 08:44:19 GMT

Like the others - I agree its your kernel, among other things. I've got a bigger SGA - but if you use these you should come up. If you need more - review Nelson's previous post for more settings (maxdsiz,maxtsize,maxsize, etc)
a) set shmmax to 0x300000000 - that will cover your 10G and then some. Having Oracle cover lots and lots of segments can give bottlenecks - especially more than 6 from what I've read in the past. Best to have it all in one segment for performance.
b) Add the line "sga_max_size = 11264M" to the bottom of your init.ora file. If you're not getting tripped up on maxdata, maxtextsize or maxsegment size, or number file handles - this is probably what is stopping you from coming up. In fact - in general if you're low on the other resources like I just listed - you'll get an error saying kind of what the error is like. But, in my experience, if you don't have the sga_max_size big enough - it froze just like you've described, with no error code (seems like Oracle would be better at giving hints on its own settings though).

HTH

Re: Howto Use A Large SGA for Oracle 10g

Chris Perry_5 — Sun, 22 May 2005 01:14:07 GMT

Hi,
Additional Information: The pfile. Note that many params have changed name or are obsolete in 10g.

processes = 750
open_cursors = 3000
#db_block_buffers = 1800
#db_file_multiblock_read_count = 128
# buffer_pool_keep = 12800
db_keep_cache_size = 14K

shared_pool_size = 534986576
shared_pool_reserved_size = 100663296

large_pool_size = 47185920

#sort_area_size = 52428800
#hash_area_size = 25165824

compatible = 10.1.0
parallel_max_servers = 64
parallel_min_servers = 64

sga_max_size=10G
sga_target=10G
pga_aggregate_target=2000M

Re: Howto Use A Large SGA for Oracle 10g

Chris Perry_5 — Sun, 22 May 2005 01:40:05 GMT

Hi,

I have sent the kernal related responses off to the Unix guys to figure on.

BTW, the DB size is 3TB.

Chris,
DBA.

Re: Howto Use A Large SGA for Oracle 10g

Bill Hassell — Sun, 22 May 2005 21:50:47 GMT

The size of the database is not really important for SGA sizing. SGA should be sized based on the database design (row sizes, tempfile sorts, caching, etc). The largest sections for SGA should be checked for reasonableness. If all is well, then Oracle support will have to resolve the wide disparity between a 5GB SGA (one segment) and the dozens of segments (I assume they are quite small) started with the 10GB SGA. The small segment requests were probably triggered by some return value. Since 10g is a 64bit design, the size of SGA can be hundreds to thousands of GB unless limited by kernel parameters. Note that memory windows are only used for 32bit programs.

Re: Howto Use A Large SGA for Oracle 10g

Eric Antunes — Mon, 23 May 2005 04:33:00 GMT

Hi Chris,

Here are some notes about the use of automatic SGA management:

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=256913.1

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=270065.1

And

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=257643.1

Best Regards,

Eric Antunes

Re: Howto Use A Large SGA for Oracle 10g

TwoProc — Fri, 26 Aug 2005 16:54:13 GMT

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.

Re: Howto Use A Large SGA for Oracle 10g

Hein van den Heuvel — Fri, 26 Aug 2005 23:38:27 GMT

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.

Re: Howto Use A Large SGA for Oracle 10g

Yogeeraj_1 — Fri, 26 Aug 2005 23:42:32 GMT

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

Re: Howto Use A Large SGA for Oracle 10g

Chris Perry_5 — Sun, 28 Aug 2005 22:44:19 GMT

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.