Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Oracle: All versions FAQ

Tips and Tricks

Recommendations for setting up SGA by sybaseguru
Posted: 1 Aug 03 (Edited 1 Aug 03)

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

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

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.              

Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close