Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Approximately sizing SGA

Re: Approximately sizing SGA

From: Joel Garry <joel-garry_at_home.com>
Date: 15 Dec 2004 14:02:16 -0800
Message-ID: <1103148136.305930.253230@f14g2000cwb.googlegroups.com>

iloadmin wrote:
> Hello,
>
> I've been given the exciting responsibility of getting oracle 9 sga
> sized to be the backend of a website. I have some oracle experience
> and I understand the sga consist of memory structures which are part
of
> an instance. I know the sga contain data information and manipulates
> memory virutally. My goal is to approximately size the
> shared_pool_size, db_block_size, db_cache_size and log_buffer
> parameters. After doing some research I found a formula to size the
> sga.
>
> db_cache_size + db_keep_cache_size + db_recycle_cache_size +
> DBnk_cache_size +
> shared_pool_size+ large_pool_size + java_pool_size + log_buffers +
1MB.
>
> Again, I am not trying to be exact. I do not want to accept the
> default settings of an oracle installation for these parameters. The
> database will be the backend of website and it is my first time
> attempting this. I am willing to research for all my answer and test
> my hypothesis as I learn about them so I can complete the
> responsibility. The database will be working hand in hand with
redhat
> linux version 3 and the OFA for Oracle 9 have been set including the
> blocksize for the mount points.
>
> My quesiton "knowing this will be the backend what numbers should I
be
> looking at to plug into these parameters to get this started" ?
> Thanks in advance,
> iloadmin

The db_block_size depends on the primary purpose of the db, ie OLTP or DW. log_buffer, dba_cache_size and shared_pool_size need to be tuned with a running system under load. shared_pool_size requirements can vary significantly based on how the code is written. Some people (including me) initially ballpark the dba_cache size to make the SGA take about 1/3 the memory, knowing we will probably have to increase the shared_pool and dink with keep pools and such dependent on the site usage and application. Poke around in the patch set readme's for an idea on how big to start the java_pool_size.

Definitely look up pga_aggregate_target, db_file_multiblock_read_count, SGA_MAX_SIZE, granularity, optimizer_index_cost_adj, statistics_level, fast_start_mttr_target, log_checkpoint_interval and open_cursors in the docs. You might also want to consider getting a few of the 3rd party Oracle books that explain this, keeping in mind there is still a lot of myth making floating about and your load testing trumps all. J. Lewis' book is quite practical.

jg

--
@home.com is bogus.
$500M AOL surprise!
http://www.signonsandiego.com/uniontrib/20041215/news_1b15aol.html
Received on Wed Dec 15 2004 - 16:02:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US