Re: Determining Memory Allocations - Dedicated/Shared Server

On Jan 24, 6:50 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> I've found "formulas" for determining the SGA/PGA sizing for a dedicated
> server.
>
> Using a formula from  the Burleson Consulting web site I was able to come up
> with these figures:
>
>                                                           --  8 GB --   --  
> 16 GB --    -- 32 GB --
> Total RAM on Windows Server         8,191.0 MB 16,384.0 MB 32,767.0 MB
>
>  Less:
>
>  Total PGA regions for 100 users        3,980.0 MB  3,980.0 MB  3,980.0 MB
>
>  RAM reserved for Windows (20%)   1,638.2 MB  3,276.8 MB  6,553.4 MB
>
>  RAM for SGA & Buffers                   2,572.8 MB  9,127.2 MB 22,233.6 MB
>
> What I've been trying to find out is how would I determine the memory
> allocations if both dedicated and shared connections would be in use?  Is
> the memory allocated for a shared server configuration, a dedicated server
> configuration or is there a "hybrid" server configuration that's used?

It is usually best to look for agreement between multiple information sources before determining what is correct, partially correct, wrong, or just out of date. Another source for information on this subject can be found here:
 http://jonathanlewis.wordpress.com/2007/12/19/memory-management/

The above link contains a link to the following presentation: http://www.pythian.com/blogs/741/pythian-goodies-free-memory-swap-oracle-and-everything

You might also look here:
http://jonathanlewis.wordpress.com/2007/04/16/sga-resizing/

One suggestion from a 4+ year old Oracle tuning book is the following: Oracle SGA Components ~ 50%

   Database Buffer Cache ~80%
   Shared Pool Area ~12%
   Fixed Size + Misc. ~1%
   Redo Log Buffer ~0.1%
Operating System + Related Components ~15% User Memory ~ 35%

The above does not make a distinction between dedicated and shared server setup. I suspect that the above falls under the category of "just out of date", especially with Oracle 10g. The above suggestion may have been an appropriate starting point for Oracle 8i.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jan 25 2008 - 05:05:30 CST