Sizing a database ...

[vivekm]

Hi all,

I am looking into sizing our new database which is run on Oracle 9i Release 2. As yet, I have not been able to find a simple guideline which tells me how to do the calculation for each component. For example, I want to know, given the number of users, the hardware specified (which ultimately restricts how much I can allocate to Oracle), size of database, the number of transactions per second - what size the following will be:
Shared pool
Large pool
Java pool
buffer cache
log buffer

I know Oracle gives a best stab and then monitor the hit ratios but that just is not good enough - as there should be a bit more scientific process involved. Can someone send me a document or reference.

Thanks,
Vivek

 

[Turkbear]

Hi,
I believe that Oracle has a sizing guide, check out their site or

http://otn.oracle.com

and search for 'sizing'..
( You may need to register at otn ( Oracle's Technet site), but it is free, they do not sell your name and it is a very valuable resource..)

 

[sybaseguru]

Vivek,

Let us go though rule of thumb. Again this depends on your application profilling (OLTP,DSS, warehouse etc) but good to start.

Total memory per instance "m" = (RAM x(0.6-0.75)/No of instances on the same host

shared pool  =  m x 0.45
buffer cache =  m x 0.45
log buffer   =  m x 0.10 

The amount of max shared memory should be set large enough for memory to be allocated. In Solaris host this is set in /etc/system file. If you are running 64-bit Oracle on 64-bit OS (Solaris 2.8 say) there is no limit on the maximum size of shared memory. If you are running 32-bit Oracle you will be limited to maximum of 4GB of shared memory. In other words your SGA cannot exceed 4GB.

Hope this helps