Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recommendations for setting up SGA

Tips and Tricks

Recommendations for setting up SGA

by  sybaseguru  Posted    (Edited  )

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.
[color blue]
Code: 
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

db_block_size                 =8192
		
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 

db_block_buffers              =40320
[/color]
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.

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top