Oracle Sizing

[Thijn Moons_1]

I am trying to setup some general baselines to size oracle on a HP9000 4000 N class server. Are there any tools/whitepapers etc on this subject?
Help is much appreciated.

Thanks

[Andreas D. Skjervold]

Hi

You have the Oracle withepaper on SAFE:
How to Stop Defragmenting and Start Living(64K/pdf) CR 239049 http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=131771.1
http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049

Its a good way of setting up uniform tablespace extent sizes for data, rollback and temp tablespaces.

Its still useful when using the uniform tablespace storage parameter in 8i, as you'll need the right size for the extent.

Andreas

[Dennis J Robinson]

I don't know of any tools or whitepapers
based on sizing with respect specifically to N4000. But I can give some general recommendations.

All filesystems used for datafiles should be created with a 8k block and 8k fragment. We want Oracle blocks to align properly with filesystem blocks.

SYSTEM tablespace 256M at least

RBS 1024 striped across 4 disks. Few large rbs for batch, many small rbs for OLTP

TEMP Can live on single LUN on array such as EMC or XP

Redo 64M-1024M, size so log switch no less than 15 minutes.

Datafiles - 2GB is a size which will work with all versions of tar, backup products, etc.

init.ora parameters, general recommends:

SGA sized so that box has at least 30% memory free during hardest processor loading.

db_block_size=8192 for OLTP, 32768 for DSS

db_block_buffers - don't use any oracle defaults, size so oracle hit% >=95%

shared_pool_size - don't use oracle default, size so that shared pool is large enough to hold commonly used sql, but not so large as to cause unnecessary long search times.

log_buffer 1048576 seems to work

cursor_space_for_time=true if your don't have large miss on library cache

cursor_sharing=force - set this if application doesn't make use of bind variables. Will force common sql to use them, reducing hard parses - which reduce CPU cost.

_db_block_hash_latches=db_block_buffers/4 on 8i versions. Formula changed for hash latch allocation, results in too few hash latches allocated and large wait/spin on "cache buffer chain".

db_block_lru_latches=(#CPU's*2)
db_writer_processes=(* of CPU's even with async)
disk_async_io=TRUE if you use raw, FALSE if you use cooked.

lock_sga=TRUE (also have dba MLOCK line in /etc/privgroup)

db_file_multiblock_read_count=32 ( 128k-256k seems to be sweet spot on most disk devices for large read )

enqueue_resources=1000 tune based on enqueue timeouts,

hash_join_enable=true if you use cost based optimizer

optimizer_mode=CHOOSE #make sure you analyze statistics on all tables.

sort_area_size=1048576 # Tune for no disk sort
sort_area_retained_size=1048576

max_files=1024 #never know how large you will grow

timed_statistics=true until you get init settings tuned.

Please insert the perfstat schema for statspack and even if you use graphical monitor allow statspack to tell you state of instance health.

I can be reached at drobinson@optimalss.com