We have a hardware with 1GB of memory and we are installing Oracle 9i on this server. This server will be a dedicated Oracle server with only one instance on it. We are intending to install a large multipurpose production database. Let us see how we can go about it.
In estimating the parameters required in init.ora file, I will do a heuristic approach. I assume that I have 1GB of RAM and I will be installing one Oracle instance on the hardware!
I will allocate somewhere in the region of 60-75% of RAM to my SGA i.e. Total SGA = RAM x 0.7 = 1000MB * 0.7 = 700MB
For a multi purpose database I will opt for a database block size "db_block_size" of 8192 bytes (see notes below)
I will give database buffer cache "db_buffer_cache" 45% share of SGA i.e. 700MB * 0.4 =315 MB.
Remember db_buffer_cache = db_block_buffers x db_block_size
For 315MB of db buffer cache, using the above formula I will have 315*1024*1024/8192 = 40320 "db_block_buffers".
I will give "shared_pool" 45% of SGA = 315MB and 5% to "shared_pool_reserved_size" = 16MB.
For "large_pool_size" I will choose initial value of 20MB. This should be large enough for RMAN etc.
For "java_pool_size" size I will allocate 20MB to start with.
I will allocate 512KB to "sort_area_size" and will set "sort_area_retained_size" = 0.
For "log_buffer, I will consider a range of 512KB-1MB max (see notes below). I will go for 512KB to start with.
So in summary I will have
shared_pool_size =330301440 # 315MB
shared_pool_reserved_size =16515072 # 15M
large_pool_size =20971520 # 20MB
java_pool_size =20971520 # 20MB
log_buffer =524288 # 512KB
sort_area_size =524288 # 512KB
sort_area_retained_size =0 # 0 K
Notes: For OLTP systems, smaller block sizes are usually better. These systems typically are looking for random data, usually via indexed lookups. If you are using systems that do sequential reads, such as data warehouses for OLAP systems, then you want to read in as much data in a single I/O as possible. This is because you typically want to read all the rows in the block. Thus larger block sizes are usually preferred in such environments. For an OLTP database these days, an 8K block size tends to perform best.
Do not oversize the log buffer. Generally do not make the redo log buffer larger than 1MB.
Keep both Large pool and Java pool to default and monitor their usage and increase them if required.